Ｍｅｒｇｅ SQL 2008
merge [target] t
using [source] s on t.id = s.id
when matched then update t.name = s.name, t.age = s.age -- use "rowset1"
when not matched then insert values(id,name,age) -- use "rowset2"
when source not matched then delete; -- use "rowset3"
MERGE dbo.table AS im --对比表
USING (SELECT * from dbo.table_MID with(nolock) ) AS src --源
ON im.ProductID = src. ProductID
WHEN MATCHED THEN
UPDATE SET im.ProductID = src. ProductID
,im.BrandName=src.BrandName
,im.ProductName = src.ProductName
,im.ProductType=src.ProductType
,indate=getdate()
WHEN NOT MATCHED THEN
INSERT (ProductID,BrandName,Series,ModelNumber,ProductName,ProductType,Category,indate)
VALUES (src.ProductID,src.BrandName,src.Series,src.ModelNumber,src.ProductName,src.ProductType,src.Category,src.indate)
WHEN NOT MATCHED BY SOURCE then delete;
相关文档：
PL/SQL实例分析
第五章
1、PL/SQL实例分析
1）在【SQLPlus Worksheet】中直接执行如下SQL代码完成上述操作。(创建表)
―――――――――――――――――――――――――――――――
CREATE TABLE "SCOTT"."TESTTABLE" ("RECORDNUMBER" NUMBER(4) NOT NULL, "CURRENTDATE" DATE NOT NULL)
TABLESPACE "SYSTEM ......
drop table father;
create table father(
id int identity(1,1) primary key,
name varchar(20) not null,
age int not null
)
drop table mother;
create table mother(
id int identity(1,1),
name varchar(20) not null,
age int not null,
husban ......
判断数据库类型
(select count(*) fromsysobjects)>0 //sql数据库
(select count(*) from msysobjects)>0 //access数据库
得到Sql用户名
user>0
Conversion failed when converting the nvarchar value 'dbo' to data type int.
重构SQL语句
整数型
(A) ID=49 ID=49 And [ 查询条件] ，即� ......
注释:只适合单表单列数据,
create database test
go
use test
go
create table users
(
:id int identity(1,1) primary key not null,
:name nvarchar(20)
)
go
create proc sp_Inserts
@Names nvarchar(4000)
as
declare @Name nvarchar(20),@ErrorSum int
:set @ErrorSum = 0
:begin tra ......
