SQL 重复数据插入

使用NOT EXISTS：

INSERT INTO TABLE_2

(id, name)

SELECT t1.id,

t1.name

FROM TABLE_1 t1

WHERE NOT EXISTS(SELECT id

FROM TABLE_2 t2

WHERE t2.id = t1.id)

使用NOT IN：

INSERT INTO TABLE_2

(id, name)

SELECT t1.id,

t1.name

FROM TABLE_1 t1

WHERE t1.id NOT IN (SELECT id

FROM TABLE_2)

使用LEFT JOIN/IS NULL：

INSERT INTO TABLE_2

(id, name)

SELECT t1.id,

t1.name

FROM TABLE_1 t1

LEFT JOIN TABLE_2 t2 ON t2.id = t1.id

WHERE t2.id IS NULL

--tb1为测试表，替换为table1

WITH tb1 AS

(SELECT 1 AS u_id,2 AS num

UNION SELECT 2,3

UNION SELECT 3,4)

,tb2 AS 

(SELECT u_id,num-1 AS num FROM tb1

UNION ALL

SELECT u_id,num-1 FROM tb2 WHERE num>0)

--查询插入table2的结果集

SELECT u_id,0 AS sum FROM tb2 ORDER BY u_id,num

t-sql

一次插入多条数据，除了复制其它表外， 只有重复地写insert. 如果有规律可以用WHILE 循环。

insrt into GOODS (GoodsName,Price，AreaId)values('aaa','bbb','ccc1')

insrt into GOODS (GoodsName,Price，AreaId)values('aaa','bbb','ccc2')

insrt into GOODS (GoodsName,Price，AreaId)values('aaa','bbb','ccc3')

insrt into GOODS (GoodsName,Price，AreaId)values('aaa','bbb','ccc4')

insrt into GOODS (GoodsName,Price，AreaId)values('aaa','bbb','ccc5')

insrt into GOODS (GoodsName,Price，AreaId)values('aaa','bbb','ccc6')

.................

---