Date Type Value
d1 a 1
d2 a 2
d3 a 3
d4 b 4
d5 a 5
d6 c 6
d7 d 7
d8 a 8
d9 b 9
通过在内存中处理,得到一个几十万行三列的二维数组,结构如上所示,请教导入到SQLSERVER的最快方法.要求:服务器已有的前两列数据更新第三列,否则添加
d1 a 1
d2 a 2
d3 a 3
d4 b 4
d5 a 5
d6 c 6
d7 d 7
d8 a 8
d9 b 9
通过在内存中处理,得到一个几十万行三列的二维数组,结构如上所示,请教导入到SQLSERVER的最快方法.要求:服务器已有的前两列数据更新第三列,否则添加
LZ可以参考Bcp /Bulk Insert
set t1.value = t2.value
from t1 , (...) t2 where t1.date = t2.date and t1.type = t2.typeinsert into t1
select .... from t2 where not exists(select 1 from t2 where t1.date = t2.date and t1.type = t2.type)
Dim oDataBase As Database
Dim oDataReader As IDataReader = Nothing
Dim oDbCommand As DbCommand
Dim t As Integer = 0
Dim sqlstr As String = ""
Dim arr(5000, 2) As Integer '你的数组 Try
For i As Integer = 0 To 5000
arr(i, 0) = i * 1
arr(i, 1) = i * 2
arr(i, 2) = i * 3
Next
For i As Integer = 0 To 5000 sqlstr = sqlstr + " insert @Temp select '" + arr(i, 0).ToString() + "','" + arr(i, 1).ToString() + "','U" + arr(i, 2).ToString() + "'" '一批批的提交,一几提交100多条,这考虑varchar(max)的长度 If (sqlstr.Length > 7000 And sqlstr.Length < 8000) Or (i = 5000 And sqlstr.Length < 7000) Then
sqlstr = " declare @Temp table(Date nvarchar(10),[Type] nvarchar(10), [Value] nvarchar(10)) " + sqlstr oDataBase = DatabaseFactory.CreateDatabase() oDbCommand = oDataBase.GetStoredProcCommand("ProcKK") oDataBase.AddInParameter(oDbCommand, "@Sql", DbType.String, sqlstr) oDataBase.ExecuteNonQuery(oDbCommand)
sqlstr = ""
End If
Next
Catch ex As Exception
Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "DALPolicy")
If (rethrow) Then
Throw
End If
Finally
If Not oDataReader Is Nothing Then
oDataReader.Close()
End If
End Try End Sub
SQL:create Table TryTable(Date nvarchar(10),[Type] nvarchar(10), [Value] nvarchar(10))
declare @i as int
set @i=0
while @i<=4000
begin
insert TryTable
select cast(@i*1 as nvarchar(10)),cast(@i*2 as nvarchar(10)),cast(@i*3 as nvarchar(10))
set @i=@i+1
end--存储过程
alter proc ProcKK
@Sql as varchar(max) --这里是传上边VB码的字符串变量sqlstr
as
set @sql=@sql+' update TryTable set TryTable.[Value]=t.[Value] from @Temp as t where '+
' TryTable.Date=t.Date and TryTable.[Type]=t.[Type] '+
' insert TryTable select * from @Temp as t where not exists'+
' (select 1 from TryTable as t1 where t1.[Date]=t.[Date] and t1.[Type]=t.[Type])'
exec (@sql)
小弟只是试试,不算什么最好方法,哈哈
<Data>
<Item>
<Date>a1</Date>
<Type>a</Type>
<Value>1</Value>
</Item>
<Item>
<Date>b2</Date>
<Type>b</Type>
<Value>2</Value>
</Item>
<Item>
<Date>c3</Date>
<Type>c</Type>
<Value>3</Value>
</Item>
<Item>
<Date>d4</Date>
<Type>d/Type>
<Value>4</Value>
</Item>
</Data>
'CREATE TABLE #Temp (
[Date] VARCHAR(10) NOT NULL,
[Type] VARCHAR(10) NOT NULL,
[Value] INT NULL,
PRIMARY KEY([Date],[Type])
)INSERT INTO #Temp
SELECT c.value('Date[1]', 'VARCHAR(10)') AS [Date],
c.value('Type[1]', 'VARCHAR(10)') AS [Type],
c.value('Value[1]', 'VARCHAR(10)') AS [Value]
FROM @xml.nodes('/Data/Item') T(c)UPDATE [YOURTARGETTABLE]
SET [Value] = #Temp.Value
FROM [YOURTARGETTABLE] INNER JOIN #Temp
ON [YOURTARGETTABLE].[Date] = #Temp.Date
AND [YOURTARGETTABLE].[Type] = #Temp.[Type]INSERT INTO [YOURTARGETTABLE] ([Date],[Type],Value)
SELECT [Date],[Type],Value
FROM #Temp
WHERE NOT EXISTS (SELECT 1 FROM [YOURTARGETTABLE] INNER JOIN #Temp
ON [YOURTARGETTABLE].[Date] = #Temp.Date
AND [YOURTARGETTABLE].[Type] = #Temp.[Type])
DROP TABLE #Temp