系统发布之前,需要将已经在开发环境建好的数据库以及数据一次性导出为SQL 脚本。
其中,表结构、存储过程、视图、函数等内容,SQL Server已经提供了导出向导。
除了这些之外,有些系统的初始化数据也避免不了的需要生成insert脚本,便于部署时一次性导入,本文中的SQL就是为了将数据库中的数据转化为insert脚本而写。
使用时,需要修改脚本中的三个参数:@tablename-表名称,需要导出数据的表;@selectcondition-数据过滤条件,值为空则表示导出所有数据,否则在此加上查询条件,如只需要导出id<100的数据,则该值设为“id<100”;@isneedidentity-是否需要将自增字段一起导出,1表示需要,0表示不需要。
–再上一版本的基础上可以设置是否输出identity字段
–修正了将null字段的值输出为”的bug
declare @tablename varchar(100)
declare @selectcondition nvarchar(1000)
declare @isneedidentity int
–设置需要获取数据的表名
set @tablename=’permissionitem’
–设置条件
set @selectcondition=”
–设置是否需要生成自增字段
set @isneedidentity=1
if object_id(@tablename) is null
print ‘this table is not exists. please change the datebase and try again.’
else
begin
declare @colname varchar(80)
declare @coldatetype int
declare @needquotation int
declare @isidentity 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,a.is_identity 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,@isidentity
while @@fetch_status=0
begin
if @isidentity = 0 or @isneedidentity = 1
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 + ”’ + case when ‘ + @colname + ‘ is null then ”null” else ”””” + ‘ + @colname + ‘ + ”””” end + ”’
else
set @sql_valuestext = @sql_valuestext + ‘,” + case when ‘ + @colname + ‘ is null then ”null” else ”””” + ‘ + @colname + ‘ + ”””” end + ”’
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”) +”’
end
fetch next from cur_cols into @colname,@coldatetype, @needquotation,@isidentity
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)
end