把一张表的数据批量插入到另外一张表
<%
Server.ScriptTimeOut=600
Set conn=Server.CreateObject("ADODB.Connection")
if err.number <>0 then
err.clear
else
conn.open "Driver={SQL Server};Server=local,1433;UID=sa;PWD=!~@~@#$$432234$$543!;database=DATA;"
end if
set rs=server.createobject("adodb.recordset")
rs.open "select CI_Keywords from TE_CorpInfo where ci_id > 13000 and ci_id <14000",conn,1,1
if not rs.eof and not rs.bof then
for i=1 to rs.recordcount
'response.Write(rs.recordcount)
'response.end
aaa= rs.fields("CI_Keywords").value
bbb=split(aaa," ")
for j=1 to ubound(bbb)-1
set rskey = server.CreateObject("Adodb.Recordset")
rskey.open "select * from SS",conn,1,3
rskey.addnew
rskey("KeyName")= bbb(j)
rskey.update
rskey.close
set rskey=nothing
next
rs.movenext
next
end if
rs.close
conn.close
set conn=nothing
response.Write("success1~~~")
%>
拆分表:
--> --> (Roy)生成測試數據
if not object_id('Tab') is null
drop table Tab
Go
Create table Tab([Col1] int,[COl2] nvarchar(5))
Insert Tab
select 1,N'a,b,c' union all
select 2,N'd,e' union all
select 3,N'f'
Go
SQL2000用辅助表:
if object_id('Tempdb..#Num') is not null
drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
Select
a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID)
from
Tab a,#Num b
where
charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','
SQL2005用Xml:
select
a.COl1,b.Col2
from
(select Col1,COl2=convert(xml,' <root> <v>'+replace(COl2,',',' </v> <v>')+' </v> </root>') from Tab)a
outer apply
(select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b
SQL05用CTE:
;with roy as
(select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab
union all
select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>''
)
select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)
生成结果:
/*
Col1 COl2
----------- -----
1 a
1 b
1 c
2 d
2 e
3 f
*/
if not object_id('Tab') is null drop table TabGoCreate table Tab([COl2] nvarchar(20))Insert Tabselect N'sky blue water'Goif object_id('Tempdb..#Num') is not null drop table #Numgoselect top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns bSelect COl2=substring(replace(a.Col2, ' ', ','),b.ID,charindex(',',replace(a.Col2, ' ', ',')+',',b.ID)-b.ID) from Tab a,#Num bwhere charindex(',',','+replace(a.Col2, ' ', ','),b.ID)=b.ID /*COl2------------------------skybluewater*/
[解决办法]
Create table Tab(CI_ID int, CI_Keywords nvarchar(20))Insert TabSELECT 10001, 'sky blue water' UNION ALL SELECT 10002, 'book apple shirt' UNION ALL SELECT 10005, 'cup yellow org' UNION ALL SELECT 61245, 'box phone paper'Gocreate table SSS(KeyID int, KeyName varchar(20))goif object_id('Tempdb..#Num') is not null drop table #Numgoselect top 40000 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns bSelect id=identity(int,1,1) , COl2=substring(replace(a.CI_Keywords, ' ', ','),b.ID,charindex(',',replace(a.CI_Keywords, ' ', ',')+',',b.ID)-b.ID) into #from Tab a,#Num bwhere charindex(',',','+replace(a.CI_Keywords, ' ', ','),b.ID)=b.IDinsert SSS select * from #SELECT * FROM SSSdrop table Tab,SSS,# /*KeyID KeyName----------- --------------------1 sky2 book3 cup4 box5 blue6 yellow7 phone8 apple9 water10 paper11 shirt12 org(12 row(s) affected)*/
[解决办法]
--修改排序Create table Tab(CI_ID int, CI_Keywords nvarchar(20))Insert TabSELECT 10001, 'sky blue water' UNION ALL SELECT 10002, 'book apple shirt' UNION ALL SELECT 10005, 'cup yellow org' UNION ALL SELECT 61245, 'box phone paper'Gocreate table SSS(KeyID int, KeyName varchar(20))goif object_id('Tempdb..#Num') is not null drop table #Numgoselect top 40000 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns bSelect id=identity(int,1,1) , COl2=substring(replace(a.CI_Keywords, ' ', ','),b.ID,charindex(',',replace(a.CI_Keywords, ' ', ',')+',',b.ID)-b.ID) into #from Tab a,#Num bwhere charindex(',',','+replace(a.CI_Keywords, ' ', ','),b.ID)=b.IDorder by CI_ID,b.idinsert SSS select id,col2 from # SELECT * FROM SSSdrop table Tab,SSS,# /*KeyID KeyName----------- --------------------1 sky2 blue3 water4 book5 apple6 shirt7 cup8 yellow9 org10 box11 phone12 paper(12 row(s) affected)*/
------解决方案--------------------
--修改了一下，我试了6W多条数据十几秒，你可以把分拆和插入分开试看是哪里慢了：
--SS表用标识列，这样不必用临时表#
--不必用replace
Create table Tab(CI_ID int, CI_Keywords nvarchar(20))Insert TabSELECT 10001, 'sky blue water' UNION ALL SELECT 10002, 'book apple shirt' UNION ALL SELECT 10005, 'cup yellow org' UNION ALL SELECT 61245, 'box phone paper'Gocreate table SSS(KeyID int identity(1,1), KeyName varchar(20))go/*declare @rowcount intset @rowcount=4WHILE @rowcount<40000begin insert Tab select * from Tab set @rowcount=@rowcount+@@rowcountend*/select top 40000 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b--insert SSSSelect COl2=substring(a.CI_Keywords, b.ID,charindex(' ',a.CI_Keywords+' ',b.ID)-b.ID)from Tab a,#Num bwhere ID<len(CI_Keywords) and charindex(' ', ' '+a.CI_Keywords, b.ID)=b.ID--SELECT * FROM SSSdrop table Tab,#Num,SSS
[解决办法]
据我所知sql server 的sql目前没有一个很好的方法实现split，计算量都有点大
看了，楼主的程序，感觉可以先考虑优化一下这段vb，你这段vb效率很差，我帮你简单改了一下，意思就是不要在插入每条记录时都打开、更新、关闭记录集。
如果这样还不理想，建议你可以考虑用vb生成带分隔符的文本文件，然后传到sqlserver指定目录再调用bcp 或bulk insert 完成导入，性能应该会更好的
set rs=server.createobject("adodb.recordset")'在循环外面打开插入记录集set rskey = server.CreateObject("Adodb.Recordset")rskey.open "select * from SS",conn,1,3 rs.open "select CI_Keywords from TE_CorpInfo where ci_id > 13000 and ci_id<14000",conn,1,1 if not rs.eof and not rs.bof then for i=1 to rs.recordcount 'response.Write(rs.recordcount) 'response.end aaa= rs.fields("CI_Keywords").value bbb=split(aaa," ") for j=1 to ubound(bbb)-1 rskey.addnew rskey("KeyName")= bbb(j) rskey.update next rs.movenext next end if'关闭也是rskey.closeset rskey=nothingrs.close conn.closeset conn=nothingresponse.Write("success1~~~")