i understand this ought to be db 101, nevertheless its simply not as obvious as possible for me personally. I'm using SQL2005 express and i wish to copy data from databaseA to databaseB. DatabaseB already consists of existing data - it might even contain data with pk's which have transformed or dont exist - for instance:
DataBase A pk1 = peaches pk2 = apples
DataBase B pk1 = peaches pk2 = oranges pk3 = apples
now both A &lifier B have related tables which are associated with the pk.
In the past i'd write an application that chooses the information from the and copies it to B via its Place/Update procs (using .Internet), but obv this is cumbersome, tho some advantages are i will have a dropdown that enables you to definitely choose A copying - and B copying to whether it is available, or say add new - for example:
(dropdown - choose source) Peaches Apples
(dropdown - choose target) -new- peaches oranges apples
I would use SSIS, however the target db only has SQL express making this unavailable nor have i got time for you to learn it (ive used dts much previously, although not this more recent tool). In SQL2000 i'd only use DTS, however with the greater complicated schema now i am not even i would trust that.
Any suggestions or must i just still write my custom applications emigrate data from One place to another?
Im searching toward everyones suggestions - i would like to continue lower a path which i feel is the easiest method to do that :-)
when the servers are on a single network give a linked server (lookup in transact sql books online). You'll be able to run queries over the two servers.
SQL Server 2005 Express sucks for the reason that it doesn't include SSIS. However you may also use BCP (bulk copy, look this up within the transact sql books online too) copying your computer data to various tables around the target server. It's most likely inside your path so from dos you are able to type bcp /? to obtain a listing of options. When the information is inside a table in your target server you are able to run queries against it.
You need to produce a mapping between secrets. I'm not sure how from peaches/apples you receive peaches/oranges/apples. Without doubt there's some business logic. When you link the servers or obtain the data on a single server. You may either use business logic to map old primary secrets to new secrets. Or perhaps a mapping table (oldkey, newkey) or (oldkey1, oldkey2, newkey1, newkey2, newkey3) that you simply by hand complete.
sql 2008 comes with an interesting feature known as Change Data Capture that could be interesting in the future for you personally http://blog.benhall.me.uk/2007/06/sql-server-2008-change-data-capture-cdc.html
A fantasy acquainted with the restrictions of SQL Server Express, but tend to you backup? After which import as the second database?
You may consider merge replication - http://msdn.microsoft.com/en-us/library/ms165713(SQL.90).aspx
Could it be always a 1 way push? A->B?
Like a note, if you can get SSIS, you need to take some time to understand it. It's much better than DTS imho.