sql insert rows from another table based on condit

问题:

Consider the following two tables

Table 1

ID    DATA
1      'A'
2      'B'
3      'C'
4      'D'
5      'E'
6      'F'

Table 2

ID    DATA
2       'G'
3       'F'
4       'Q'

How do I insert data into Table 1 from Table 2 where Table 2 doesn't have Table 1's ID?

In other words, I'd like the following result:

Table 2

ID    DATA
1       'A'
2       'G'
3       'F'
4       'Q'
5       'E'
6       'F'

回答1:

The wording in your question a little bit confusing because you first ask How do I insert data into Table 1 from Table 2 but then you're showing the desired result for Table2.

Now if you want to insert rows from table1 into table2 with ids that doesn't exist in table2 you can do it this way

INSERT INTO Table2 (id, data)
SELECT id, data
  FROM Table1 t
 WHERE NOT EXISTS
(
  SELECT * 
    FROM Table2
   WHERE id = t.id
)

Here is SQLFiddle demo

or

INSERT INTO Table2 (id, data)
SELECT t1.id, t1.data
  FROM Table1 t1 LEFT JOIN Table2 t2
    ON t1.id = t2.id
 WHERE t2.id IS NULL;

Here is SQLFiddle demo

Outcome (in both cases):

| ID | DATA |
|----|------|
|  1 |    A |
|  2 |    G |
|  3 |    F |
|  4 |    Q |
|  5 |    E |
|  6 |    F |

回答2:

INSERT INTO Table2 (ID, DATA)
SELECT a.ID, a.DATA 
FROM Table1 a
JOIN (
        SELECT ID FROM Table1
        EXCEPT
        SELECT ID FROM Table2
    ) b
    ON b.ID = a.ID
;

Here some code you can run in SSMS that will exemplify this code by:

• Setting up temp tables
• Filling the temp tables with data (taken from peterm's SQL Fiddle however heavily modified)
• Executing the above
• Then cleaning up after itself by destroying the temp tables

.

PRINT 'Dropping and creating temp tables';

IF OBJECT_ID('tempdb.dbo.#t1') IS NOT NULL DROP TABLE #t1;
IF OBJECT_ID('tempdb.dbo.#t2') IS NOT NULL DROP TABLE #t2;

CREATE TABLE #t1 (
    ID INT, DATA VARCHAR(1)
);

CREATE TABLE #t2 (
    ID INT, DATA VARCHAR(1))
;

PRINT 'Temp tables created';
PRINT 'Inserting test data into temp tables';

INSERT INTO #t1 (
    ID, DATA
)
VALUES
     (1, 'A')
    ,(2, 'B')
    ,(3, 'C')
    ,(4, 'D')
    ,(5, 'E')
    ,(6, 'F')
;


INSERT INTO #t2 (
    ID, DATA
) 
VALUES
     (2, 'G')
    ,(3, 'F')
    ,(4, 'Q')
;

PRINT 'Test data inserted';
PRINT 'SELECTING temp tables before modifying data'


SELECT * FROM #t1;
SELECT * FROM #t2;

PRINT 'Modifying data'

INSERT INTO #t2 (ID, DATA)
SELECT a.ID, a.DATA 
FROM #t1 a
JOIN (
        SELECT ID FROM #t1
        EXCEPT
        SELECT ID FROM #t2
    ) b
    ON b.ID = a.ID
;

PRINT 'SELECTING "after" modifying data'

SELECT * FROM #t1
SELECT * FROM #t2

PRINT 'Cleaning up (destroying temp tables)'

IF OBJECT_ID('tempdb.dbo.#t1') IS NOT NULL DROP TABLE #t1;
IF OBJECT_ID('tempdb.dbo.#t2') IS NOT NULL DROP TABLE #t2;