Sqlserver备份数据表

快速备份表 saleDetail

使用select into 可以快速创建表并将表数据同时插入新建表中

1 2	select * into [saleDetail_20181012] from [saleDetail]

备份表数据还原，使用insert into 插入数据

1 2	insert into [saleDetail] select * from [saleDetail_20181012]

sqlserver 数据插入，若存在则更新，若没有则进行插入


MERGE INTO merge_target target 
USING (SELECT B.name,B.age,B.target_id FROM merge_source B) source
ON (target.id=source.target_id) 
WHEN MATCHED THEN 
    UPDATE 
    SET target.name = source.name, 
        target.age = source.age 
WHEN NOT MATCHED THEN 
    INSERT(target.name,target.age) VALUES (source.name,source.age);
 
``` 
注意，无论是merge_target中的id 还是 merge_source 中的id,单独查询，都不允许有重复的数据，若有重复数据，则会报错无法执行


##### sqlserver 数据插入，若存在则更新，若没有则进行插入，若目标表中有，来源表中没有则删除的sql写法

merge into 目标表 a
using 源表 b
on a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 …
when matched update set a.字段1=b.字段1,
a.字段2=b.字段2
when not matched insert values (b.字段1,b.字段2)
when not matched by source
then delete

例子：

create table targetTable(ID INT primary key identity(1,1),[name] varchar(50),age int)
create table sourceTable(ID INT primary key identity(1,1),[name] varchar(50),age int)
insert into targetTable([name],age) values(‘大卫’,40)

merge into targetTable as t
using sourceTable as S on t.ID=s.ID
when matched –更新目标表中有ID,则更新
then update set t.[name]=S.[name]
when not matched –添加目标表中没有ID,在原表中有，则插入相关数据
then insert values (s.[name],s.age)
when not matched by source –目标表存在，源表不存在，则删除
then delete;

```