May 10, 2006 at 8:57 am
Hi ,
I have a situation to insert records from one table to 2 other tables.
Situation is as below:
Table name and there structure is as below
source table
user name|First name|last name | address
users table
user id |user name | person id
person table
person id |user name | First name|last name | address
Person id,user id are not autogenerated columns
1.read each record from source table into a cursor
2. Generate user id [This id i fetch from a fourth table called as Unique_Id which store the last Unique Id]
3. insert username and User id in users table
4. Generate person id [This id fetch from a fourth table called as Unique_Id which store the last Unique Id]
Get value from Unique_Id table and 1 to it
5. Insert person details with new person id
get next record from cursor
Is there any other way to accomplish this task as the number of records are around 600000
Pavas
Dream The Dream
Explore the World
Experince The Exhilaration
May 10, 2006 at 9:01 am
Sorry a bit mistake total number of records are 60,000.
Pavas
Dream The Dream
Explore the World
Experince The Exhilaration
May 10, 2006 at 10:16 am
Try something like this to avoid cursors:
--create tables
create table source(username varchar(255) PRIMARY KEY, firstname varchar(255), lastname varchar(255), address varchar(255))
create table person(personid int PRIMARY KEY, username varchar(255), firstname varchar(255), lastname varchar(255), address varchar(255))
create table (userid int PRIMARY KEY, username varchar(255), personid int)
create table uniqueid(lastid int)
insert uniqueid values(0)
go
insert source values('hermanm', 'herman', 'munster', '1313 Mockingbird Lane')
insert source values('lilym', 'lily', 'munster', '1313 Mockingbird Lane')
insert source values('ralphk', 'ralph', 'kramden', '65 123rd Street')
go
--create some working tables
create table #user(userid int identity(1,1) PRIMARY KEY, username varchar(255), personid int)
create table #person(personid int identity(1,1) PRIMARY KEY, username varchar(255), firstname varchar(255), lastname varchar(255), address varchar(255))
BEGIN TRANSACTION
--update the uniqueid table to lock it
update uniqueid
set lastid = lastid + 1
insert #person(username, firstname, lastname, address)
select username, firstname, lastname, address
from source
insert person(personid, username, firstname, lastname, address)
select personid+lastid, username, firstname, lastname, address
from #person, uniqueid
update uniqueid
set lastid = x.maxid
from (select max(personid) maxid from person) x
insert #user( username, personid)
select username, personid
from person
insert (userid, username, personid)
select userid+lastid, username, personid
from #user, uniqueid
update uniqueid
set lastid = x.maxid
from (select max(userid) maxid from ) x
COMMIT
go
select * from person
select * from
go
--clean up temporary tables
drop table #person
drop table #user
go
drop table person
go
drop table source
go
drop table
go
drop table uniqueid
go
May 18, 2006 at 6:24 am
thanks , Sorry for the delay in reply.
I have got the exact solution from sqlserver performance.
Thanks for Your all great efforts
Pavas
Dream The Dream
Explore the World
Experince The Exhilaration
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply