34,593
社区成员
发帖
与我相关
我的任务
分享
set IDENTITY_INSERT c5erp_bak.dbo.SC_FHD_T ON
INSERT INTO c5erp_bak.dbo.SC_FHD_T
SELECT * FROM SC_FHD_T
WHERE id NOT exists (SELECT id FROM c5erp_bak.dbo.sc_fhd_t) with(Rowlock)
if object_id('test.dbo.tb') is not null drop table tb
go
create table tb
(
id int identity(1,1),
p_id int,
photo int,
p_type int,
c_name int,
e_name int,
o_name int
)
go
declare @str varchar(1000)
set @str=''
select @str=@str+','+rtrim(name) from syscolumns where id=object_id('tb')
print @str --,id,p_id,photo,p_type,c_name,e_name,o_name
只列部分字段插入
set IDENTITY_INSERT c5erp_bak.dbo.SC_FHD_T ON
INSERT INTO c5erp_bak.dbo.SC_FHD_T(ID,clo1,col2,col3)
SELECT id,col1,col2,col3 FROM SC_FHD_T
WHERE id NOT IN (SELECT id FROM c5erp_bak.dbo.sc_fhd_t)
万一还试不行的话
你把要插入的数据和对应的表列名称
一一对应
set IDENTITY_INSERT c5erp_bak.dbo.SC_FHD_T ON
INSERT INTO c5erp_bak.dbo.SC_FHD_T
SELECT * FROM SC_FHD_T
WHERE id NOT IN (SELECT id FROM c5erp_bak.dbo.sc_fhd_t)
set IDENTITY_INSERT c5erp_bak.dbo.SC_FHD_T OFF
你确认这个c5erp_bak.dbo.SC_FHD_T是表名吗?
set IDENTITY_INSERT c5erp_bak.dbo.SC_FHD_T ON
INSERT INTO c5erp_bak.dbo.SC_FHD_T
SELECT * FROM SC_FHD_T
# --(2)当设置identity_insert选项值为 ON的时候,可以插入id.
# create table #te
# (
# id int identity(1,1),
# col1 varchar(10)
# )
# insert into #te values ('aa')
# insert into #te values ('bb')
# select * from #te
# set identity_insert #te on
# insert into #te(id,col1) values (10,'ff')
#
# set identity_insert #te off
# insert into #te values ('hh')