如何一次插入多条不重复的数据?

[gddd]

数据库MSSQL2008R2,VB6或ASP写的程序,SqlBulkCopy似乎很麻烦好像也无法使用.

表结构
id,md5,name

插入多条md5数据.
如0f0f010,0fEf010,0f0fA10,0f0fB10,0f0f210,有对应的name列名如a,b,c,d,e(这个不重要)

希望能一次性插入这5条数据到md5列,并且重复的话就不要插入了,同时把name的值输入.

求解.谢谢

[gddd]

额.最好是效率高点的.

[gddd]

insert into test表名(md5id,tname) select * from (
select 'e4bd629300c5ed376d8bdab5c94e0f5fea33bcf5e' as md5id,'A' as tname
union all
select 'e4bd629300c5198bd05dacd2fea6503bbdf91b0d5' as md5id,'B' as tname
union all
select 'beee9d1bd4e7e507c5750da55e7f9c215f5249292' as md5id,'C' as tname
union all
select 'e4bd629300c59df21ae6043bf8a5be8803bb4d31a' as md5id,'D' as tname
union all
select 'e4bd629300c5e52c36abf380024035585f39802d1' as md5id,'E' as tname
) t where not exists( select 1 from test表名 where md5id=t.md5id )

这条可以实现，不过不知道效率如何。

[开着拖拉机泡妞]

还行吧。你试试就知道了

[gddd]

遇到个问题...
临时数据里,可能会出现重复值,而原表(test表名)是拒绝重复值的做了INDEX的.
1.不确定原表是否已有MD5ID值(唯一index).
2.不确定临时数据是否有MD5ID的重复值.

insert into test表名(md5id,tname) select * from (
select 'e4bd629300c5ed376d8bdab5c94e0f5fea33bcf5e' as md5id,'A' as tname
union all
select 'e4bd629300c5ed376d8bdab5c94e0f5fea33bcf5e' as md5id,'A' as tname
union all
select 'beee9d1bd4e7e507c5750da55e7f9c215f5249292' as md5id,'C' as tname
union all
select 'e4bd629300c5ed376d8bdab5c94e0f5fea33bcf5e' as md5id,'A' as tname
union all
select 'e4bd629300c5e52c36abf380024035585f39802d1' as md5id,'E' as tname
) t where not exists( select 1 from test表名 where md5id=t.md5id )

[gddd]

DISTINCT可以解决,但如果有1千万数据往6千万的主表里插入,会不会很慢?

insert into test表名(md5id,tname) select DISTINCT md5id,tname from (
select 'e4bd629300c5ed376d8bdab5c94e0f5fea33bcf5e' as md5id,'A' as tname
union all
select 'e4bd629300c5ed376d8bdab5c94e0f5fea33bcf5e' as md5id,'A' as tname
union all
select 'beee9d1bd4e7e507c5750da55e7f9c215f5249292' as md5id,'C' as tname
union all
select 'e4bd629300c5ed376d8bdab5c94e0f5fea33bcf5e' as md5id,'A' as tname
union all
select 'e4bd629300c5e52c36abf380024035585f39802d1' as md5id,'E' as tname
) t where not exists( select 1 from test表名 where md5id=t.md5id )

[gddd]

晕了.怎么还是有问题?

insert into test表名(md5id,tname)
select DISTINCT t.md5id,t.tname from 可能会有重复值的临时表 t
where not exists(select 1 from test表名 where md5s=t.md5s)

消息 2601，级别 14，状态 1，第 2 行
不能在具有唯一索引 'IX_md5id' 的对象 'dbo.test表名' 中插入重复键的行。
语句已终止。

[tw_ddm]

你的not exists有问题，并没有排除掉已经存在的md5id