什么意思?insert into tb2
select * from tb1
?
select * from tb1
?
解决方案 »
- sql能不能批量插入随机数据(不用游标)
- 超简单存储过程,帮忙纠个简单的错误
- 从网上找的自动增加一列序号的代码,完美实现功能,就是速度差,几百条记录就较慢了。可以优化吗?
- 求助~~~~自动截断日志文件的语句不管用啊
- 触发器影响业务操作速度的问题?
- 在线等。解决马上结贴。年/月/日 小时:分钟,这种的日期格式对么?怎么提取这种类型的时间段?回者有分。
- 通过什么方式可以知道现有数据库中每个表的数据量?
- 有这样一个查询:select top 6 id,title from [mytable] where id in (34,279,34,44,45,33,46,279,45,46),怎样使结果按条件先后排序?
- 请问可不可以关闭数据库的日志?
- Sql 怎么实现分时段取数据然后更新本地数据库
- sql server如何将一张表导出数据成insert语句.
- 请教c++ +ado连接 sql server 2000 的问题
set QUOTED_IDENTIFIER ON
goALTER proc [dbo].[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 test(ID int,[name] varchar(10),Meno varchar(10))
insert test select 1, '张三','备注1'
insert test select 2,'李四','备注2'
insert test select 3,'王五','备注3'
select 'insert test select ' +cast(id as varchar(5))+','''+[name]+''','''+meno+''''from test
/*--------------------------------------------------
insert test select 1,'张三','备注1'
insert test select 2,'李四','备注2'
insert test select 3,'王五','备注3'(所影响的行数为 3 行)*/