sql根据条件从另一个表插入行

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?

如何将表2中的数据插入表2中,表2中没有表1的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'

2 个解决方案

#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.

您的问题中的措辞有点令人困惑,因为您首先询问如何将数据插入表2中的表1,但随后您将显示表2的所需结果。

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

现在,如果要将table1中的行插入到table2中,而table2中不存在的id可以这样做

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

这是SQLFiddle演示

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

这是SQLFiddle演示

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:

这里有一些代码可以在SSMS中运行,它们将通过以下方式举例说明此代码:

• Setting up temp tables

设置临时表

• Filling the temp tables with data (taken from peterm's SQL Fiddle however heavily modified)

使用数据填充临时表(取自peterm的SQL Fiddle但经过大量修改)

• 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;