在批量插入数据时若主键相同则复盖该条记录,若主键不相同则添加该条记录.这样的sql语句如何写谢谢!
表如下:
字段1(主键) 字段2 字段3 字段4 字段5
00001 10 20 30 50
00002 20 10 20 30
..... .. .. .. ..数据:
00001 60 60 60 60
00010 20 80 80 20
..... .. .. .. ..完成插入数据后表中数据为:
字段1(主键) 字段2 字段3 字段4 字段5
00001 60 60 60 60
00002 20 10 20 30
00010 20 80 80 20
..... .. .. .. ..
表如下:
字段1(主键) 字段2 字段3 字段4 字段5
00001 10 20 30 50
00002 20 10 20 30
..... .. .. .. ..数据:
00001 60 60 60 60
00010 20 80 80 20
..... .. .. .. ..完成插入数据后表中数据为:
字段1(主键) 字段2 字段3 字段4 字段5
00001 60 60 60 60
00002 20 10 20 30
00010 20 80 80 20
..... .. .. .. ..
解决方案 »
- sql操作两个数据库之间数据的变换后的结果异常
- 输入日期的问题
- 删除掉表中一行数据记录,如何才能不使ID号产生中断?
- bcp导入数据到库中相关表时,怎样判断数据重复问题
- --求一触发器,自动维护树形数据的级数及路径!
- 在存储过程中,自动编号的初始值怎么设置呢
- 请问:如何根据一个编号取得其所有父类编号及其名称?
- 可以限制SQL SERVER日志大小吗
- 急,急,急 Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
- 欢迎各位有经验的高手指教:一个关于视图的概念问题。
- 在SQL Server 中,如何统计一个学员成绩表中所有奇数行的成绩
- 这种情况索引怎么建
00001 10 20 30 50
00002 20 10 20 30
..... .. .. .. .. 数据: ----------------tableb
00001 60 60 60 60
00010 20 80 80 20
..... .. .. .. ..
--插入新值
insert tablea
select *
from tableb b
where not exists(select 1 from tablea where a.字段1 = 字段1)
--更新
update a
set col = b.col ....
from tablea a
left join tableb b on a.字段1 = b.字段1
set col2 = tb2.col2,
col3 = tb2.col3,
col4 = tb2.col4,
col5 = tb2.col5
from tb1,tb2
where tb1.col1 = tb2.col1insert into tb1 select * from tb2 where col1 not in (col1 from tb1)create procedure my_proc
as
begin
update tb1
set col2 = tb2.col2,
col3 = tb2.col3,
col4 = tb2.col4,
col5 = tb2.col5
from tb1,tb2
where tb1.col1 = tb2.col1 insert into tb1 select * from tb2 where col1 not in (col1 from tb1)
end
goexec my_proc
MERGE 语句这个新增的 Transaction SQL 语句在一个基于源数据连接结果集的目标表上执行 INSERT、UPDATE 和 DELETE 操作。该语法允许您将一个数据源连接到目标表或视图上。然后在连接后的结果集上执行多种操作。MERGE 的语法为:[ WITH <common_table_expression> [,...n] ]
MERGE
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] target_table [ [ AS ] table_alias ]
[ WITH ( <merge_hint> ) ]
USING <table_source>
ON <search_condition>
[ WHEN MATCHED [ AND <search_condition> ]
THEN <merge_matched> ]
[ WHEN [TARGET] NOT MATCHED [ AND <search_condition> ]
THEN <merge_not_matched> ]
[ WHEN SOURCE NOT MATCHED [ AND <search_condition> ]
THEN <merge_ matched> ]
<output_clause>
[ OPTION ( <query_hint> [ ,...n ] ) ]
;
<merge_hint>::=
{ [ <table_hint_limited> [ ,...n ] ]
[ [ , ] INDEX ( index_val [ ,...n ] ) ] }<table_source> ::=
{
table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ]
[ WITH ( table_hint [ [ , ]...n ] ) ]
| rowset_function [ [ AS ] table_alias ]
[ ( bulk_column_alias [ ,...n ] ) ]
| user_defined_function [ [ AS ] table_alias ]
| OPENXML <openxml_clause>
| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
| <joined_table>
| <pivoted_table>
| <unpivoted_table>
}<merge_matched>::=
{ UPDATE SET <set_clause> | DELETE }<set_clause>::=
{ column_name = { expression | DEFAULT | NULL }
| { udt_column_name. { { property_name = expression
| field_name = expression }
| method_name ( argument [ ,...n ] ) } }
| column_name { .WRITE ( expression , @Offset , @Length ) }
| @variable = expression
} [ ,...n ] <merge_not_matched>::=
INSERT [ ( <column_list> ) ]
{ VALUES ( <values_list> )
| DEFAULT VALUES }<output_clause>::=
{
[ OUTPUT <dml_select_list> INTO { @table_variable | output_table }
[ (column_list) ] ]
[ OUTPUT <dml_select_list> ]
}
<dml_select_list>::=
{ <column_name> | scalar_expression } [ [AS] column_alias_identifier ]
[ ,...n ]<column_name> ::=
{ DELETED | INSERTED | from_table_name } . { * | column_name }
| $ACTION示例:在一条 SQL 语句中使用 WHERE 在一张表上执行 UPDATE 和 DELETE 操作USE AdventureWorks;
GO
MERGE Production.ProductInventory AS pi
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail sod
JOIN Sales.SalesOrderHeader soh
ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate = GETDATE()
GROUP BY ProductID) AS src (ProductID, OrderQty)
ON (pi.ProductID = src.ProductID)
WHEN MATCHED AND pi.Quantity - src.OrderQty <> 0
THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
WHEN MATCHED AND pi.Quantity - src.OrderQty = 0
THEN DELETE;这个示例是一个非常典型的销售定货库存问题。这个示例很简单,表达的意思就是:如果某一个产品产生了销售定单数据,则
将其对应的产品库存除去该销售定单所产生的数量,如果当前库存数量与该销售定单数量相同,则从库存表中删除该产品的库存纪录。我们看到,利用 MERGE 语句可以将复杂的 SQL 语句简化。它比起 IF、CASE 等更加灵活和强大。
字段1(主键) 字段2 字段3 字段4 字段5
00001 10 20 30 50
00002 20 10 20 30 tb2
数据:
00001 60 60 60 60
00010 20 80 80 20 update tb1
set col2 = tb2.col2,
col3 = tb2.col3,
col4 = tb2.col4,
col5 = tb2.col5
from tb1,tb2
where tb1.col1 = tb2.col1insert into tb1 select * from tb2 where col1 not in (col1 from tb1)
create procedure my_proc
as
begin
update tb1
set col2 = tb2.col2,
col3 = tb2.col3,
col4 = tb2.col4,
col5 = tb2.col5
from tb1,tb2
where tb1.col1 = tb2.col1 insert into tb1 select * from tb2 where col1 not in (col1 from tb1)
end
goexec my_proc