TSQL - Copy data from one table to another

Question

I'm copying the contents of a table into another identical table. But there are already data in the destination table.

Some data in the destination table has the same code as the source table.

Is it possible to skip the duplicates and not to block the insertion for the rest of the data without it failing?

insert into [DB2].[dbo].[MAN] values([MAN],[DES])
SELECT [MAN]
      ,[DES]
FROM [DB1].[dbo].[MAN]

Please add sample data which shows what you mean by duplicate here. — Tim Biegeleisen, Jan 16 '19 at 11:21

score 1 · Accepted Answer · answered Jan 16 '19 at 11:22

1

You can use NOT EXISTS :

INSERT INTO [DB2].[dbo].[MAN] ([MAN], [DES])
    SELECT M.[MAN], M.[DES]
    FROM [DB1].[dbo].[MAN] AS M
    WHERE NOT EXISTS (SELECT 1 FROM [DB2].[dbo].[MAN] M1 WHERE M1.COL = M.COL);

You need to change the M1.COL = M.COL with your actual column name from which you can identify the duplicate values.

answered Jan 16 '19 at 11:22

Yogesh Sharma

49,870
5
26
52

Faster than me :-) Though I would also mention that in a multi-user environment such a script might fail due to a race condition. Read [my self-answered related question for details.](https://stackoverflow.com/questions/38497259/what-is-the-best-practice-for-inserting-a-record-if-it-doesnt-already-exist) – Zohar Peled Jan 16 '19 at 11:24

score 0 · Answer 2 · answered Jan 16 '19 at 14:58

If you have your unique col then you can go like this.

insert into [DB2].[dbo].[MAN] values([MAN],[DES])
SELECT [MAN]
      ,[DES]
FROM [DB1].[dbo].[MAN] WHERE uniqueCol NOT IN (SELECT uniqueCol FROM [DB2].[dbo].[MAN])

Otherwise append few columns to get unique one and compare like that.

TSQL - Copy data from one table to another

2 Answers2