I want to move data from table1 to table2 (both the tables have same structure)with condition of comparing their emaild (unquie)in both the tables...if the data of emaild exits in table 2 I should not load that data otherwise I have to insert the record in table 2..these two tables are in two different databases of same sql server instance... Could you please provide me with the syntax.. Thanks in advance
Asked
Active
Viewed 190 times
-3
-
Does this answer your question? [SQL - Insert Where Not Exists](https://stackoverflow.com/questions/46779223/sql-insert-where-not-exists) – Charlieface Oct 29 '21 at 12:25
1 Answers
2
INSERT db2.dbo.table2(key, other cols)
SELECT key, other cols
FROM db1.dbo.table1 AS t1
WHERE NOT EXISTS
(
SELECT 1
FROM db2.dbo.table2 AS t2
WHERE t2.key = t1.key
);
You can double-check you're getting the right rows by commenting out the first line.
Here's an example with a procedure on db<>fiddle.
Aaron Bertrand
- 272,866
- 37
- 466
- 490
-
-
3@VinithKumar What do you mean? You asked a generic question, how do I do this type of insert. This is how you do it. If you need to do this in a stored procedure, ok, wrap a `CREATE PROCEDURE` around it. If you need to learn how to create a procedure, there are many tutorials all over the Internet. – Aaron Bertrand Oct 29 '21 at 12:05
-
-
How does the table gets iterated to the second row if we compare only 1st row from the table – Vinith Kumar Nov 01 '21 at 05:55
-
@VinithKumar I'm really not understanding your question. Where are we only comparing a single row? – Aaron Bertrand Nov 01 '21 at 12:39