我刚学sql语句,在一次插入多行数据中,
insert into Card (ID,PassWord,Balance,UserName) select id,password,balance,userName
from Table_1
执行的时候就出现了
消息 544,级别 16,状态 1,第 1 行
当 IDENTITY_INSERT 设置为 OFF 时,不能为表 'Card' 中的标识列插入显式值。
insert into Card (ID,PassWord,Balance,UserName) select id,password,balance,userName
from Table_1
执行的时候就出现了
消息 544,级别 16,状态 1,第 1 行
当 IDENTITY_INSERT 设置为 OFF 时,不能为表 'Card' 中的标识列插入显式值。
go
insert into Card (ID,PassWord,Balance,UserName) select id,password,balance,userName
from Table_1
go
set IDENTITY_INSERT Card off
go
--测试的表
CREATE TABLE ta(id int IDENTITY(1,1),col int)
CREATE TABLE tb(id int IDENTITY(1,1),col int)
GO--设置 IDENTITY_INSERT 属性
SET IDENTITY_INSERT ta ON
SET IDENTITY_INSERT tb ON
GO
/*======================================================*/
--2. 如果插入记录的标识值大于表的当前标识值,则SQL Server自动将新插入值作为当前标识值使用
--测试的表
CREATE TABLE tb(id int IDENTITY(1,1),col int)--强制在表中插入标识值
SET IDENTITY_INSERT tb ON
INSERT tb(id,col) VALUES(10,1)
SET IDENTITY_INSERT tb OFFINSERT tb(col) VALUES(2)
SELECT * FROM tb
/*--结果
id col
----------------- -----------
10 1
11 2
--*/
GO
/*======================================================*/
--3. 如果插入记录的标识值小于表的当前标识值,则表的当前标识值不受新插入值的影响
--测试的表
CREATE TABLE tb(id int IDENTITY(1,1),col int)
INSERT tb VALUES(1)
INSERT tb VALUES(2)--强制在表中插入标识值
SET IDENTITY_INSERT tb ON
INSERT tb(id,col) VALUES(1,11)
SET IDENTITY_INSERT tb OFFINSERT tb(col) VALUES(3)
SELECT * FROM tb
/*--结果
id col
----------------- -----------
1 1
2 2
1 11
3 3
--*/
SET IDENTITY_INSERT dbo.Card ON
GO
insert into Card (ID,PassWord,Balance,UserName) select id,password,balance,userName
from Table_1
from Table_1
GO
insert into Card (ID,PassWord,Balance,UserName) select id,password,balance,userName
from Table_1
insert into Card (PassWord,Balance,UserName)
SET IDENTITY_INSERT tb ON
INSERT tb(id,col) VALUES(10,1)
SET IDENTITY_INSERT tb OFFINSERT tb(col) VALUES(2)
SELECT * FROM tb
id int identity,
ida int,
scode varchar(10),
data int
)create trigger tb_insert_y
on tb
for insert
as
begin
declare @ida int
select @ida=isnull(max(ida),0) from tb t
where not exists(select 1 from inserted where t.id=id)
update tb
set ida=@ida+1,scode=right(100000+@ida+1,5)
from tb t,inserted i
where t.id=i.id
end insert into tb(data)select id from tb_tmpselect * from tb