|
由于数据库前期没有设置主键或者索引,经常会出现sql数据库表中存在重复的记录,利用select distinct虽然也能去重,但数据量大的话估计会跑死电脑,先将自己的一段代码贴出来,或许对您有所帮助,那将是对我最大的鼓励
'去掉sql数据库重复的记录
Sub deldouble()
On Error Resume Next
Dim cnn As New ADODB.Connection
Dim cnnstr As String, sql As String
cnnstr = "provider=sqloledb;" _
& "user id=sa;" _
& "password=5200218;" _
& "data source=ZGH;" _
& "initial catalog=czsw"
cnn.ConnectionString = cnnstr
cnn.Open
sql = "select * into dwe2 from dwe where 0=1" '创建于dwe表结构相同的临时表dwe2
cnn.Execute sql
'------------------设置临时表dwe2的索引
sql = "create unique index dd on dwe2 (stcd, year, dates) with ignore_dup_key "
cnn.Execute sql
'--------------------------------------
sql = "insert into dwe2 Select * from dwe " '将dwe表中数据导入dwe2临时表,此时dwe2中存放的是去重后的数据
cnn.Execute sql
sql = "drop table dwe" '删除dwe表
cnn.Execute sql
sql = "sp_rename dwe2 , dwe" '重命民dwe2为dwe
cnn.Execute sql
cnn.Close
Set cnn = Nothing
End Sub |
|