ErikMN (3/27/2014)
...I want to take data from one database to another database that already has data in tables that the data is going into.For example,
DB1 - Table1 has IDs 1-1000
DB2 - Table1 has IDs 1-500
I'm wanting to add records with IDs 1-1000 to the second table but also making sure the references in other tables point to the correctID from Table1.
OK, here's my crazy, shoot-from-the-hip shortcut idea:
1) Make a new schema in your source DB called [Export]
2) For each table to be exported, create a View in [Export] named "v_{tableName}" that just selects every column by name. (This can be automated)
3) Edit these views, changing every ID column and ID-linking column to "{IdName}+20000 As [{IdName}]". (with some effort, this too can be automated)
4) Use Generate Scripts on the source DB, Data-Only and select only the Views in the [Export] schema.
5) Edit the script and add "SET IDENTITY INSERT" On/Off statements for every table's INSERT statement block. (I haven't figured out a way to automate this part)
6) Backup the Target DB. Seriously, this is dangerous.
7) Execute the script against the Target DB.
And hopefully that does it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]