October 6, 2008 at 4:18 am
Hello All,
I have read many times that cursors are bad and try to avoid using them. However, I have to bulk update some tables with new data on a nightly basis and the only way I can see to do this at the moment is via a cursor and I am looking for other possible methods before I dance with the devil!
Firstly the schema is not under my control as the load is into purchased a piece of software’s database. ImportPerson is my staging table with a Name and SystemKey fields. The name field needs to be loaded into a table called Person each person then gets and a seeded identity from the Person table, the system key information is added to a SystemKeys table with the identity from the Person table as a foreign key. My initial thinking leads me to some code as outlined below:
declare person_cur cursor fast_forward for
select name,systemkey from importperson;
declare @name varchar(100),
@id bigint,
@perrsonid bigint,
@systemkey varchar(50)
declare @sql nvarchar(4000),
@Parm nvarchar(500);
open person_cur;
fetch next from person_cur into @name,@systemkey;
while @@fetch_status = 0
begin
set @sql = 'insert into person (name) values (@name);';
set @sql = @sql + ' select @id=scope_identity();';
set @parm=N'@name varchar(100),@personid bigint output';
exec sp_executesql @sql,@parm,@name=@name,@id=@personid OUTPUT;
insert into SystemKeys(person_id,systemkey) values (@id,@systemkey);
set @name = null;
set @id = null;
set @systemkey = null;
fetch next from person_cur into @name,@systemkey;
end
close person_cur;
deallocate person_cur;
Does anyone have an alternative method that would be an improvement on the method above? I have considered bulk insert into person first but I do not have a way to join ImportPerson to Person and retrieve the keys for the second SystemKeys insert as the name field is not unique.
Many Thanks,
Paul.
October 6, 2008 at 4:57 am
This may help, using the Sql Server 2005 OUTPUT clause. This assumes name is unique in importperson
declare @t table(name varchar(100),person_id bigint)
insert into person(name)
output inserted.name,inserted.person_id into @t(name,person_id)
select name
from importperson
insert into SystemKeys(person_id,systemkey)
select t.person_id,p.systemkey
from importperson p
inner join @t t on t.name=p.name
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537October 6, 2008 at 7:06 am
Thanks for the reply Mark. I have had a look at and play with the OUTPUT clause, the problem is that I can't guarantee the name field is unique and I could not find a way to pass the importperson.systemkey to the table populated by the OUTPUT clause. But it is an interesting option that I have not used before.
Mark (10/6/2008)
This may help, using the Sql Server 2005 OUTPUT clause. This assumes name is unique in importperson
declare @t table(name varchar(100),person_id bigint)
insert into person(name)
output inserted.name,inserted.person_id into @t(name,person_id)
select name
from importperson
insert into SystemKeys(person_id,systemkey)
select t.person_id,p.systemkey
from importperson p
inner join @t t on t.name=p.name
October 6, 2008 at 7:16 am
What else do you have to uniquely identifi your importpersons?
N 56°04'39.16"
E 12°55'05.25"
October 7, 2008 at 3:31 am
Hi, the only key I have to link to the import without making any assumptions is the systemkey field, the name field is actually split into forename, surname, title. The only other info I have is an addressid this id is loaded into another table that also requires the personid as a foreign key from the person table insert.
Peso (10/6/2008)
What else do you have to uniquely identifi your importpersons?
July 20, 2010 at 8:11 am
I have the similiar issue . Is there a solution for this output clause or some other clause instead of looping through using cursor
July 20, 2010 at 9:27 am
That is one of the ways you can do it without a cursor:
declare @iLastPersonId int
create table #t_person
(
person_id INT NOT NULL IDENTITY(1,1),
name VARCHAR(100),
systemkey VARCHAR(50)
)
begin transaction
-- the following will get last person_id and lock this table for period of transaction
select @iLastPersonId = ISNULL(MAX(person_id),0)
from person with (tablockx)
if @iLastPersonId > 0 then
begin
-- "reseed" temp table
set identity_insert #t_person on
insert into #t_person (person_id) select @iLastPersonId
set identity_insert #t_person off
delete #t_person
-- next person_id generated in #t_person will be @iLastPersonId + 1
end
insert into #t_person (name, systemkey)
select name, systemkey from importperson
set identity_insert person on
insert into person (person_id, name)
select person_id, name
from #t_person
set identity_insert person off
insert into SystemKeys (person_id, systemkey)
select person_id, systemkey
from #t_person
commit transaction
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy