各位请教一下数据脚本生成的proc
最近很忙大家帮弄弄 原版
create procedure [dbo].[Sys_GetData]
@tablename sysname
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 @tablename + '对象不存在'
return
end
set @objectname=rtrim(object_name(@objectId))
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
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 '''''''''+'+@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
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'
需要大家修改的问题1 如果表包含自增列,在生成的insert语句中去掉这列
2 特殊字段的生成,上例中不能生成text,ntext的,希望更改成可以生成text,ntext的
3 如果text内容很长,保证可以把数据正确取出
最近很忙大家帮弄弄 原版
create procedure [dbo].[Sys_GetData]
@tablename sysname
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 @tablename + '对象不存在'
return
end
set @objectname=rtrim(object_name(@objectId))
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
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 '''''''''+'+@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
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'
需要大家修改的问题1 如果表包含自增列,在生成的insert语句中去掉这列
2 特殊字段的生成,上例中不能生成text,ntext的,希望更改成可以生成text,ntext的
3 如果text内容很长,保证可以把数据正确取出
解决方案 »
- SQL语句错误
- 这个SQL怎么写啊,2表关联
- 为什么 in 后面跟变量或函数应该怎么写?
- ACCESS数据导入到SQL表的问题!
- 问大家一个简单的问题,当某人职位跳级后,想保留以前的职位信息,怎么设计这个职位表呢?
- 斑竹书:346页,11.3.7 同步备份服务器--顶者有分!!!
- 菜鸟紧急求助:不同数据库如何同步,类似同数据库关系图组建关系
- 用sql语句怎样将一个字段的缺省值修改为一个新的缺省值(该字段原先可能有缺省值也可能没有)
- 急问:如何卸载SQL SERVER?!
- 如何通过程序将SQL Server的一个text字段的内容追加到另外一个text字段中
- 一个sql语句,更新和查询影响的行数不一样?
- 请大家帮忙看下这个SQL代码哪里出错了
什么程序?
drop proc dbo.proc_insert
go
create proc proc_insert (@tablename varchar(256))
as
begin
set nocount on
declare @sqlstr varchar(4000)
declare @sqlstr1 varchar(4000)
declare @sqlstr2 varchar(4000)
select @sqlstr='select ''insert '+@tablename
select @sqlstr1=''
select @sqlstr2=' ('
select @sqlstr1= ' values ( ''+'
select @sqlstr1=@sqlstr1+col+'+'',''+',@sqlstr2=@sqlstr2+name+',' from (select case
-- when a.xtype =173 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
when a.xtype =104 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(1),'+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(23),'+a.name +',121)'+ '+'''''''''+' end'
when a.xtype =106 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
when a.xtype =62 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
when a.xtype =56 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(11),'+a.name +')'+' end'
when a.xtype =60 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+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('+convert(varchar(4),a.xprec+2)+'),'+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(23),'+a.name +',2)'+' end'
when a.xtype =58 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
when a.xtype =52 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(12),'+a.name +')'+' end'
when a.xtype =122 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
when a.xtype =48 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(6),'+a.name +')'+' end'
-- when a.xtype =165 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+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(@tablename) and a.xtype <>189 and a.xtype <>34 and a.xtype <>35 and a.xtype <>36
)t order by colid
select @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+') '+left(@sqlstr1,len(@sqlstr1)-3)+')'' from '+@tablename
-- print @sqlstr
exec( @sqlstr)
set nocount off
end go
--测试键表
create table tb(id int,name varchar(50))
insert into tb select 1,'abc'
insert into tb select 2,'abc'
insert into tb select 3,'abc'
insert into tb select 4,'abc'
--执行过程,形成insert语句
exec dbo.proc_insert 'tb'
Go
Declare
@Table1 nvarchar(128),
@Table2 nvarchar(128),
@Sql1 nvarchar(4000),
@Sql2 nvarchar(4000),
@SqlIdentityInsert nvarchar(512)Set @Table1='DutyHistory' --源表
Set @Table2='Duty' --目标表IF Object_id(@Table1,'U') Is Null
Return
If Isnull(@Table2,'')=''
Set @Table2=@Table1Set @SqlIdentityInsert=''
If Exists(Select 1 From sys.columns Where Object_id=Object_id(@Table1,'U'))
Set @SqlIdentityInsert='Select ''Set Identity_Insert '+@Table2+' On '''Select
@Sql1=Isnull(@Sql1+',',' Insert Into '+@Table2+' (')+Quotename(name),
@Sql2=Isnull(@Sql2+'+'',''+','Select @Sql1+'' Select ''')+'+Case When '+Quotename(name)+' Is null Then ''null'' Else '+
Case When user_type_id In(175,61,239,231,58,98,36,167,241) Then '''''''''+Rtrim('+Quotename(name)+')+''''''''' Else 'Rtrim('+Quotename(name)+')' End +' End'
From sys.columns
Where Object_id=Object_id(@Table1,'U')Set @Sql1=@Sql1+')'
Set @Sql2= 'Select Convert(nvarchar(max),''If Object_id('''''+@Table2+''''',''''U'''') Is Null Return;'') As SqlInsert Union All '+
@SqlIdentityInsert+' Union All '+
@Sql2+' From '+Quotename(@Table1)+Case @SqlIdentityInsert When '' Then '' Else ' Union All ' End+
Replace(@SqlIdentityInsert,' On ',' Off')Exec sp_executesql @Sql2,N'@Sql1 nvarchar(4000)',@Sql1
Result:SqlInsert
---------------------------------------------------------
If Object_id('Duty','U') Is Null Return;
Set Identity_Insert Duty On
Insert Into Duty ([id],[Name],[WorkDate],[WorkHours]) Select 1,'Robert','01 1 2008 12:00AM',3.4
Insert Into Duty ([id],[Name],[WorkDate],[WorkHours]) Select 2,'Robert','01 2 2008 12:00AM',3.4
Insert Into Duty ([id],[Name],[WorkDate],[WorkHours]) Select 3,'Robert','01 3 2008 12:00AM',3.4
... ...
Insert Into Duty ([id],[Name],[WorkDate],[WorkHours]) Select 58,'Robert','02 27 2008 12:00AM',4.5
Insert Into Duty ([id],[Name],[WorkDate],[WorkHours]) Select 59,'Robert','02 28 2008 12:00AM',4.5
Insert Into Duty ([id],[Name],[WorkDate],[WorkHours]) Select 60,'Robert','02 29 2008 12:00AM',4.5
Set Identity_Insert Duty Off
紫煈的脚本
1 没有处理自增列的问题
2 没有处理text,ntext的问题
3 在我这有一个表没过去消息 102,级别 15,状态 1,第 1 行
Incorrect syntax near 'null'.
消息 102,级别 15,状态 1,第 1 行
Incorrect syntax near 'c'.
都要修改一下
我的表多,不方便用你的方法