将数据库记录转化成insert割接语句的脚本

系统发布之前,需要将已经在开发环境建好的数据库以及数据一次性导出为SQL 脚本。

其中,表结构、存储过程、视图、函数等内容,SQL Server已经提供了导出向导。

除了这些之外,有些系统的初始化数据也避免不了的需要生成insert脚本,便于部署时一次性导入,本文中的SQL就是为了将数据库中的数据转化为insert脚本而写。

使用时,需要修改脚本中的两个参数:@tablename-表名称,需要导出数据的表;@selectcondition-数据过滤条件,值为空则表示导出所有数据,否则在此加上查询条件,如只需要导出id<100的数据,则该值设为“id<100”;

declare @tablename varchar(100)
set @tablename=’permissionitem’
declare @selectcondition nvarchar(1000)
set @selectcondition=”

if object_id(@tablename) is null
print ‘this table is not exists.’

declare @colname varchar(80)
declare @coldatetype int
declare @needquotation int

declare @sql_text nvarchar(4000)
declare @sql_colstext nvarchar(4000)
declare @sql_valuestext nvarchar(4000)

declare @datetypetable table (datetypename varchar(40),datetype int, needquotation int)

insert into @datetypetable (datetypename,datetype,needquotation) values (‘bigint’,127,0)
insert into @datetypetable (datetypename,datetype,needquotation) values (‘binary’,173,1)
insert into @datetypetable (datetypename,datetype,needquotation) values (‘bit’,104,0)
insert into @datetypetable (datetypename,datetype,needquotation) values (‘char’,175,1)
insert into @datetypetable (datetypename,datetype,needquotation) values (‘datetime’,61,1)
insert into @datetypetable (datetypename,datetype,needquotation) values (‘decimal’,106,0)
insert into @datetypetable (datetypename,datetype,needquotation) values (‘float’,62,0)
insert into @datetypetable (datetypename,datetype,needquotation) values (‘image’,34,1)
insert into @datetypetable (datetypename,datetype,needquotation) values (‘int’,56,0)
insert into @datetypetable (datetypename,datetype,needquotation) values (‘money’,60,0)
insert into @datetypetable (datetypename,datetype,needquotation) values (‘nchar’,239,1)
insert into @datetypetable (datetypename,datetype,needquotation) values (‘ntext’,99,1)
insert into @datetypetable (datetypename,datetype,needquotation) values (‘numeric’,108,0)
insert into @datetypetable (datetypename,datetype,needquotation) values (‘nvarchar’,231,1)
insert into @datetypetable (datetypename,datetype,needquotation) values (‘real’,59,0)
insert into @datetypetable (datetypename,datetype,needquotation) values (‘smalldatetime’,58,1)
insert into @datetypetable (datetypename,datetype,needquotation) values (‘smallint’,52,0)
insert into @datetypetable (datetypename,datetype,needquotation) values (‘smallmoney’,122,0)
insert into @datetypetable (datetypename,datetype,needquotation) values (‘sql_variant’,98,1)
insert into @datetypetable (datetypename,datetype,needquotation) values (‘sysname’,256,1)
insert into @datetypetable (datetypename,datetype,needquotation) values (‘text’,35,1)
insert into @datetypetable (datetypename,datetype,needquotation) values (‘timestamp’,189,1)
insert into @datetypetable (datetypename,datetype,needquotation) values (‘tinyint’,48,0)
insert into @datetypetable (datetypename,datetype,needquotation) values (‘uniqueidentifier’,36,1)
insert into @datetypetable (datetypename,datetype,needquotation) values (‘varbinary’,165,1)
insert into @datetypetable (datetypename,datetype,needquotation) values (‘varchar’,167,1)
insert into @datetypetable (datetypename,datetype,needquotation) values (‘xml’,241,1)

declare cur_cols cursor for
select a.[name],a.user_type_id,b.needquotation from sys.all_columns a inner join @datetypetable b on a.user_type_id=b.datetype where a.[object_id]=object_id(@tablename) order by a.column_id

set @sql_colstext = ”
set @sql_valuestext = ”
open cur_cols
fetch next from cur_cols into @colname,@coldatetype,@needquotation
while @@fetch_status=0
begin
if @sql_colstext=”
set @sql_colstext = @sql_colstext + @colname
else
set @sql_colstext = @sql_colstext + ‘,’ + @colname
if @needquotation=1
if @sql_valuestext = ”
set @sql_valuestext = @sql_valuestext + ”””’ + isnull(‘ + @colname + ‘,””) + ”””’
else
set @sql_valuestext = @sql_valuestext + ‘,””” + isnull(‘ + @colname + ‘,””) + ”””’
else
if @sql_valuestext = ”
set @sql_valuestext = @sql_valuestext + ”’ + isnull(convert(varchar(50),’ +  @colname + ‘),”null”) +”’
else
set @sql_valuestext = @sql_valuestext + ‘,” + isnull(convert(varchar(50),’ +  @colname + ‘),”null”) +”’
fetch next from cur_cols into @colname,@coldatetype, @needquotation
end
CLOSE cur_cols
DEALLOCATE cur_cols

set @sql_text=’select ”insert into ‘ + @tablename + ‘(‘ + @sql_colstext + ‘) values (‘ + @sql_valuestext + ‘)” from ‘ + @tablename + case when @selectcondition=” then ” else ‘ where ‘ + @selectcondition end
–print @sql_text
exec (@sql_text)

Advertisements

About 张俊波

Consultant / Project Manager
此条目发表在Sql Server分类目录,贴了标签。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s