Is a cursor the best way to bulk insert to multiple tables with foreign key?
-
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.
-
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/61537
-
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 importpersondeclare @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
-
What else do you have to uniquely identifi your importpersons?
N 56°04'39.16"
E 12°55'05.25" -
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? -
I have the similiar issue . Is there a solution for this output clause or some other clause instead of looping through using cursor
-
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply