February 2, 2004 at 8:18 pm
Hi,
Before running some Day End procedures, I usually make some backup. I have this table that has an identity column. When I want to insert the back up copy, I have to go to EM and remove the identity, then copy the records and then re-enable the identity.
Is there a way to do that? I've tried Set Identity_insert tablename off but that's only applicable if I insert one row at a time right?
February 3, 2004 at 2:09 am
Are you looking for something like this?
set nocount on
if object_id('MyIdentity') is not null
drop table MyIdentity
create table myidentity(id int identity(1,1), col1 char(1))
declare @i int
set @i=10
insert into myidentity(col1) values('a')
insert into myidentity(col1) values('b')
insert into myidentity(col1) values('c')
insert into myidentity(col1) values('d')
set identity_insert myidentity on
while @i < 25
begin
insert into myidentity(id, col1) values(@i, 'a')
set @i=@i+1
end
set identity_insert myidentity off
insert into myidentity(col1) values('q')
select * from myidentity
set nocount off
id col1
----------- ----
1 a
2 b
3 c
4 d
10 a
11 a
12 a
13 a
14 a
15 a
16 a
17 a
18 a
19 a
20 a
21 a
22 a
23 a
24 a
25 q
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
February 3, 2004 at 2:27 am
That's one way to do it. You are basically using "set identity_insert myidentity on" and then insert one row at a time, which is permissible. I was hoping to insert a whole bulk... Something like that:
1. Remove the "identity"
2. Insert into myidentity (id, col1) Select id, Col1 from BackUpMyIdentity
3. Restore the "identity"
To use the above method, I'll have to create a cursor on the BackUpMyIdentity or a similary way to insert one record by one record. If there's no other solution, I'll have to go for that I guess..
Thanks anyway
February 3, 2004 at 2:38 am
Why do you need a cursor?
set nocount on
if object_id('myIdentity') is not null
drop table myIdentity
if object_id('myresult') is not null
drop table myresult
declare @i int
set @i = 10
create table myidentity(id int identity(1,1), col1 char(1))
create table myresult(id int identity(1,1), col1 char(1))
insert into myidentity(col1) values('a')
insert into myidentity(col1) values('b')
insert into myidentity(col1) values('c')
insert into myidentity(col1) values('d')
set identity_insert myidentity on
while @i<= 25
begin
insert into myidentity(id, col1) values(@i,'d')
set @i=@i+1
end
set identity_insert myidentity off
set identity_insert myresult on
insert into myresult (id, col1) select id, col1 from MyIdentity
set identity_insert myresult off
select * from myresult
set nocount off
id col1
----------- ----
1 a
2 b
3 c
4 d
10 d
11 d
12 d
13 d
14 d
15 d
16 d
17 d
18 d
19 d
20 d
21 d
22 d
23 d
24 d
25 d
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply