110,566
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE Test1(A INT PRIMARY KEY, B NVARCHAR(10));
INSERT Test1
SELECT 1, '11' UNION ALL
SELECT 2, '22' UNION ALL
SELECT 3, '33';
CREATE TABLE Test2(A INT PRIMARY KEY, B NVARCHAR(10));
INSERT Test2;
SELECT 1, '111' UNION ALL
SELECT 4, '444' UNION ALL
SELECT 5, '555';
SELECT * FROM test1;
SELECT * FROM test2;
--批量更新
UPDATE test1
SET test1.b = test2.b
FROM test1 INNER JOIN test2 on test1.a = test2.a;
--批量修改
INSERT test1
SELECT test2.*
FROM test2 LEFT JOIN test1 ON test2.a = test1.a
WHERE test1.a IS null;
SELECT * FROM test1;
SELECT * FROM test2;
drop table test1;
drop table test2;
---更新相同数据的时间
update oldTable set CreateDate=A.CreateDate from newTable A,oldTable B where B.Title=A.Title and B.ProName=A.ProName
---插入不同数据的记录
insert into oldTable(Title,ProName,CreateDate) select Title,ProName,CreateDate from newTable A where not exists(select id from oldTable where oldTable.Title= A.Title and oldTable.ProName=A.ProName)
---这里必须先执行更新 再执行插入