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

[hokor]

最近在发现有人想要一种通用存储过程，实现将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
*/

[hokor]

--自增列是主键的情况测试
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 ,COL1 VARCHAR(10),COL2 DATETIME CONSTRAINT PK_SRC PRIMARY KEY (ID,COL1)) 
GO
INSERT INTO [SRC](ID,COL1,COL2)
SELECT 4,'DDD',GETDATE()+1 UNION ALL
SELECT 5,'EEE',GETDATE()+1 UNION ALL
SELECT 6,'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 (ID,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() 
GO
SELECT * FROM [DES]
/*
ID	COL1	COL2
1	AAA	2010-07-29 14:19:39.137
2	BBB	2010-07-29 14:19:39.137
3	CCC	2010-07-29 14:19:39.137
4	DDD	2010-07-29 14:19:39.137
5	EEE	2010-07-29 14:19:39.137
*/
SELECT * FROM [SRC]
/*
ID	COL1	COL2
4	DDD	2010-07-30 14:19:39.037
5	EEE	2010-07-30 14:19:39.037
6	FFF	2010-07-29 14:19:39.037
*/
EXEC [P_Incr_DataInsert] '[SRC]','[DES]'
SELECT * FROM [DES]
/* --更新了DDD,EEE 两行，新增了FFF行
ID	COL1	COL2
1	AAA	2010-07-29 14:19:39.137
2	BBB	2010-07-29 14:19:39.137
3	CCC	2010-07-29 14:19:39.137
4	DDD	2010-07-30 14:19:39.037
5	EEE	2010-07-30 14:19:39.037
6	FFF	2010-07-29 14:19:39.037
*/

[Andy__Huang]

最近在发现有人想要一种通用存储过程，实现将A表数据插入到B表中。
其中A表与B表结构完全一致。如果插入的记录在B表已经存在则更新。

================================
其实以前我也这样写，但发觉写起来比较麻烦，
要是你的表都有主键，为了达到相同的效果，在插入数据之前，先把这个主键删除再插入数据，这样写起来简单而且到相同效果。如果先判断数据是否存在？然后再更新，显然更麻烦一些

[pt1314917]

。。。
多个参数呢？

[hokor]

[Quote=引用 3 楼 pt1314917 的回复:]
。。。
多个参数呢？
[/Quote]
哪些参数？

[hokor]

[Quote=引用 2 楼 hdhai9451 的回复:]
最近在发现有人想要一种通用存储过程，实现将A表数据插入到B表中。
其中A表与B表结构完全一致。如果插入的记录在B表已经存在则更新。

================================
其实以前我也这样写，但发觉写起来比较麻烦，
要是你的表都有主键，为了达到相同的效果，在插入数据之前，先把这个主键删除再插入数据，这样写起来简单而且到相同效果。如果先判断数据是否存在？然后再更……
[/Quote]
删除主键，如果插入的数据在目标表已经存在的话，就会导致重复记录，再添加主键就会失败。
所以我认为还是删数据的好。