0

Inserting data from one table to another is usually as simple as:

SELECT * INTO A FROM B

But just out of curiosity,suppose I have two tables tbl_A and tbl_B. I have 100 records in tbl_B and some 20 rows in tbl_A (some of which might be common in both tables), I want to insert rows from tbl_B into tbl_A which are not already present in tbl_A'

Also, lets assume that both table have identity fields.

SamuraiJack
  • 5,131
  • 15
  • 89
  • 195

3 Answers3

1

You can use MERGE command

Description in MS tech

http://msdn.microsoft.com/en-us/library/bb510625.aspx

DimaSUN
  • 921
  • 8
  • 13
0

You can use NOT EXISTS:

INSERT INTO tbl_A 
    SELECT IdCol, Col2, Col3
    FROM dbo.tbl_B B
    WHERE NOT EXISTS(SELECT 1 FROM tbl_A A2 WHERE A2.IdCol = B.IdCol)
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • I tried `INSERT INTO Tbl_BankEntry_exp SELECT * FROM dbo.Tbl_BankEntry B WHERE NOT EXISTS(SELECT 1 FROM Tbl_BankEntry_exp A2 WHERE A2.srno = B.srno)` But it gave me `An explicit value for the identity column in table 'Tbl_BankEntry_exp' can only be specified when a column list is used and IDENTITY_INSERT is ON.` – SamuraiJack Jan 14 '14 at 10:03
  • 1
    @Arbaaz: here's the answer to that problem: http://stackoverflow.com/questions/2005437/an-explicit-value-for-the-identity-column-in-table-can-only-be-specified-when-a – Tim Schmelter Jan 14 '14 at 10:13
  • Thanks Tim. btw I am assuming that `SELECT 1 FROM` selects rows from the first mentioned table? Right? – SamuraiJack Jan 14 '14 at 12:20
  • It selects nothing at all, the EXISTS just checks if there are rows in Table_A. – Tim Schmelter Jan 14 '14 at 12:29
  • I am not talking about EXISTS I am talking about `SELECT 1 FROM` why `1`? – SamuraiJack Jan 14 '14 at 16:12
  • 1
    @Arbaaz: it doesn't matter what you "Select" since you select nothing anyway. You could also use `SELECT *` or `SELECT NULL`. I prefer `SELECT 1`. That's just the way `EXISTS` works. http://stackoverflow.com/questions/6137433/where-does-the-practice-exists-select-1-from-come-from – Tim Schmelter Jan 14 '14 at 16:14
0

I think this should work. 

INSERT INTO tbl_A (IdCol, Col2, Col3)
(SELECT IdCol, Col2, Col3 FROM dbo.tbl_B AS b 
WHERE b.IdCol NOT IN (SELECT Distinct IdCol FROM tbl_A))
Dylan Cross
  • 544
  • 2
  • 6
  • 14