Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 474
  • Last Modified:

SQL SERVER - does not exist in - Help with sql insert

Hi..
I have 2 identical tables   A and B. both with an ID

I want to insert the records from B into A that are not in A.

what is the correct SQL syntax

thx
0
JElster
Asked:
JElster
3 Solutions
 
Surendra NathTechnology LeadCommented:
try this out

INSERT INTO A
SELECT * FROM B 
WHERE NOT EXISTS ( SELECT 1 FROM A WHERE A.id = B.id)

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Just to be safe...

INSERT INTO A (id, column1, column2, ..., columnN)
SELECT id, column1, column2, ..., columnN
FROM B
   LEFT JOIN A ON B.id = a.id
WHERE a.id IS NULL

The above FROM .. WHERE NOT EXISTS will also work, although I would recommend

WHERE NOT EXISTS ( SELECT id FROM A WHERE A.id = B.id)
0
 
PadawanDBAOperational DBACommented:
assuming the IDs are aligned the following should work...

insert into A
  select
      *
  from
      B as B
           left join A as A on B.ID = A.ID
  where
      A.ID is null;

Open in new window


Edit:
As an addendum, if the ID is an identity, you will have to turn identity insert on and may have to reseed the identity column
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now