2 dramagame dramagame 于 2014.11.27 11:53 提问

sql语句问题。help sos

有A B两张表里面的字段列名完全一致只有数据不一致。。现在要把A B两张中不同数据插入到A表中。数据不只一条。一条insert语句够吗?怎么做?希望大神能给条实例代码谢谢 字段写两个就够了

5个回答

lk13962517093
lk13962517093   2014.11.27 12:12
    CREATE TABLE #a(col1 VARCHAR(20),col2 VARCHAR(20))

CREATE TABLE #b(col1 VARCHAR(20),col2 VARCHAR(20))

INSERT INTO #a( col1, col2 )
SELECT 'text1','text2'
UNION SELECT 'text1','text3'
UNION SELECT 'text1','text4'

INSERT INTO #b( col1, col2 )
SELECT 'text1','text2'
UNION SELECT 'text2','text3'
UNION SELECT 'text2','text4'

SELECT * FROM #a

SELECT * FROM #b

INSERT INTO #a
        ( col1, col2 )
SELECT col1, col2
FROM #b b WHERE NOT EXISTS(SELECT 1 FROM #a a WHERE a.col1=b.col1 AND a.col2=b.col2)

SELECT * FROM #a

DROP TABLE #a

DROP TABLE #b 

col1 col2


text1 text2
text1 text3
text1 text4

col1 col2


text1 text2
text2 text3
text2 text4

col1 col2


text1 text2
text1 text3
text1 text4
text2 text3
text2 text4

luxiuwen
luxiuwen   2014.11.27 13:47

insert into tabled(cola,clob)
select a, b from (
select a,b from (
select a, b from tablea
minus
select a,b from tableb
) tablec
union
select a, b from (
select a, b from tableb
minus
select a,b from tablec
)
) table_all

luxiuwen
luxiuwen   2014.11.27 13:47

insert into tabled(cola,clob)
select a, b from (
select a,b from (
select a, b from tablea
minus
select a,b from tableb
) tablec
union
select a, b from (
select a, b from tableb
minus
select a,b from tablec
)
) table_all

xiaozhang0372
xiaozhang0372   2014.11.27 14:15

(select * from A , B where A.id<>B.id) as C,把C表中的数据插入你的A表

wll381200
wll381200   2014.11.27 16:55

直接一条insert语句足够了

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!