Please help with INSERT

[35mph]

We have two databases, almost identical, and we are trying to copy data from Table1 in Database1 to Table1 in Database2.

DB1..TBL1 has these columns:
Col1: primary key integer
Col2: text
Col3: Identity column

DB2..TBL1 has the same structure, and ALSO already has data in it, that we want to preserve.

We want to INSERT in DB2 those rows from DB1 that are not already in DB2.

The problem is that we can't just insert all the rows and columns from DB1 into DB2 since DB2 has Col1 already populated, and we can't overwrite those.

Basically, we need to insert into DB2 those rows that aren't already there, and pick up with the next sequential COL1 value...

But we can't (I don't think) make Col1 an Identity column since Col3 already is an identity column. How can we leave col3 as an identity column, and automatically increment Col1, leaving the existing columns intact.

(The basic SQL comand is, of course,
INSERT into DB2..TBL1 (col1 col2 col3)
SELECT col1 col2 col3 from DB1..TBL1
where DB1..TBL1.Col1 not in (select Col1 from DB2.TBL1)

We're just need a way to populate Col1 in DB2 with the next sequential value. Thanks in advance for your help!

 

[JohnDTampaBay]

So COL3 is the identity and COL1 is an incrementing integer unique to each row? Are they the same number?

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[35mph]

Yes. No.

I basicaically got around this by exporting the data for these tables from DB1 and DB2 to separate Excel spreadsheets, leaving COL1 the same for Excel.DB2, and just re-numbering COL1 in Excel.DB1, starting at the next highest number above that which existed in Database.DB2... then combining them, so that Excel.COL1 was unique to each row... then just renumbering Excel.COL3 in the combined spreadsheet sequentially.

Database.COL3 can just be a sequential number, whereas Database.COL1, while unique for each row, has meaning... that is, it is referenced elsewhere in the database, and that's why I had to preserve the original values in Database.DB2... but it was OK to use NEW values for the new rows inserted into Database.DB2.

I deleted the existing rows in the real database table (not the Excel spreadsheet), then imported the combined spreadsheet.

This worked, but sure seemed like the long way around. Oh, well... Thanks for your interest... If I missed something obvious, please advise.

 

[maswien]

I don't think Excel can be a good solution for your case.
It looks like you are implementing replication by yourself,

If the DB1 is the only source updating the DB2, then you don't need exactly the same
structure for DB2.tbl1

let's assume you have DB1.tbl1 as

Col1: primary key integer, with identity
Col2:
Col3:

you can define DB2.tbl1 as

Col1: primary key integer, without identity
Col2:
Col3:

then you can use the query to insert into DB2, any issues you don't use identity column in DB2?

 

[35mph]

Thanks for your reply...

First, the schema you wrote is not exactly correct.

It is:
DB1.tbl
Col1: primary key integer
Col2:
Col3: Identity

DB2.tbl
Col1: primary key integer
Col2:
Col3: Identity

Yes, I must leave the schema as it is...