一个通用数据插入存储过程,欢迎指导

hokor hokor 墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年,写一次发全网 我也要 + 加入社区
最近在发现有人想要一种通用存储过程,实现将A表数据插入到B表中。
其中A表与B表结构完全一致。如果插入的记录在B表已经存在则更新。
存储过程两个参数,A表名和B表名。
存储过程以前没怎么写过,看个帮助文档写了一个,自己测试时满足需求了。
但是没有错误处理机制,希望有高手帮忙完善一下。
下面是代码,适应多种种情况,无自增列,有自增列且自增列是主键,有自增列且自增列非主键。

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[P_Incr_DataInsert]') AND type in (N'P', N'PC'))
DROP PROCEDURE [P_Incr_DataInsert]
GO
CREATE PROC [P_Incr_DataInsert]
(
@Src NVARCHAR(120), --源 [database_name].[schema_name].table_name
@Des NVARCHAR(120) --目标 [database_name].[schema_name].table_name
)
AS
BEGIN
DECLARE @ColList NVARCHAR(1000),@SQL NVARCHAR(MAX);
IF OBJECT_ID(@Src) IS NULL OR OBJECT_ID(@Des) IS NULL RETURN --如果源或目标不存在则返回

SELECT @SQL= isnull(@SQL+char(13)+'AND','ON')+ ' des.'+c.name+' = src.'+c.name FROM sys.columns c
inner join sys.index_columns ic on ic.column_id = c.column_id and c.object_id =ic.object_id
inner join sys.indexes i on i.object_id = ic.object_id
WHERE c.object_id = OBJECT_ID(@Des) and i.is_primary_key = 1
SET @SQL = 'DELETE des FROM '+@Des+' des JOIN '+@Src+' src '+ @SQL
EXEC (@SQL)
SET @SQL = NULL
--如果自增列是主键,则开启标识符插入,否则排除自增列插入
IF EXISTS( SELECT c.* FROM sys.columns c
inner join sys.index_columns ic on ic.column_id = c.column_id and c.object_id =ic.object_id
inner join sys.indexes i on i.object_id = ic.object_id
WHERE c.OBJECT_ID = OBJECT_ID(@Des)
AND i.is_primary_key = 1 AND c.is_identity = 1)
BEGIN
SELECT @ColList = ISNULL(@ColList+',','')+ QUOTENAME(name) FROM sys.columns
WHERE OBJECT_ID = OBJECT_ID(@Des)
SET @SQL = N'SET IDENTITY_INSERT '+@Des +' ON'+CHAR(13)
+ 'INSERT INTO '+@Des+ N'('+@ColList+N')'+CHAR(13)+ 'SELECT '
+ @ColList+' FROM '+@Src +CHAR(13)
+ 'SET IDENTITY_INSERT '+@Des +' OFF'
END
ELSE
BEGIN
SELECT @ColList = ISNULL(@ColList+',','')+ QUOTENAME(name) FROM sys.columns
WHERE OBJECT_ID = OBJECT_ID(@Des) and is_identity = 0 --排除自增列
SET @SQL = N'INSERT INTO '+@Des+ N'('+@ColList+N')'+CHAR(13)+ 'SELECT '
+ @ColList+' FROM '+@Src
END
EXEC (@SQL)
END
GO

测试
--自增列非主键情况测试
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[SRC]') AND type in (N'U'))
DROP TABLE [SRC]
GO
CREATE TABLE [SRC] (ID INT IDENTITY,COL1 VARCHAR(10),COL2 DATETIME CONSTRAINT PK_SRC PRIMARY KEY (COL1))
GO
INSERT INTO [SRC](COL1,COL2)
SELECT 'DDD',GETDATE()+1 UNION ALL
SELECT 'EEE',GETDATE()+1 UNION ALL
SELECT 'FFF',GETDATE()
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DES]') AND type in (N'U'))
DROP TABLE [DES]
GO
CREATE TABLE [DES] (ID INT IDENTITY,COL1 VARCHAR(10),COL2 DATETIME CONSTRAINT PK_DES PRIMARY KEY (COL1))
GO
INSERT INTO [DES](COL1,COL2)
SELECT 'AAA',GETDATE() UNION ALL
SELECT 'BBB',GETDATE() UNION ALL
SELECT 'CCC',GETDATE() UNION ALL
SELECT 'DDD',GETDATE() UNION ALL
SELECT 'EEE',GETDATE()

SELECT * FROM [DES]
/*
ID COL1 COL2
1 AAA 2010-07-29 14:14:16.587
2 BBB 2010-07-29 14:14:16.587
3 CCC 2010-07-29 14:14:16.587
4 DDD 2010-07-29 14:14:16.587
5 EEE 2010-07-29 14:14:16.587
*/
SELECT * FROM [SRC]
/*
1 DDD 2010-07-30 14:14:16.487
2 EEE 2010-07-30 14:14:16.487
3 FFF 2010-07-29 14:14:16.487
*/
EXEC [P_Incr_DataInsert] '[SRC]','[DES]'
SELECT * FROM [DES]
/* 更新了DDD,EEE 两行,新增了FFF行
1 AAA 2010-07-29 14:14:16.587
2 BBB 2010-07-29 14:14:16.587
3 CCC 2010-07-29 14:14:16.587
6 DDD 2010-07-30 14:14:16.487
7 EEE 2010-07-30 14:14:16.487
8 FFF 2010-07-29 14:14:16.487
*/

...全文

相关推荐

评论 5
收藏 已收藏
分享
返回顶部
hokor
应用实例 27,579 成员 68,540 内容