5

I need to copy all data from a table to an another same schema table. If the new table has a row already then i need not to update or insert it. I need only new data from old table that does not exist in new table to copy to that new table.

So what will be the sql query, can any one help me.

Thanks, Muntasir Rahman Rafi

Dzianis Yafimau
  • 2,034
  • 1
  • 27
  • 38
raficsedu
  • 361
  • 2
  • 5
  • 12
  • 2
    ***SQL*** is just the *Structured Query Language* - a language used by many database systems, but not a a database product... many things are vendor-specific - so we really need to know what **database system** (and which version) you're using (please update tags accordingly).... also: please **SHOW US** the table structures! – marc_s Jul 09 '13 at 05:29
  • sometimes people call Microsof'ts SSMS (SQL Server management server) just "SQL" we do it all the time where I work – JosephDoggie Oct 08 '15 at 20:42
  • technically, the language that SSMS uses to implement SQL is called TSQL, but again we very rarely use that usage where I work – JosephDoggie Oct 08 '15 at 20:43

2 Answers2

14

try this :

INSERT INTO TABLENAME2 (col1, Col2, col3)
SELECT *
FROM tablename1 A
WHERE NOT EXISTS (
          SELECT *
          FROM tablename2 B
          WHERE A.col1 = B.col1
     )
Devart
  • 119,203
  • 23
  • 166
  • 186
Ahmed
  • 452
  • 3
  • 7
5

Try this one -

INSERT INTO new_table(...)
SELECT *
FROM old_table AS o
WHERE o.ID NOT IN (
     SELECT n.ID FROM new_table AS n
)

Or this -

INSERT INTO new_table(...)
SELECT o.*
FROM old_table AS o
LEFT JOIN new_table AS n ON n.ID = o.ID
WHERE n.ID IS NULL
Devart
  • 119,203
  • 23
  • 166
  • 186