一个通用数据插入存储过程,欢迎指导
其中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
*/
相关推荐
通用数据库查询器源码
支持 SQL Server、MySQL、Oracle数据库
SQLServer用存储过程实现插入更新数据示例
实现 1)有相同的数据,直接返回(返回值:0); 2)有主键相同,但是数据不同的数据,进行更新处理(返回值:2); 3)没有数据,进行插入数据处理(返回值:1)。 【创建存储过程】 Create proc Insert_Update @Id varchar(20), @Name varchar(20), @Telephone varchar(20), @Address varchar(20), @Job varchar(20), @returnValue int output as declare @tmpName varchar(20), @tmpTelephone v
百万数据批量插入存储过程
mysql 数据库已存储过程插入一千万条测试数据!利用项目即将上限需要,可以完全系统的测试项目性能!里面又完整的文档和各项说明:打开文档就可以实际操作。
存储过程(循环插入数据)
使用for loop向表中批量插入数据,推荐使用plsql执行,Dbvisualizer语句执行会出错
MySQL创建存储过程批量插入10万条数据
MySQL创建存储过程批量插入10万条数据 存储过程 1、首先防止主键冲突,我们清空表。 TRUNCATE table A_student; 2、编写存储过程 delimiter ‘$’; CREATE PROCEDURE batchInsert(in args int) BEGIN declare i int default 1; start TRANSACTION; while i <= args DO insert into A_student(id,name) VALUES (i, concat(“陈瓜皮-”, i)); set i = i+1; end while; COMMIT;


应用实例
