Data transfer

[keroed1]

i am planning to do an extensive data transfer from tables in one database to different database that has a different table structure can someone point me in the direction i should be reading up where i can accomplish this?

[redbwoy]

Well there is several things you could do. I'm not sure what is your timeline but you could:
1). Export the data to a text file that is comma delimited and then use DTS to import into the new table from this text file.

2). You could write a quick web or windows app to do this transfer automatically.

3) Write a stored procedure that uses a cursor and it will do the same select and insert you used to but place the columns where they need to be.

Either way you take it, there is no simple solution since you mention that both tables have a different structure.

[keroed1]

Well there is several things you could do. I'm not sure what is your timeline but you could:
1). Export the data to a text file that is comma delimited and then use DTS to import into the new table from this text file.

2). You could write a quick web or windows app to do this transfer automatically.

3) Write a stored procedure that uses a cursor and it will do the same select and insert you used to but place the columns where they need to be.

Either way you take it, there is no simple solution since you mention that both tables have a different structure.

yup i know for real, i jus wanted to know what directions were open to me

[recursion]

You could also use DTS from source to destination but ensure you do the appropriate transformations.

[icymint3]

if both databases are on the same machine u can use SQL statements to transfer them from one database to the next

insert	SecondDatabase.dbo.TableTo( changedColA, changedB, changecColC /*, etc*/)
select	UserName, 23 * age, isnull( dateofbirth, getdate() ) /*, etc */ 
from 	SourceDatabase.dbo.TableFrom /* , SourceDatabase.dbo.NextTable */
where 	DataShoudBCopied = 1

if they not on the same machine then just back up from one machine, restore on the other... then carry out the said procedure.

[girldemsuga]

I once wrote a utility to export a Supermarket Database from FoxPro tables to MS SQL Database.
It's gonna take some work on your part but it is not hard to do.

[redbwoy]

Recursion and icymint3.

I think you guys are missing the problem here. He is trying to port the data over to a table that has a different structure. So the methods you guys mention only works if the data is going to the same structure

[recursion]

If it was the same structure then a simple transfer would do. The transformation is what would allow the data from one DB to be transferred to the appropriate fields in the other.

icy's post also takes the different structure into consideration.

[keroed1]

if both databases are on the same machine u can use SQL statements to transfer them from one database to the next

insert	SecondDatabase.dbo.TableTo( changedColA, changedB, changecColC /*, etc*/)
select	UserName, 23 * age, isnull( dateofbirth, getdate() ) /*, etc */ 
from 	SourceDatabase.dbo.TableFrom /* , SourceDatabase.dbo.NextTable */
where 	DataShoudBCopied = 1

if they not on the same machine then just back up from one machine, restore on the other... then carry out the said procedure.

going to give you SP a test run on two random test databases.

"DataShoudBCopied = 1" whats this is the sepcified db command or u saying put my where clause whatever it is here?

[icymint3]

the methods you guys mention only works if the data is going to the same structure

yute, are u trying to annoy me...

get a book and read...
get a server and run some SQL...

do something, but dont tell me nuh crap...

what i showed the lad does not have anything to deal with similar structure, it have changes of column names, calculations, joins etc. that is implicit that the structure of the tables can b different... i wouldnt have said it if i unsure... that is what i do here ... and we been doing it a lot recently cause we convert a lot of persons database to our format.

recursion solution can also work, but using sql statements give u better control...

in fact let me show u (part of) an exact script

INSERT INTO smartX.dbo.Stock(   ItemNo, 
			        Description, 
                                SalesCategory, 
				SalesSubCategory, 
				Price, 
				WholeSalePrice, 
				CostPrice, 
				ReOrderLevel, 
				LastOrdered, 
				LastorderNo, 
				UnitOfOrder, 
				UnitOfSale, 
				TaxCode, 
				AlternateItemNo, 
				SubstituteItemNo, 
				LastPurchasePrice, 
				QtyOnOrder, 
				YtdSalesDollar, 
				PrevYtdSales, 
				YtdSalesQty, 
				YtdPurDollar, 
				YtdPurQty, 
				DateLastPurchased, 
				DateLastSale, 
				DeliveryDate, 
				MaximumDiscount, 
				AutoDiscount, 
				AutoDiscountExpires)
select Itemno, 
	[desc], 
	'PARTS', 
	null, 
	price,
	cost, 
	0, 
	0,
	null, 
	null,
	unit, 
	unit, 
	0, 
	null, 
	substitute,
	0,
	0,
	0,
	0,
	0,
	0,
	0,
	null,
	null,
	null,
	null,
	null,
	null

from EXTRACTF

whats this is the sepcified db command or u saying put my where clause whatever it is here?

kenny u a try anger mi too dont.