|
SET?QUOTED_IDENTIFIER?OFF????
GO???
SET?ANSI_NULLS?ON????
GO???
if?exists(select?1?from?sysobjects?where?id=object_id('BicashyOutputData')?and?xtype='P')??????
???drop?procedure?BicashyOutputData;???
GO???
create?procedure?dbo.BicashyOutputData(@tablename?varchar(256),@whereStr?varchar(256))???
AS?????
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)?????
??
if?@objectId?is?null?--?判斷對象是否存在?????
begin?????
print?'The?object?not?exists'?????
return?????
end?????
set?@objectname=rtrim(object_name(@objectId))?????
??
if?@objectname?is?null?or?charindex(@objectname,@tablename)=0?--此判断不严密?????
begin?????
print?'object?not?in?current?database'?????
return?????
end?????
??
if?OBJECTPROPERTY(@objectId,'IsTable')?<?>?1?--?判斷對象是否是table?????
begin?????
print?'The?object?is?not?table'?????
return?????
end?????
??
select?@ident=status&0x80?from?syscolumns?where?and?status&0x80=0x80?????
??
if?@ident?is?not?null?????
print?'SET?IDENTITY_INSERT?ON'?????
??
declare?syscolumns_cursor?cursor??
??
for?select?c.name,c.xtype?from?syscolumns?c?where?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?????
if?@xtype?not?in(189,34,35,99,98)?--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?--varchar,char?????
when?@xtype?in(231,239)?then?--nvarchar,nchar?????
when?@xtype=61?then?'''''''''+convert(char(23),'+@name+',121)+'''''''''?--datetime?????
when?@xtype=58?then?'''''''''+convert(char(16),120)+'''''''''?--smalldatetime?????
when?@xtype=36?then?'''''''''+convert(char(36),'+@name+')+'''''''''?--uniqueidentifier?????
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?)?values(''as?''--'','+@columndata+','');''?from??????
??
print??????
exec(@sql)?????
??
if?@ident?is?not?null?????
print?'SET?IDENTITY_INSERT?OFF'?????
??
??
GO???
SET?QUOTED_IDENTIFIER?OFF????
GO???
SET?ANSI_NULLS?ON????
GO?
?
?建立好存储过程以后，可以调用存储过程查看生成好的insert?插入语句。?
?????具体操作：
?????执行?exec?BicashyOutputData?cardinfo,'where?drawOutper="李佩娟"'
?????注意cardinfo是要执行的表名，'where?drawOutper="李佩娟"'?是where条件，如果不需要查询条件全部导出，则可以直接写为：?exec?BicashyOutputData?cardinfo,''
?????还需要注意的一点就是如查询条件中含有字符串，需要在字符串前后加?“”
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
