sp_GetInsertScript插入带数据sql

/** Object: StoredProcedure [dbo].[sp_GetInsertScript] Script Date: 08/21/2020 14:57:38 **/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/
exec sp_GetInsertScript ‘customer’,’ and 1=1’,’custno,custname’,1,1
if(object_id(‘sp_GetInsertScript’)>0) drop proc sp_GetInsertScript
/
ALTER proc [dbo].[sp_GetInsertScript] (
@tblname NVARCHAR(max), —表名
@condition VARCHAR(max), —条件
@fixcolumns VARCHAR(max)=’’,—指定列名
@iscreatewhere INT = 1, —是否创建插入语句时增加where
@IDENTITY_INSERT INT = 0 —默认不启用标识插入
)
as
begin
set nocount ON
—PRINT @condition
declare @sqlAll varchar(max)
declare @sqlstr varchar(max)
declare @sqlColumnValueForInsert varchar(max)
declare @sqlColumnNameForInsert varchar(max)
DECLARE @primarykey VARCHAR(500) —主键
DECLARE @rid VARCHAR(500) —自增列
declare @Sql nvarchar(max)
DECLARE @columns VARCHAR(max) —非自增的所有列逗号分隔
DECLARE @insertWhere VARCHAR(max)
DECLARE @usedb VARCHAR(500)
IF(ISNULL(@fixcolumns,’’)!=’’) SET @fixcolumns = ‘,’+@fixcolumns+’,’
—连接数据库
set @usedb =’’— CASE isnull(@dbname,’’) WHEN ‘’ THEN ‘’ ELSE ‘ use ‘+@dbname+ ‘ ‘ END
—获得主键————————-
SET @primarykey = ‘’
SET @sql =@usedb + N’ SELECT @primarykey = @primarykey+’’,’’ + name FROM syscolumns col WHERE OBJECT_NAME(id)=’’’+@tblname+’’’ AND
EXISTS ( SELECT 1
FROM dbo.sysindexes si
INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
AND si.indid = sik.indid
INNER JOIN dbo.syscolumns sc ON sc.id = sik.id
AND sc.colid = sik.colid
INNER JOIN dbo.sysobjects so ON so.name = si.name
AND so.xtype = ‘’PK’’
WHERE sc.id = col.id
AND sc.colid = col.colid )
ORDER BY col.colid’
EXEC sp_executesql @sql,N’@primarykey varchar(500) out’,@primarykey out
SET @primarykey = SUBSTRING(@primarykey,2,LEN(@primarykey ))
—print @primarykey
IF(CHARINDEX(‘,’,@primarykey)>0) set @primarykey = REPLACE(@primarykey,’,’,’+’)
—获得自增列————————-
—SELECT @rid = name FROM syscolumns WHERE OBJECT_NAME(id)=@tblname AND COLUMNPROPERTY(id, name, ‘IsIdentity’) = 1
SET @sql =@usedb + N’ select @rid = name FROM syscolumns WHERE OBJECT_NAME(id)=’’’+@tblname+’’’ AND COLUMNPROPERTY(id, name, ‘’IsIdentity’’) = 1 ‘;
EXEC sp_executesql @sql,N’@rid varchar(500) out’,@rid out
—PRINT @rid
IF(@primarykey = ‘’) SET @primarykey = @rid
IF(isnull(@primarykey,’’)=’’)
BEGIN
PRINT ‘主键和自增列不能为空！’
RETURN
END
—获得所有列
SET @columns = ‘’
—SELECT @columns = @columns+’,’+ + name FROM syscolumns WHERE OBJECT_NAME(id)=@tblname AND COLUMNPROPERTY(id, name, ‘IsIdentity’) = 0 ORDER BY colid
—SET @sql =@usedb + N’ SELECT @columns = @columns+’’,’’+ name FROM syscolumns WHERE OBJECT_NAME(id)=’’’+@tblname+’’’ AND COLUMNPROPERTY(id, name, ‘’IsIdentity’’) = 0 ‘
SET @sql =@usedb + N’ SELECT @columns = @columns+’’,’’+ name FROM syscolumns WHERE OBJECT_NAME(id)=’’’+@tblname+’’’ ‘
IF(isnull(@fixcolumns,’’) != ‘’)
BEGIN
SET @sql = @sql + ‘ and ‘’’+@fixcolumns+’’’ like ‘’%,’’+name+’’,%’’ ‘
END
IF(@IDENTITY_INSERT = 0)
BEGIN
SET @sql = @sql + ‘ and ‘’,’+@rid+’,’’ not like ‘’%,’’+name+’’,%’’ ‘
END
SET @sql = @sql + ‘ order by colid ‘

—PRINT @sql
EXEC sp_executesql @sql,N’@columns varchar(max) out’,@columns out
SET @columns = SUBSTRING(@columns,2,LEN(@columns ))
—PRINT @columns

