CODEWITHSUNDEEP
sqlsql-server
Luis Pinto
Luis Pinto

Reputation: 11

Insert records from one table on another table incrementing id

How can I insert records from one table(table1) to the same table(table2) in a different SQL database?

However the destination table already exists and had records with the same id(c1). the records of the table1 must be added to table2, but I have to increment the field (c1).

table 1

C1;C2;C3;
1;Name 1;Address 1;
2;Name 2;Address 2;
3;Name 3;Address 3;
4;Name 4;Address 4;

table 2

C1;C2;C3 ;
1;Name 11;Address 11;
2;Name 12;Address 12;
3;Name 13;Address 13;
4;Name 14;Address 14;
5;Name 1;Address 1;
6;Name 2;Address 2;
7;Name 3;Address 3;
8;Name 4;Address 4;

what is wrong? table cl is the same in both databases and as the following columns: (clstamp, nome, no, estab, vendnm, ncont)

INSERT INTO DB2..cl(clstamp, nome, estab, vendnm, ncont)
    select clstamp, nome, estab, vendnm, ncont
      FROM         [DB1]..[cl]
      LEFT JOIN    [DB2]..[cl] ON [DB1]..[cl].clstamp=[DB2]..[cl].clstamp 
      and [DB1]..[cl].nome=[DB2]..[cl].nome 
      and [DB1]..[cl].estab=[DB2]..[cl].estab 
      and [DB1]..[cl].vendm=[DB2]..[cl].vendnm 
      and [DB1]..[cl].ncont=[DB2]..[cl].ncont
    where [DB1]..cl.ncont not in (select ncont from [DB2]..cl) 
    order by [DB2]..[cl].no

Upvotes: 1

Views: 754

Answers (3)

Ci Zhu
Ci Zhu

Reputation: 31

If you didn't manually add any settings like IDENTITY_INSERT, all the records inserted would naturally be granted with incremental IDs.

As you described, the problem you have here is the duplicate records that violates your PRIMARY KEY constraint.

The most straightforward way is using a MERGE statement. MERGE INTO Table 2 USING Table 1. WHEN MATCHED do nothing and insert the records when UNMATCHED

Also, be aware that if you DELETE any records, the deleted ID won't be recycled for the newly inserted rows. Thus check the table if you want the original IDs and the inserted IDs flow continuously

Upvotes: 0

sam
sam

Reputation: 1985

If you want to avoid inserting duplicate rows, please try this: 

INSERT INTO database2..myTable2 (C2, C3)
        select t1.c2, t1.c3
          FROM         [database1]..[table1] t1
          LEFT JOIN    [database2]..[table2] t2 ON t1.C2 = t2.C2 and t1.C3 = t2.C3
        where t2.c2 is null and t2.c3 is null
        order by t1.C1

If you are okay with duplicates then the solution is already mentioned by Angelo.

I tried with temp tables and working code is below:

IF (OBJECT_ID('tempdb..#myTable1') IS NOT NULL)
BEGIN
   DROP TABLE #myTable1
END;

IF (OBJECT_ID('tempdb..#myTable2') IS NOT NULL)
BEGIN
   DROP TABLE #myTable2
END;

CREATE TABLE #myTable1 ([C1] int identity(1,1), [C2]  varchar(10),  [C3]  varchar(10));

CREATE TABLE #myTable2 ([C1] int identity(1,1), [C2]  varchar(10),  [C3]  varchar(10));

Insert INTO #myTable1(C2, C3)
values
 ('Name 1', 'Address 1')
, ('Name 2', 'Address 2')
, ('Name 3', 'Address 3')
, ('Name 4', 'Address 4')

Insert INTO #myTable2(C2, C3)
values
  ('Name 11', 'Address 11')
, ('Name 12', 'Address 12')
, ('Name 13', 'Address 13')
, ('Name 14', 'Address 14')

select * from #myTable1
select * from #myTable2

INSERT INTO #myTable2 (C2, C3)
    select t1.c2, t1.c3
      FROM         [#myTable1] t1
      LEFT JOIN    [#myTable2] t2 ON t1.C2 = t2.C2 and t1.C3 = t2.C3
    where t2.c2 is null and t2.c3 is null
    order by t1.C1

select * from #myTable2

Upvotes: 0

Angelo
Angelo

Reputation: 1656

If your databases are on the same server, and table 2 has column C1 marked as an identity column, then you can generally execute a straightforward SQL statement:

INSERT INTO database2..table2(C2, C3)
SELECT C2, C3
FROM database1..table1
ORDER BY C1 ASC

Upvotes: 1

Related Questions