How to export/generate data without copying its primary key?

Question

I got the following problem: I have 2 databases, let's say DB1 and DB2.
I'm using SQL Server 2008R2

In DB1 I have:

tblPerson

   ID Value
   1  Jake
   2  Sandra

In DB2 I have:

   ID   Value
   1    Jef
   2    Kendra

I want to export or generate script or use a tool to become next.

In DB1:

   ID  Value
   1   Jake
   2   Sandra
   3   Jef
   4   Kendra

But I always get this result:

   ID   Value
   1    Jake
   2    Sandra
   1    Jef
   2    Kendra

I tried:

• Export (with property identity insert on)
• Generate script with generate primary key off

None of these worked is there a tool for this or is it just not possible and do I have to do everything by hand?

Answer 1

You're unfortunately not saying which version of SQL Server you're using - if you're on SQL Server 2005 or newer, you could use something like this:

;WITH CombinedData AS
(
    SELECT DBNr = 1, ID, Value
    FROM DB1.dbo.tblPerson

    UNION ALL

    SELECT DBNr = 2, ID, Value
    FROM DB2.dbo.tblPerson
)
SELECT
    ROW_NUMBER() OVER (ORDER BY DBNr, ID) AS 'NewID',
    Value
FROM 
    CombinedData

That would give you an output something like this:

NewID  PersonName
  1     Jake
  2     Sandra
  3     Jef
  4     Kendra

Answer 2

with the new table having its id set to auto-identity, you should be able to do the following:

insert into db1.NewTable ( Value ) select Value from db1.Table
insert into db1.NewTable ( Value ) select Value from db2.Table