• ErikMN (3/27/2014)


    RBarryYoung (3/27/2014)


    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.

    Thank you, this is definitely intriguing and while it would still be quite a bit of work, it'd certainly be less initial work than my original approach. The only thing I'd need to think about is step 3 where i don't think a static value of +20000 would be appropriate as there are tables with a few hundred million rows and some with 20, so I'd probably want to do some kind of count or max on the receiving table and add that instead.

    In that case, if the data type is int, add +1000000000 instead of +20000 🙂

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.