select @sqlstr=’select ‘’insert ‘+@tblname
select @sqlColumnValueForInsert=’’
select @sqlColumnNameForInsert=’(‘
—select @sqlColumnValueForInsert=’values (‘’+’
select @sqlColumnValueForInsert=’select ‘’+’
SELECT @insertWhere = ‘ +’’ where not exists(select 1 from ‘+@tblname+’ where ‘+@primarykey+’ = ‘’’’’’+convert(varchar,’+@primarykey+’)+’’’’’’)’’ ‘
select @sqlColumnValueForInsert=@sqlColumnValueForInsert+col+’+’’,’’+’ ,@sqlColumnNameForInsert=@sqlColumnNameForInsert+name +’,’
from (select case
when a.xtype =173 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’convert(varchar,’+a.name +’)’+’ end’
when a.xtype =104 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’convert(varchar,’+a.name +’)’+’ end’
when a.xtype =175 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’’’’’’’’’+’+’replace(‘+a.name+’,’’’’’’’’,’’’’’’’’’’’’)’ + ‘+’’’’’’’’’+’ end’
when a.xtype =61 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’’’’’’’’’+’+’convert(varchar,’+a.name +’,121)’+ ‘+’’’’’’’’’+’ end’
when a.xtype =106 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’convert(varchar,’+a.name +’)’+’ end’
when a.xtype =62 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’convert(varchar,’+a.name +’,2)’+’ end’
when a.xtype =56 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’convert(varchar,’+a.name +’)’+’ end’
when a.xtype =60 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’convert(varchar,’+a.name +’)’+’ end’
when a.xtype =239 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’’’’’’’’’+’+’replace(‘+a.name+’,’’’’’’’’,’’’’’’’’’’’’)’ + ‘+’’’’’’’’’+’ end’
when a.xtype =108 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’convert(varchar,’+a.name +’)’+’ end’
when a.xtype =231 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’’’’’’’’’+’+’replace(‘+a.name+’,’’’’’’’’,’’’’’’’’’’’’)’ + ‘+’’’’’’’’’+’ end’
when a.xtype =59 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’convert(varchar,’+a.name +’,2)’+’ end’
when a.xtype =58 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’’’’’’’’’+’+’convert(varchar,’+a.name +’,121)’+ ‘+’’’’’’’’’+’ end’
when a.xtype =52 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’convert(varchar,’+a.name +’)’+’ end’
when a.xtype =122 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’convert(varchar,’+a.name +’)’+’ end’
when a.xtype =127 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’convert(varchar,’+a.name +’)’+’ end’
when a.xtype =48 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’convert(varchar,’+a.name +’)’+’ end’
when a.xtype =165 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’convert(varchar,’+a.name +’)’+’ end’
when a.xtype =167 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’’’’’’’’’+’+’replace(‘+a.name+’,’’’’’’’’,’’’’’’’’’’’’)’ + ‘+’’’’’’’’’+’ end’
else ‘’’NULL’’’
end as col,a.colid,a.name
from syscolumns a where a.id = object_id(@tblname)
and a.xtype <>189 and a.xtype <>34 and a.xtype <>35 and a.xtype <>36
)t
WHERE 1=1 AND ‘,’+ @columns + ‘,’ LIKE ‘%,’+t.name+’,%’
order by colid

—PRINT @sqlColumnValueForInsert
—select @sqlstr=@sqlstr+left(@sqlColumnNameForInsert,len(@sqlColumnNameForInsert)-1)+’) ‘+left(@sqlColumnValueForInsert,len(@sqlColumnValueForInsert)-3)+’)’’ from ‘+@tblname + ‘ where 1=1 and ‘ + isnull(@condition,’’)
SET @sqlAll = ‘’;
set @sqlAll = @sqlAll + @usedb
IF(@IDENTITY_INSERT = 1) SET @sqlAll = @sqlAll + ‘ select ‘’aa—- SET IDENTITY_INSERT ‘+@tblname+’ ON ‘’ union ‘
set @sqlAll = @sqlAll + @sqlstr
set @sqlAll = @sqlAll + left(@sqlColumnNameForInsert,len(@sqlColumnNameForInsert)-1)+’) ‘
set @sqlAll = @sqlAll + left(@sqlColumnValueForInsert,len(@sqlColumnValueForInsert)-5)
IF(@iscreatewhere = 1) set @sqlAll = @sqlAll + @insertWhere
set @sqlAll = @sqlAll + ‘ from ‘+ @tblname + ‘ where 1=1 ‘
IF(isnull(@condition,’’) != ‘’) SET @sqlAll = @sqlAll + isnull(@condition,’’)
IF(@IDENTITY_INSERT = 1) SET @sqlAll = @sqlAll + ‘ union select ‘’zz—- SET IDENTITY_INSERT ‘+@tblname+’ OFF ‘’ ‘
/ set @sqlstr=@sqlstr+
left(@sqlColumnNameForInsert,len(@sqlColumnNameForInsert)-1)+’) ‘+
left(@sqlColumnValueForInsert,len(@sqlColumnValueForInsert)-5)+
case @iscreatewhere when 1 then @insertWhere ELSE ‘’ END +
‘ from ‘+ @tblname + ‘ where 1=1 and ‘ + isnull(@condition,’’) /
print @sqlAll
exec( @sqlAll)
PRINT ‘总记录数：’+convert(varchar,case when @IDENTITY_INSERT=1 then @@rowcount - 2 else @@rowcount end)
/ SELECT ‘SET IDENTITY_INSERT ‘+@tblname+’ ON’
SELECT ‘SET IDENTITY_INSERT ‘+@tblname+’ OFF’ /
set nocount off
END