相同结构的表之间复制数据

Question

INSERT INTO Inventory3(col1,col2,col3...)
SELECT col1,col2,col3... FROM Inventory

Answer 1

仅当使用了列的列表，并且identity_insert为on时，才能在'indentory3'中为标识列指定显示列值

Answer 2

--可以用下面的存储过程
create proc p_insert
@d_tb sysname,   --目标表名
@s_tb sysname,   --源表名
@bz bit=0,       --@bz=0,按字段名相同做匹配,@bz非0,按字段顺序匹配
@identity bit=0  --@identity=0,不复制标识字段,@identity=1,复制标识字段
as
declare @fd1 nvarchar(4000),@fd2 nvarchar(4000),@head nvarchar(100)
select @fd1=N'',@fd2=N''
if @bz=0
select @fd1=@fd1+N','+quotename(a.name)
,@fd2=@fd2+N','+quotename(a.name)
from syscolumns a,syscolumns b
where a.name=b.name
and a.id=object_id(@d_tb)
and b.id=object_id(@s_tb)
else
select @fd1=@fd1+N','+quotename(a.name)
,@fd2=@fd2+N','+quotename(b.name)
from syscolumns a,syscolumns b
where a.colid=b.colid
and a.id=object_id(@d_tb)
and b.id=object_id(@s_tb)
and(@identity=1 or 
@identity=0 and a.status<>0x80)
if @identity=1
and exists(select * from syscolumns where id=object_id(@d_tb) and status=0x80)
set @head=N'set identity_insert '+@d_tb+N' on'
else
set @head=''

select @d_tb=quotename(@d_tb),@s_tb=quotename(@s_tb)
,@fd1=stuff(@fd1,1,1,'')
,@fd2=stuff(@fd2,1,1,'')exec(@head+N'
insert '+@d_tb+N'('+@fd1+N') select '+@fd2+N' from '+@s_tb)
go
--调用
exec p_insert 'jobs','jobs1',1,0
godrop proc p_insert

Answer 3

如果表结构一样，字段的排序也是一样的。INSERT INTO Inventory3 
SELECT * 
FROM Inventory这个应该是没有问题的。如果排序不一样，你在企业管理器中打开表的设计，调整一下字段的顺序。

Answer 4

SET IDENTITY_INSERT
允许将显式值插入表的标识列中。语法
SET IDENTITY_INSERT [ database.[ owner.] ] { table } { ON | OFF }参数
database是指定的表所驻留的数据库名称。owner是表所有者的名称。table 是含有标识列的表名。注释
任何时候，会话中只有一个表的 IDENTITY_INSERT 属性可以设置为 ON。如果某个表已将此属性设置为 ON，并且为另一个表发出了 SET IDENTITY_INSERT ON 语句，则 Microsoft&reg; SQL Server&#8482; 返回一个错误信息，指出 SET IDENTITY_INSERT 已设置为 ON 并报告此属性已设置为 ON 的表。如果插入值大于表的当前标识值，则 SQL Server 自动将新插入值作为当前标识值使用。SET IDENTITY_INSERT 的设置是在执行或运行时设置，而不是在分析时设置。权限
执行权限默认授予 sysadmin 固定服务器角色和 db_owner 及 db_ddladmin 固定数据库角色以及对象所有者。示例
下例创建一个含有标识列的表，并显示如何使用 SET IDENTITY_INSERT 设置填充由 DELETE 语句导致的标识值中的空隙。-- Create products table.
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
-- Inserting values into products table.
INSERT INTO products (product) VALUES ('screwdriver')
INSERT INTO products (product) VALUES ('hammer')
INSERT INTO products (product) VALUES ('saw')
INSERT INTO products (product) VALUES ('shovel')
GO-- Create a gap in the identity values.
DELETE products 
WHERE product = 'saw'
GOSELECT * 
FROM products
GO-- Attempt to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO products (id, product) VALUES(3, 'garden shovel')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO-- Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) VALUES(3, 'garden shovel').
GOSELECT * 
FROM products
GO
-- Drop products table.
DROP TABLE products
GO

Answer 5

--给出错误提示--用我下面的存储过程if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_insert]
GO/*--生成复制数据的sql语句 根据源表和目标表的字段对应关系,生成插入处理的sql语句
(适当比较懒的人 :D)
--邹建 2005.03(引用请保留此信息)--*//*--调用实例 exec p_insert 'jobs','jobs',1,1,0
--*/
create proc p_insert
@d_tb sysname,   --目标表名
@s_tb sysname,   --源表名
@bz bit=0,       --@bz=0,按字段名相同做匹配,@bz非0,按字段顺序匹配
@identity bit=0, --@identity=0,不复制标识字段,@identity=1,复制标识字段
@generatesql bit=1   --@generatesql=1,仅生成处理的sql语句,@generatesql=0,执行插入处理
as
declare @fd1 nvarchar(4000),@fd2 nvarchar(4000),@head nvarchar(1000),@end nvarchar(1000)
select @fd1=N'',@fd2=N''
if @bz=0
select @fd1=@fd1+N','+quotename(a.name)
,@fd2=@fd2+N','+quotename(a.name)
from syscolumns a,syscolumns b
where a.name=b.name
and a.id=object_id(@d_tb)
and b.id=object_id(@s_tb)
else
select @fd1=@fd1+N','+quotename(a.name)
,@fd2=@fd2+N','+quotename(b.name)
from syscolumns a,syscolumns b
where a.colid=b.colid
and a.id=object_id(@d_tb)
and b.id=object_id(@s_tb)
and(@identity=1 or 
@identity=0 and a.status<>0x80)
if @identity=1
and exists(select * from syscolumns where id=object_id(@d_tb) and status=0x80)
select @head=N'set identity_insert '+@d_tb+N' on'
,@end=N'set identity_insert '+@d_tb+N' off'
else
select @head=N'',@end=N''

select @d_tb=quotename(@d_tb),@s_tb=quotename(@s_tb)
,@fd1=stuff(@fd1,1,1,'')
,@fd2=stuff(@fd2,1,1,'')if @generatesql=1
exec(@head+N'
insert '+@d_tb+N'('+@fd1+N') select '+@fd2+N' from '+@s_tb+N'
'+@end)
else
print(@head+N'
insert '+@d_tb+N'('+@fd1+N') select '+@fd2+N' from '+@s_tb+N'
'+@end)
go

Answer 6

我把所有的字段都加上后，发现还不行，错误如下：
服务器: 消息 273，级别 16，状态 1，行 1
不能在 timestamp 列中插入非空值。请使用带有列的列表，或 timestamp 列默认值为 NULL 的 INSERT 语句。并且这个timestamp列并没有在我的表字段中显示，怎么办呢？

Answer 7

--看这个你就明白了declare @t table(a sysname,b timestamp)
insert @t(a) select name from sysobjects
select * from @t