Merging Identical User Tables

Question

Merging Identical User Tables

Barbara-167570

SSCrazy

Points: 2352
More actions
February 7, 2006 at 8:43 am

#111471

Hello Everyone,

I have what I think is a fairly simple question that I’m hoping someone can help me with. I am merging two databases that have the same layout, Database A and Database B. Database A has not been in use as long as Database B so there is significantly less records on it. What I’m trying to do is just copy the data out of Database A tables and insert it into Database B tables. However, the tables that need to be merged have identity values as their Primary Keys, for example users.users_id, so the issue I’m running into is some of the identity fields overlap in some of these tables.

I am currently using sp_generate_inserts from http://vyaskn.tripod.com/, and the script works great, but I’m not sure how to modify it or if there is a better way to create these inserts so that the id is increased by a number that I can specify. For example if I want to add 9000 to the identity value for all of the records in the users table so that the identity values for users_id are then 9001, 9002, so on…

I have looked through this site, and various others along with BOL, but I’m coming up empty. If you have not figured it out by now I am not a SQL Developer J

Along with this I will also be updating the id values in any associated child tables.

If anyone can assist me with this I’d really appreciate it.
Thanks in advance for any and all assistance!

Barbara

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Justin-226590 SSC-Addicted Points: 432 More actions · Answer 1

Barbara,

Are you familiar with using DTS? This can easily be done by using the DTS wizard and select "Usa a query to specify data to transfer". Once you select your tables to export, you can click "Transform" to edit how the information comes over. There you can add 9000 to your existing field value (assuming it's a numeric field).

I hope this helps

Justin

Barbara-167570 SSCrazy Points: 2352 More actions · Answer 2

Thank Justin,

I'm not all that familiar with DTS. I did look at that earlier when trying to resolve this issue but when I went on to the Transformations tab the script is in VB which I have no idea how to modify. Am I missing something?

The existing fields that need to be modified are all numeric fields.

Thanks again!

Barbara

Ray M SSC-Insane Points: 21093 More actions · Answer 3

For what your doing Dts would take a little time.

so simple insert / select statements should work.

Declare @maxid int

Set @MaxID = select max(FieldID) from DatabaseA.dbo.TableA

Set identity_Insert DatabaseA.dbo.TableA on

Insert into DatabaseA.dbo.TableA (FieldID, field1, field2, field3)

Select FieldID + @MaxID, Field1, Field2, Field3

From DatabaseB.dbo.TableA

Set identity_Insert DatabaseA.dbo.TableA off

Justin-226590 SSC-Addicted Points: 432 More actions · Answer 4

When you get to the Transformation, you can edit the statement by adding + 9000 (or whatever the numebr is). See below for example:

Function Main()

DTSDestination("YourField") = DTSSource("YourField") + 9000

Main = DTSTransformStat_OK

End Function

Barbara-167570 SSCrazy Points: 2352 More actions · Answer 5

Thank you both very much for taking the time to respond to my post.

I have tested both of your suggestions a little, and definitely plan on testing it more as soon as I get a chance.

I was able to get one of our developers to "tweak" the sp_generate_inserts sp that I mentioned above so it creates the inserts for me with the appropriate id number. As I'm going to need to change the identity increament for each of the tables I'm merging, this solution seems to be working out the best for me.

Again, thank you for helping me with this. I'm looking forward to working with your suggestions in the near future so that I can become more comfortable with this process.

Barbara