CODEWITHSUNDEEP
sqlsql-serverssis
Jata Thakur
Jata Thakur

Reputation: 5

Remove inserting new records which are already available in destination table

I I have 2 server 2009 and 2012 server. I have inserted data from 2012 server data to a new staging table, now i have to insert 2009 server data into same staging table. If 2009 data record matches with existing record then i dont want to insert that record and rest all other data should insert into staging.

Upvotes: 0

Views: 96

Answers (3)

user2799436
user2799436

Reputation: 36

Create a linked server for server 2008. Write an OPENQUERY to access the server 2008 table.

Now Use the Execute SQL task of SSIS and use SQL Server 2008 table except server 2012 table.

Upvotes: 0

Sergio.Gaspar
Sergio.Gaspar

Reputation: 66

Just an alternative to the answer given using joins instead of exits:

INSERT INTO TABLE_2
  (id, name)
SELECT t1.id,
       t1.name
  FROM TABLE_1 t1 LEFT JOIN TABLE_2 t2 ON t1.id = t2.id
where t2.id = null

This can be easier to read when multiple staging tables or "complementary" tables (add data from other tables joined).

Regards,
Sérgio

Upvotes: 0

Mr. Bhosale
Mr. Bhosale

Reputation: 3096

Try below Query

Using NOT EXISTS:

INSERT INTO TABLE_2
  (id, name)
SELECT t1.id,
       t1.name
  FROM TABLE_1 t1
 WHERE NOT EXISTS(SELECT id
                    FROM TABLE_2 t2
                   WHERE t2.id = t1.id)

Using NOT IN:

INSERT INTO TABLE_2
  (id, name)
SELECT t1.id,
       t1.name
  FROM TABLE_1 t1
 WHERE t1.id NOT IN (SELECT id
                       FROM TABLE_2)

Upvotes: 1

Related Questions