数组到SQLSERVER

[northwolves]

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的最快方法.要求：服务器已有的前两列数据更新第三列，否则添加

[northwolves]

[Quote=引用 7 楼 dawugui 的回复:]

需要分两步走.

update t1
set t1.value = t2.value
from t1 , (...) t2 where t1.date = t2.date and t1.type = t2.type

insert into t1
select .... from t2 where not exists(select 1 from t2 where t1.date ……
[/Quote]
谢谢回复。这里的t2怎么组建最好？

[liang145]

VB码..

    Private Sub SimpleButton19_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SimpleButton19.Click
        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) 

小弟只是试试，不算什么最好方法，哈哈

[水向东流时光怎么偷]

太高深了,不懂,帮顶,蹭分

[silwol]

DECLARE @xml XML

SET @xml='
<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

[Mr_Nice]

[Quote=引用楼主 northwolves 的回复:]
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
……
[/Quote]

LZ可以参考Bcp /Bulk Insert

[叶子]

几十万行三列的二维数组,实在是想不到什么高效的办法
循环后逐行判断是最普通的思路，但是效率很差。

[昵称被占用了]

形成XML，怎么形成快程序考虑

XML给数据库后先形成临时表，判断存在修改，不存在插入

[--小F--]

用BCP格式文件不知道怎么样

[快溜]

[Quote=引用 4 楼 haiwer 的回复:]
形成XML，怎么形成快程序考虑

XML给数据库后先形成临时表，判断存在修改，不存在插入
[/Quote]SQL Server 2005 XML 操作总结（五）元素（节点）操作——修改、删除、移动、遍历操作

[dawugui]

需要分两步走.

update t1
set t1.value = t2.value
from t1 , (...) t2 where t1.date = t2.date and t1.type = t2.type

insert into t1
select .... from t2 where not exists(select 1 from t2 where t1.date = t2.date and t1.type = t2.type)

[So_CooL]

用C#中的SqldataAdapter.update()方法最快