listview中的数据插入临时表,有什么方法可以把速度提高
Sub add_temp()
Dim rscol As New ADODB.Recordset
If rscol.State <> 1 Then
rscol.Open "SELECT * From tempdb.dbo.syscolumns WHERE (id = OBJECT_ID('tempdb..#temp'))", conn, 1, 1
Else
rscol.Requery
End If
If Trim(labxin.Label1.caption) <> Biao_Ming Or Trim(labxin.Label8.caption) = "按列名查询" Then
For i = 1 To (rscol.RecordCount - 7)
conn.Execute "ALTER TABLE #temp DROP COLUMN col" & i + 7
Next i
End Ifrscol.Requery
If rscol.RecordCount < 8 Then
For m = 1 To (labxin.ListView1.ColumnHeaders.Count - 7)
For i = 1 To labxin.ListView1.ListItems.Count - 1
If Trim(labxin.ListView1.ListItems(i).SubItems(m + 6)) <> "" Then Exit For
Next i
If IsNumeric(labxin.ListView1.ListItems(i).SubItems(m + 6)) = True Then
conn.Execute "alter table #temp add col" & m + 7 & " decimal(9,3)"
Else
conn.Execute "alter table #temp add col" & m + 7 & " varchar(200)"
End If
'Wend
'Next i
Next m
End If conn.Execute "delete from #temp"
For i = 1 To labxin.ListView1.ListItems.Count
conn.Execute "insert into #temp(col1,col2)values('" & Trim(labxin.ListView1.ListItems(i).Text) & "','" & Trim(labxin.ListView1.ListItems(i).SubItems(1)) & "')"
For j = 3 To labxin.ListView1.ColumnHeaders.Count
'If IsNumeric(labxin.ListView1.ListItems(i).SubItems(j - 1)) = False Then
'conn.Execute "ALTER TABLE #temp ALTER COLUMN col" & j & " varchar(200)"
'End If
conn.Execute "update #temp set col" & j & "=" & KONGTONULL(labxin.ListView1.ListItems(i).SubItems(j - 1)) & " where col2='" & Trim(labxin.ListView1.ListItems(i).SubItems(1)) & "'"
Next j
Next i
rscol.Close
End Sub
Sub add_temp()
Dim rscol As New ADODB.Recordset
If rscol.State <> 1 Then
rscol.Open "SELECT * From tempdb.dbo.syscolumns WHERE (id = OBJECT_ID('tempdb..#temp'))", conn, 1, 1
Else
rscol.Requery
End If
If Trim(labxin.Label1.caption) <> Biao_Ming Or Trim(labxin.Label8.caption) = "按列名查询" Then
For i = 1 To (rscol.RecordCount - 7)
conn.Execute "ALTER TABLE #temp DROP COLUMN col" & i + 7
Next i
End Ifrscol.Requery
If rscol.RecordCount < 8 Then
For m = 1 To (labxin.ListView1.ColumnHeaders.Count - 7)
For i = 1 To labxin.ListView1.ListItems.Count - 1
If Trim(labxin.ListView1.ListItems(i).SubItems(m + 6)) <> "" Then Exit For
Next i
If IsNumeric(labxin.ListView1.ListItems(i).SubItems(m + 6)) = True Then
conn.Execute "alter table #temp add col" & m + 7 & " decimal(9,3)"
Else
conn.Execute "alter table #temp add col" & m + 7 & " varchar(200)"
End If
'Wend
'Next i
Next m
End If conn.Execute "delete from #temp"
For i = 1 To labxin.ListView1.ListItems.Count
conn.Execute "insert into #temp(col1,col2)values('" & Trim(labxin.ListView1.ListItems(i).Text) & "','" & Trim(labxin.ListView1.ListItems(i).SubItems(1)) & "')"
For j = 3 To labxin.ListView1.ColumnHeaders.Count
'If IsNumeric(labxin.ListView1.ListItems(i).SubItems(j - 1)) = False Then
'conn.Execute "ALTER TABLE #temp ALTER COLUMN col" & j & " varchar(200)"
'End If
conn.Execute "update #temp set col" & j & "=" & KONGTONULL(labxin.ListView1.ListItems(i).SubItems(j - 1)) & " where col2='" & Trim(labxin.ListView1.ListItems(i).SubItems(1)) & "'"
Next j
Next i
rscol.Close
End Sub
解决方案 »
- 去掉小数点后00的简单方法
- 用户表的登录账户列 应该设成聚焦索引吗?
- 救救我吧
- 關於SQL保密 高手能做到嗎??
- 一个好的数据库论坛!
- 设计复制策略&索引优化?
- 另开新帖,高分求教分组统计的SQL实现,在线等待
- 用户密码的问题!?????在线给分!
- 数据库中如何能够将一个维度拆开变成n个列,就是所谓行列互换!
- 我的sqlserver启动不了,我搜索了以前的记录,找不到原因
- 现在有:学生表(学号,姓名,年龄,性别),课程表(课程号,课程名,学分),选课表(学号,课程号,成绩).假设已在课程表在增加一列STU_NUM,用于统计当前选修该门课的学生人数.创建INSERT触发器,当选修该课程的学生人数增加时,该课程所对应的STU_N
- 同学们,快来答题啊! 请教关于存储过程的几个问题
Dim OBJ1 As String
'For Each OBJ In TextBox
If Trim(OBJ) = "" Or IsNull(OBJ) = True Then
KONGTONULL = "null"
ElseIf IsNumeric(OBJ) = True Then
KONGTONULL = "'" & Val(OBJ) & "'"
Else
KONGTONULL = "'" & Trim(OBJ) & "'"
End If
'Next
End Function
INSERT INTO #Temp
SELECT
*
FROM
TableName