有两张表!
TableA
col1 col2 col3 col4
1 a
2 b
3 c
TableB
col1 col2 col3 col4
1 a aa aa
2 b bb bb
3 c cc cc我现在要把表B里面的数据导入到表A里面去! 但导进去后,里面有重复的数据! 我想把B表里面的数据覆盖到A表里面去!最后TableA里面显示
TableA
col1 col2 col3 col4
1 a aa aa
2 b bb bb
3 c cc cc用一段SQL语句,如何实现呢,小弟在此先谢过了!!
TableA
col1 col2 col3 col4
1 a
2 b
3 c
TableB
col1 col2 col3 col4
1 a aa aa
2 b bb bb
3 c cc cc我现在要把表B里面的数据导入到表A里面去! 但导进去后,里面有重复的数据! 我想把B表里面的数据覆盖到A表里面去!最后TableA里面显示
TableA
col1 col2 col3 col4
1 a aa aa
2 b bb bb
3 c cc cc用一段SQL语句,如何实现呢,小弟在此先谢过了!!
update tablea set col3=b.col3,col4=b.col4 from tablea inner join tableb on tablea.col1=tableb.col1
and tablea.col2=tableb.col2
and a.col2=b.col2
A.COL2=B.COL2
INSERT TABLEA
SELECT 1 , 'a' ,'',''UNION ALL
SELECT 2 , 'b' ,'',''UNION ALL
SELECT 3 , 'c' ,'',''
--DROP TABLE TABLEB
CREATE TABLE TABLEB(COL1 INT ,COL2 VARCHAR(2),COL3 VARCHAR(2),COL4 VARCHAR(2))
INSERT TABLEB
SELECT 1 , 'a' ,'AA','AA'UNION ALL
SELECT 2 , 'b' ,'BB','BB'UNION ALL
SELECT 3 , 'c' ,'CC','CC'SELECT * FROM TABLEA
SELECT * FROM TABLEBUPDATE TABLEA SET COL1=B.COL1,COL2=B.COL2,COL3=B.COL3,COL4=B.COL4 FROM TABLEB B WHERE TABLEA.COL1=B.COL1
SELECT * FROM TABLEA
COL1 COL2 COL3 COL4
----------- ---- ---- ----
1 a AA AA
2 b BB BB
3 c CC CC(所影响的行数为 3 行)
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-06-15 22:53:12
----------------------------------------------------------------
--> 测试数据:[TableA]
if object_id('[TableA]') is not null drop table [TableA]
create table [TableA]([col1] int,[col2] varchar(1),[col3] sql_variant,[col4] sql_variant)
insert [TableA]
select 1,'a',null,null union all
select 2,'b',null,null union all
select 3,'c',null,null
--> 测试数据:[TableB]
if object_id('[TableB]') is not null drop table [TableB]
create table [TableB]([col1] int,[col2] varchar(1),[col3] varchar(2),[col4] varchar(2))
insert [TableB]
select 1,'a','aa','aa' union all
select 2,'b','bb','bb' union all
select 3,'c','cc','cc'
--------------开始查询--------------------------
UPDATE [TableA] SET COL1=B.COL1,COL2=B.COL2,COL3=B.COL3,COL4=B.COL4 FROM [TableB] B WHERE TABLEA.COL1=B.COL1
select * from [TableA]
----------------结果----------------------------
/*
COL1 COL2 COL3 COL4
1 a aa aa
2 b bb bb
3 c cc cc*/
insert [TableA]
select 1,'a',null,null union all
select 2,'b',null,null union all
select 3,'c',null,nullcreate table [TableB]([col1] int,[col2] varchar(1),[col3] varchar(2),[col4] varchar(2))
insert [TableB]
select 1,'a','aa','aa' union all
select 2,'b','bb','bb' union all
select 3,'c','cc','cc'
UPDATE TABLEA
SET COL3=B.COL3,COL4=B.COL4
FROM TABLEB B
WHERE TABLEA.COL1=B.COL1 AND TABLEA.COL2=B.COL2 SELECT * FROM TABLEAcol1 col2 col3 col4
----------- ---- ---------- ----------
1 a aa aa
2 b bb bb
3 c cc cc(3 行受影响)