0

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.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Jata Thakur
  • 5
  • 2
  • Possible duplicate of [How to avoid duplicates in INSERT INTO SELECT query in SQL Server?](http://stackoverflow.com/questions/2513174/how-to-avoid-duplicates-in-insert-into-select-query-in-sql-server) – Mr. Bhosale Nov 15 '16 at 07:26

3 Answers3

1

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)
Mr. Bhosale
  • 3,018
  • 1
  • 17
  • 34
0

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

Sergio.Gaspar
  • 66
  • 4
0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2799436
  • 36
  • 1
  • 4
  • Thanks User2799436, Mr. Bhosale and Sérgio. I have created a linked server and used the EST task and used the above query which you have posted. – Jata Thakur Nov 16 '16 at 12:04