SQL批量插入/更新 [英] Sql Bulk Insert/Update

问题描述

我有一个数据表.

我想以这种方式执行批量更新:如果表中存在记录，则应该对其进行更新，否则应插入新行.

I have a datatable.

I want to perform a Bulk update in such a way that if a record is present in a table then it should be updated, else a new row should be inserted.

Is there any best way to do this in one shot?

推荐答案

我认为您应该在临时表或临时表中批量插入数据，然后编写SQL语句来检查现有记录，然后再插入主表.

如果使用的是SQL 2008，则也可以使用新的MERGE语句.
http://technet.microsoft.com/en-us/library/bb510625.aspx [ ^ ]

I think you should bulk insert data in the staging or temp table and then write SQL statements to check for existing records before inserting in the main table.

If you are using SQL 2008, then you can use the new MERGE statement as well.
http://technet.microsoft.com/en-us/library/bb510625.aspx[^]

那样尝试

Try like that

CREATE PROC AlterSomeObj
 @act VARCHAR(10) = ''C'',    
 @objXML xml     
AS    
    
IF (@act = ''C'')    -- for creating
BEGIN    
    
 INSERT Table1(val1, val2)    
 SELECT     
    
  ITEM.TAB.value(''val1[1]'',''VARCHAR(1000)'') AS val1 ,    
  ITEM.TAB.value(''val2[1]'',''VARCHAR(MAX)'') AS val2 ,    
  FROM @objXML.nodes(''ParentNode/ChildNode'') ITEM(TAB)    
    
END    
ELSE IF(@act = ''U'')     -- for updating
BEGIN    
 U Table1    
 SET     
 val1 = TAB.val1 ,     
 val2 = TAB.val2,    
    
 FROM     
  Table1 ITEMITEMS    
  INNER JOIN    
  (    
   SELECT    
    ITEM.TAB.value(''val1[1]'',''VARCHAR(1000)'') AS val1 ,    
    ITEM.TAB.value(''val2[1]'',''VARCHAR(MAX)'') AS val2 
    
   FROM @objXML.nodes(''ParentNode/ChildNode'') ITEM(TAB)    
  )TAB    
    
 ON ITEMITEMS.ID = @urlID    
   
    
END    
ELSE IF(@act = ''D'')    -- for deleting
BEGIN    
--delete logic
END    

我认为它将解决您的问题

As I think it will solve your problem

这篇关于SQL批量插入/更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！