给你个案例:select'delete from CardScoreSave' select 'insert into CardScoreSave (cardScoreSave_ID, CardScoreSave_CardID, CardScoreSave_Score, CardScoreSave_ConsumeScore, CardScoreSave_Time, CardScoreSave_OperatorNo) values ('+ case when cardScoreSave_ID is null then 'null' else ''''+convert(varchar(30), cardScoreSave_ID)+'''' end + ',' + case when CardScoreSave_CardID is null then 'null' else '''' + convert(varchar(10), CardScoreSave_CardID) + '''' end + ',' + case when CardScoreSave_Score is null then 'null' else '''' + convert(varchar(30), CardScoreSave_Score) + '''' end + ',' +convert(varchar(30), CardScoreSave_ConsumeScore)+',' + case when CardScoreSave_Time is null then 'null' else '''' + convert(varchar(30), CardScoreSave_Time) + '''' end + ',' + case when CardScoreSave_OperatorNo is null then 'null' else '''' + convert(varchar(10), CardScoreSave_OperatorNo) + '''' end + ')' from CardScoreSave where datediff(n, @datetimefrom, CardScoreSave_Time) >= 0 and datediff(n, CardScoreSave_Time, @datetimeto) >= 0
select'insert into CardCashSave (CardCashSave_ID, CardCashSave_CardID, CardCashSave_Cash, CardCashSave_Score, CardCashSave_PresentCash, CardCashSave_RedeemCash, CardCashSave_Time, CardCashSave_OperatorNo, CardCashSave_businesslog_id) values ('+ case when CardCashSave_ID is null then 'null' else '''' + convert(varchar(30), CardCashSave_ID) + '''' end + ',' + case when CardCashSave_CardID is null then 'null' else '''' + convert(varchar(10), CardCashSave_CardID) + '''' end + ',' + convert(varchar(30), CardCashSave_Cash) + ',' + convert(varchar(30), CardCashSave_Score) + ',' + convert(varchar(30), CardCashSave_PresentCash) + ',' + convert(varchar(30), CardCashSave_RedeemCash) + ',' + case when CardCashSave_Time is null then 'null' else '''' + convert(varchar(30), CardCashSave_Time) + '''' end + ',' + case when CardCashSave_OperatorNo is null then 'null' else '''' + convert(varchar(10), CardCashSave_OperatorNo) + '''' end + ',' + case when CardCashSave_businesslog_id is null then 'null' else '''' + convert(varchar(30), CardCashSave_businesslog_id) + '''' end + ')' from CardCashSave where datediff(n, @datetimefrom, CardCashSave_Time) >= 0 and datediff(n, CardCashSave_Time, @datetimeto) >= 0 order by CardCashSave_ID
create procedure [dbo].[sys_getdata] ( @tablename sysname ) as begin --begin declare declare @column varchar(1000) declare @columndata varchar(1000) declare @sql varchar(4000) declare @xtype tinyint declare @name sysname declare @objectid int declare @objectname sysname declare @ident int
set nocount on set @objectid=object_id(@tablename); set @objectname=rtrim(object_name(@objectId)); --end declare
/*判断对象是否存在*/ if @objectId is null begin print @tablename + '对象不存在' return end
/*判断对象是否在当前数据库中*/ if @objectname is null or charindex(@objectname,@tablename)=0 begin print @tablename + '对象不在当前数据库中' return end
/*判断对象是否是表*/ if objectproperty(@objectid,'istable') <> 1 begin print @tablename + '对象不是表' return end
select @ident=status&0x80 from syscolumns where id=@objectid and status&0x80=0x80 if @ident is not null print 'set identity_insert '+ @tablename + ' on' --定义游标,循环取数据并生成Insert语句 declare syscolumns_cursor cursor for select c.name,c.xtype from syscolumns c where c.id=@objectid order by c.colid --打开游标 open syscolumns_cursor set @column='' set @columndata='' fetch next from syscolumns_cursor into @name,@xtype while @@fetch_status <> -1 begin if @@fetch_status <> -2 begin -- @xtype not in(189,34,35,99,98) and if columnproperty(object_id(@tablename),@name,'isidentity')=0 --timestamp不需处理,image,text,ntext,sql_variant 暂时不处理 begin set @column=@column + case when len(@column)=0 then '' else ',' end + @name set @columndata = @columndata + case when len(@columndata)=0 then '' else ','','',' end + case when @xtype in(167,175) then '''''''''+'+@name+'+''''''''' --varchar,char when @xtype in(231,239) then '''N''''''+'+@name+'+''''''''' --nvarchar,nchar when @xtype=61 then '''''''''+convert(char(23),'+@name+',121)+''''''''' --datetime when @xtype=58 then '''''''''+convert(char(16),'+@name+',120)+''''''''' --smalldatetime when @xtype=36 then '''''''''+convert(char(36),'+@name+')+''''''''' --uniqueidentifier when @xtype =35 or @xtype = 99 then 'case when '+@name+' is null then ''NULL'' else '+'''''''''+'+'replace(convert(varchar(8000),'+@name+'),'''''''','''''''''''')' + '+'''''''''+' end' --when @xtype=34 then '''''''''+'+@name+'+''''''''' --image else @name end end end fetch next from syscolumns_cursor into @name,@xtype end close syscolumns_cursor deallocate syscolumns_cursor set @sql='set nocount on select ''insert '+@tablename+'('+@column+') values(''as ''--'','+@columndata+','')'' from '+@tablename print '--'+@sql exec(@sql) if @ident is not null print 'SET IDENTITY_INSERT '+@TableName+' OFF' END create table users(aa varchar(3),bb varchar(3),cc varchar(3)) insert into users select 111,222,333--先CTRL+T ,然后再执行 exec [sys_getdata] 'users' /* insert users(aa,bb,cc) values( '111' , '222' , '333' ) */
select 'insert into CardScoreSave (cardScoreSave_ID, CardScoreSave_CardID, CardScoreSave_Score, CardScoreSave_ConsumeScore,
CardScoreSave_Time, CardScoreSave_OperatorNo)
values ('+ case when cardScoreSave_ID is null then 'null' else ''''+convert(varchar(30), cardScoreSave_ID)+'''' end + ','
+ case when CardScoreSave_CardID is null then 'null' else '''' + convert(varchar(10), CardScoreSave_CardID) + '''' end + ','
+ case when CardScoreSave_Score is null then 'null' else '''' + convert(varchar(30), CardScoreSave_Score) + '''' end + ','
+convert(varchar(30), CardScoreSave_ConsumeScore)+','
+ case when CardScoreSave_Time is null then 'null' else '''' + convert(varchar(30), CardScoreSave_Time) + '''' end + ','
+ case when CardScoreSave_OperatorNo is null then 'null' else '''' + convert(varchar(10), CardScoreSave_OperatorNo) + '''' end + ')'
from CardScoreSave
where datediff(n, @datetimefrom, CardScoreSave_Time) >= 0 and datediff(n, CardScoreSave_Time, @datetimeto) >= 0
CardCashSave_PresentCash, CardCashSave_RedeemCash, CardCashSave_Time, CardCashSave_OperatorNo,
CardCashSave_businesslog_id)
values ('+ case when CardCashSave_ID is null then 'null' else '''' + convert(varchar(30), CardCashSave_ID) + '''' end + ','
+ case when CardCashSave_CardID is null then 'null' else '''' + convert(varchar(10), CardCashSave_CardID) + '''' end + ','
+ convert(varchar(30), CardCashSave_Cash) + ','
+ convert(varchar(30), CardCashSave_Score) + ','
+ convert(varchar(30), CardCashSave_PresentCash) + ','
+ convert(varchar(30), CardCashSave_RedeemCash) + ','
+ case when CardCashSave_Time is null then 'null' else '''' + convert(varchar(30), CardCashSave_Time) + '''' end + ','
+ case when CardCashSave_OperatorNo is null then 'null' else '''' + convert(varchar(10), CardCashSave_OperatorNo) + '''' end + ','
+ case when CardCashSave_businesslog_id is null then 'null' else '''' + convert(varchar(30), CardCashSave_businesslog_id) + '''' end + ')'
from CardCashSave
where datediff(n, @datetimefrom, CardCashSave_Time) >= 0 and datediff(n, CardCashSave_Time, @datetimeto) >= 0
order by CardCashSave_ID
(
@tablename sysname
)
as
begin
--begin declare
declare @column varchar(1000)
declare @columndata varchar(1000)
declare @sql varchar(4000)
declare @xtype tinyint
declare @name sysname
declare @objectid int
declare @objectname sysname
declare @ident int
set nocount on
set @objectid=object_id(@tablename);
set @objectname=rtrim(object_name(@objectId));
--end declare
/*判断对象是否存在*/
if @objectId is null
begin
print @tablename + '对象不存在'
return
end
/*判断对象是否在当前数据库中*/
if @objectname is null or charindex(@objectname,@tablename)=0
begin
print @tablename + '对象不在当前数据库中'
return
end
/*判断对象是否是表*/
if objectproperty(@objectid,'istable') <> 1
begin
print @tablename + '对象不是表'
return
end
select @ident=status&0x80 from syscolumns where id=@objectid and status&0x80=0x80
if @ident is not null
print 'set identity_insert '+ @tablename + ' on'
--定义游标,循环取数据并生成Insert语句
declare syscolumns_cursor cursor for
select c.name,c.xtype from syscolumns c where c.id=@objectid order by c.colid
--打开游标
open syscolumns_cursor
set @column=''
set @columndata=''
fetch next from syscolumns_cursor into @name,@xtype
while @@fetch_status <> -1
begin
if @@fetch_status <> -2
begin
-- @xtype not in(189,34,35,99,98) and
if columnproperty(object_id(@tablename),@name,'isidentity')=0 --timestamp不需处理,image,text,ntext,sql_variant 暂时不处理
begin
set @column=@column +
case when len(@column)=0 then ''
else ','
end + @name
set @columndata = @columndata +
case when len(@columndata)=0 then ''
else ','','','
end +
case when @xtype in(167,175) then '''''''''+'+@name+'+''''''''' --varchar,char
when @xtype in(231,239) then '''N''''''+'+@name+'+''''''''' --nvarchar,nchar
when @xtype=61 then '''''''''+convert(char(23),'+@name+',121)+''''''''' --datetime
when @xtype=58 then '''''''''+convert(char(16),'+@name+',120)+''''''''' --smalldatetime
when @xtype=36 then '''''''''+convert(char(36),'+@name+')+''''''''' --uniqueidentifier
when @xtype =35 or @xtype = 99 then 'case when '+@name+' is null then ''NULL'' else '+'''''''''+'+'replace(convert(varchar(8000),'+@name+'),'''''''','''''''''''')' + '+'''''''''+' end'
--when @xtype=34 then '''''''''+'+@name+'+''''''''' --image
else @name
end
end
end
fetch next from syscolumns_cursor into @name,@xtype
end
close syscolumns_cursor
deallocate syscolumns_cursor
set @sql='set nocount on select ''insert '+@tablename+'('+@column+') values(''as ''--'','+@columndata+','')'' from '+@tablename
print '--'+@sql
exec(@sql)
if @ident is not null
print 'SET IDENTITY_INSERT '+@TableName+' OFF'
END
create table users(aa varchar(3),bb varchar(3),cc varchar(3))
insert into users select 111,222,333--先CTRL+T ,然后再执行
exec [sys_getdata] 'users'
/*
insert users(aa,bb,cc) values( '111' , '222' , '333' )
*/