Data Transfer Using Insert Script
我们在数据库开发过程中, 常需要把一个表中的数据变成
insert语句,便于完成
1,数据初始化
2,数据的导入/导出
下面的该存储过程(stored proc) usp_GenInsertScript, 我使用大约有4年了, 觉得很好用。
用法如下:
exec usp_GenInsertScript 'your_table_name'
例如:
create table T1(idx int identity(1,1), userName nvarchar(50))
create table T1(idx int identity(1,1), userName nvarchar(50))
insert into T1(userName) values('ZhangZhiRong')
insert into T1(userName) values('HeHui')
insert into T1(userName) values('PengHaiPing')
insert into T1(userName) values('HeHui')
insert into T1(userName) values('PengHaiPing')
exec usp_GenInsertScript 'T1'
执行该语句后会生成如下的脚本:
-- ====================================
SET IDENTITY_INSERT T1 ON
SET IDENTITY_INSERT T1 ON
insert into T1([idx],[userName]) values(1,'ZhangZhiRong')
insert into T1([idx],[userName]) values(2,'HeHui')
insert into T1([idx],[userName]) values(3,'PengHaiPing')
insert into T1([idx],[userName]) values(2,'HeHui')
insert into T1([idx],[userName]) values(3,'PengHaiPing')
SET IDENTITY_INSERT T1 OFF
-- ====================================
-- ====================================
下面是创建该存储过程的脚本:
/**************************************************************
File : dbo.usp_GenInsertScript.PRC
Name : usp_GenInsertScript
Author : Inez Boone ( inez.boone@xs4al.nl)
working on the Sybase version of & thanks to:
Reinoud van Leeuwen ( reinoud@xs4all.nl)
Date : 2000-12-06
Description : This stored procedure generates an SQL script to fill the
tables in the database with their current content
Referencing :
Referenced By :
Parameter Input : 1) @tablename_mask varchar(30) = NULL
Parameter Output:
Return Value :
Name : usp_GenInsertScript
Author : Inez Boone ( inez.boone@xs4al.nl)
working on the Sybase version of & thanks to:
Reinoud van Leeuwen ( reinoud@xs4all.nl)
Date : 2000-12-06
Description : This stored procedure generates an SQL script to fill the
tables in the database with their current content
Referencing :
Referenced By :
Parameter Input : 1) @tablename_mask varchar(30) = NULL
Parameter Output:
Return Value :
----------------------------------------------------------------
History
History
Author : Peter Lin
Date :
Description : only name change
Date :
Description : only name change
Author : Peter Lin
Date : 2005-11-30
Description : change fieldname to [fieldname]
handle the situation where a column name is a keyword
----------------------------------------------------------------
Example
Example
exec usp_GenInsertScript 'sec_allmodule'
****************************************************************/
create procedure usp_GenInsertScript
@tablename_mask varchar(30) = NULL
as
begin
-- NOTE: If, when executing in the Query Analyzer, the result is truncated, you can remedy
-- this by choosing Query / Current Connection Options, choosing the Advanced tab and
-- adjusting the value of 'Maximum characters per column'.
-- Unchecking 'Print headers' will get rid of the line of dashes.
****************************************************************/
create procedure usp_GenInsertScript
@tablename_mask varchar(30) = NULL
as
begin
-- NOTE: If, when executing in the Query Analyzer, the result is truncated, you can remedy
-- this by choosing Query / Current Connection Options, choosing the Advanced tab and
-- adjusting the value of 'Maximum characters per column'.
-- Unchecking 'Print headers' will get rid of the line of dashes.
declare @tablename varchar (128)
declare @tablename_max varchar (128)
declare @tableid int
declare @columncount numeric (7,0)
declare @columncount_max numeric (7,0)
declare @columnname varchar (30)
declare @columntype int
declare @string varchar (30)
declare @leftpart varchar (8000) /* 8000 is the longest string SQLSrv7 can EXECUTE */
declare @rightpart varchar (8000) /* without having to resort to concatenation */
declare @hasident int
declare @tablename_max varchar (128)
declare @tableid int
declare @columncount numeric (7,0)
declare @columncount_max numeric (7,0)
declare @columnname varchar (30)
declare @columntype int
declare @string varchar (30)
declare @leftpart varchar (8000) /* 8000 is the longest string SQLSrv7 can EXECUTE */
declare @rightpart varchar (8000) /* without having to resort to concatenation */
declare @hasident int
set nocount on
-- take ALL tables when no mask is given (!)
if (@tablename_mask is NULL)
begin
select @tablename_mask = '%'
end
if (@tablename_mask is NULL)
begin
select @tablename_mask = '%'
end
-- create table columninfo now, because it will be used several times
create table #columninfo
(num numeric (7,0) identity,
name varchar(30),
usertype smallint)
(num numeric (7,0) identity,
name varchar(30),
usertype smallint)
select name,
id
into #tablenames
from sysobjects
where type in ('U' ,'S')
and name like @tablename_mask
-- loop through the table #tablenames
select @tablename_max = MAX (name),
@tablename = MIN (name)
from #tablenames
@tablename = MIN (name)
from #tablenames
while @tablename <= @tablename_max
begin
select @tableid = id
from #tablenames
where name = @tablename
begin
select @tableid = id
from #tablenames
where name = @tablename
if (@@rowcount <> 0)
begin
-- Find out whether the table contains an identity column
select @hasident = max( status & 0x80 )
from syscolumns
where id = @tableid
begin
-- Find out whether the table contains an identity column
select @hasident = max( status & 0x80 )
from syscolumns
where id = @tableid
truncate table #columninfo
insert into #columninfo (name,usertype)
select name, type
from syscolumns C
where id = @tableid
and type <> 37 -- do not include timestamps
select name, type
from syscolumns C
where id = @tableid
and type <> 37 -- do not include timestamps
-- Fill @leftpart with the first part of the desired insert-statement, with the fieldnames
select @leftpart = 'select ''insert into
'+@tablename
select @leftpart = @leftpart + '('
select @leftpart = @leftpart + '('
select @columncount = MIN (num),
@columncount_max = MAX (num)
from #columninfo
while @columncount <= @columncount_max
begin
select @columnname = name,
@columntype = usertype
from #columninfo
where num = @columncount
if (@@rowcount <> 0)
begin
if (@columncount < @columncount_max)
begin
select @leftpart = @leftpart + '[' + @columnname + '],'
end
else
begin
select @leftpart = @leftpart + '[' + @columnname + '])'
end
end
@columncount_max = MAX (num)
from #columninfo
while @columncount <= @columncount_max
begin
select @columnname = name,
@columntype = usertype
from #columninfo
where num = @columncount
if (@@rowcount <> 0)
begin
if (@columncount < @columncount_max)
begin
select @leftpart = @leftpart + '[' + @columnname + '],'
end
else
begin
select @leftpart = @leftpart + '[' + @columnname + '])'
end
end
select @columncount = @columncount + 1
end
end
select @leftpart = @leftpart + ' values('''
-- Now fill @rightpart with the statement to retrieve the values of the fields, correctly formatted
select @columncount = MIN (num),
@columncount_max = MAX (num)
from #columninfo
@columncount_max = MAX (num)
from #columninfo
select @rightpart = ''
while @columncount <= @columncount_max
begin
select @columnname = name,
@columntype = usertype
from #columninfo
where num = @columncount
begin
select @columnname = name,
@columntype = usertype
from #columninfo
where num = @columncount
if (@@rowcount <> 0)
begin
begin
if @columntype in (39,47) /* char fields need quotes (except when entering NULL);
* use char(39) == ', easier readable than escaping
*/
begin
select @rightpart = @rightpart + '+'
select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+replace([' + @columnname + '],' + replicate( char(39), 4 ) + ',' + replicate( char(39), 6) + ')+' + replicate( char(39), 4 ) + ',''NULL'')'
end
* use char(39) == ', easier readable than escaping
*/
begin
select @rightpart = @rightpart + '+'
select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+replace([' + @columnname + '],' + replicate( char(39), 4 ) + ',' + replicate( char(39), 6) + ')+' + replicate( char(39), 4 ) + ',''NULL'')'
end
else if @columntype = 35 /* TEXT fields cannot be RTRIM-ed and need quotes */
/* convert to VC 1000 to leave space for other fields */
begin
select @rightpart = @rightpart + '+'
select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+replace(convert(varchar(1000),[' + @columnname + '])' + ',' + replicate( char(39), 4 ) + ',' + replicate( char(39), 6 ) + ')+' + replicate( char(39), 4 ) + ',''NULL'')'
end
/* convert to VC 1000 to leave space for other fields */
begin
select @rightpart = @rightpart + '+'
select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+replace(convert(varchar(1000),[' + @columnname + '])' + ',' + replicate( char(39), 4 ) + ',' + replicate( char(39), 6 ) + ')+' + replicate( char(39), 4 ) + ',''NULL'')'
end
else if @columntype in (58,61,111) /* datetime fields */
begin
select @rightpart = @rightpart + '+'
select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+convert(varchar(20),[' + @columnname + '])+'+ replicate( char(39), 4 ) + ',''NULL'')'
end
begin
select @rightpart = @rightpart + '+'
select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+convert(varchar(20),[' + @columnname + '])+'+ replicate( char(39), 4 ) + ',''NULL'')'
end
else /* numeric types */
begin
select @rightpart = @rightpart + '+'
select @rightpart = @rightpart + 'ISNULL(convert(varchar(99),[' + @columnname + ']),''NULL'')'
end
begin
select @rightpart = @rightpart + '+'
select @rightpart = @rightpart + 'ISNULL(convert(varchar(99),[' + @columnname + ']),''NULL'')'
end
if ( @columncount < @columncount_max)
begin
select @rightpart = @rightpart + '+'','''
end
end
select @columncount = @columncount + 1
end
select @columncount = @columncount + 1
end
end
select @rightpart = @rightpart + '+'')''' + ' from ' + @tablename
-- Order the select-statements by the first column so you have the same order for
-- different database (easy for comparisons between databases with different creation orders)
select @rightpart = @rightpart + ' order by 1'
-- different database (easy for comparisons between databases with different creation orders)
select @rightpart = @rightpart + ' order by 1'
-- For tables which contain an identity column we turn identity_insert on
-- so we get exactly the same content
-- so we get exactly the same content
if @hasident > 0
select 'SET IDENTITY_INSERT ' + @tablename + ' ON'
select 'SET IDENTITY_INSERT ' + @tablename + ' ON'
--print @leftpart + @rightpart
exec ( @leftpart + @rightpart )
exec ( @leftpart + @rightpart )
if @hasident > 0
select 'SET IDENTITY_INSERT ' + @tablename + ' OFF'
select 'SET IDENTITY_INSERT ' + @tablename + ' OFF'
select @tablename = MIN (name)
from #tablenames
where name > @tablename
end
from #tablenames
where name > @tablename
end
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO