[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • 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 NathCommented:
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
 
PadawanDBACommented:
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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