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?
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?
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.
You could also use DTS from source to destination but ensure you do the appropriate transformations.
3.14159265358979323846264338327950288
4197169399375105820974944592307816406
28620899862803482534211706798 pi 101
if both databases are on the same machine u can use SQL statements to transfer them from one database to the next
if they not on the same machine then just back up from one machine, restore on the other... then carry out the said procedure.Code: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
Cultured in Aggression and Koding like a Warrior!!
“Common sense is instinct. Enough of it is genius.” - George Bernard Shaw.
"The significant problems we face cannot be solved by the same level of thinking that created them." - Albert Einstein
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.
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
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.
3.14159265358979323846264338327950288
4197169399375105820974944592307816406
28620899862803482534211706798 pi 101
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 causewe 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
Code: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
kenny u a try anger mi too dont.
Last edited by icymint3; Mar 30, 2007 at 09:12 PM.
Cultured in Aggression and Koding like a Warrior!!
“Common sense is instinct. Enough of it is genius.” - George Bernard Shaw.
"The significant problems we face cannot be solved by the same level of thinking that created them." - Albert Einstein