Insert records from one table to 2 other tables | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Insert records from one table to 2 other tables

Hi ,
I have a situation to insert records from one table to 2 other tables and the number of insertions are huge around 60,000 records and this number will increase in near future.
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
I am using cursors as i need to read each record from source table generate user id and person id from a third table and then insert each record in respective table 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 60,000. If some one need the code what i am using i can provide.
What is the format of the Unique_Id, and how does it increase? You might be able to fake it without having to call the sproc for each row. Also, if adding the new records is crucial, but assigning the PersonId or UserId is less urgent, then if the PersonId/UserId column is nullable you could insert the new entries, and after that start a loop through the rows with a null on the column, assigning the missing value. Since your process doesn’t seem to care that the Unique_Id is spread between two entities, and unless there’s some sort of prefix added that reflects the distinction between Person and User, then I would suggest that the whole Unique_Id is a totally arbitrary code, and might as well be generated through an identity column. Also, if the data structure of the system is not yet finalized, you might want to check whether you could use a single table with a column identifying the row as a User or a Person. An identity column is guaranteed to have a unique value, so you would be certain that no User has the same Unique_Id as a Person.
Is userName column unique in all 3 tables?
Why don’t you use identity property for UserID and PersonID columns? (I agree with Adriaan)
Why do you have userName in both users and person table?
Do you check if user/person already exists in destination tables?
60,000 is not much if it is a one time deal.
Questions:
1. Is the relationship of User table to Person table is one is to one or one is to many? I guess from the algo, it seems it is one is to one.
2. Is person table superset of the user table?
3. How do you handle duplicates? The reason I am asking these questions is because you may need the identity column to populate the ID (saves you from querying the last id aside from the worry of data domain control). With identity column, you can use DataSet approach instead of the cursor approach you have right now (ie. INSERT INTO PersonTable (ID, ..) SELECT PersonID, .. FROM USER TABLE JOIN )
Another thing, from a design perspective, you may want to generate the PersonTable first before the User table since the PersonTable seems to be the Source Authority of the Person ID insetad of the User ID as implied in your algo. The reason that you may manually create an ID (similar to synonym in Oracle) is to enforce busines rule in ID generation (and you may want to use a function instead of just LastID). If you don’t have any business rule in ID generation, I suggest you use the identity.
May the Almighty God bless us all!
www.empoweredinformation.com
Hi Thanks for all your comments but the application is existing application and database can’t be changed The user id in users table is not null and is not auto generated
The persons id in users table is not null and is not auto generated
It is not possible to chnage the database structure 1. Is the relationship of User table to Person table is one is to one or one is to many? I guess from the algo, it seems it is one is to one.
Yes
2. Is person table superset of the user table?
Yes
3. How do you handle duplicates?
Duplicates are omited from the source table before it comes to the insertino of records.
The sample code is below: CREATE procedure proc_InsertNewUHSRecords
@Return_Message VARCHAR(200) OUTPUT– Returns Message
AS
/*
BEGIN
SET @intGroupID =1 BEGIN TRANSACTION
–Declare a Cutrsor for TempCouponsImport table
DECLARE curExistingRecords CURSOR
FOR
SELECT F2,F3,F4,F5,F6,F8,F10,F11,F12,F13,F16,UserName,UserName,F15 FROM TempEmployeeCSV
OPEN curExistingRecords
— Insert COUPON ID in a variable
FETCH NEXT FROM curExistingRecords into @strEmployeeNumber,@strTitle,@strFirstName,@strMiddleName,@strLastName,@strAddress,@strCity, @strState,@strPostalCode,@strCountry,@strProcessLevel,@strUserName,@strpassword,@stremail WHILE (@@FETCH_STATUS =0)
BEGIN
SELECT
@strEmployeeNumber,@strTitle,@strFirstName,@strMiddleName,@strLastName,
@strAddress,@strCity,@strState,@strPostalCode,@strCountry,@strProcessLevel,
@strUserName,@strpassword,@stremail — Get a new Id for person table
EXEC @return = sp_getNewID @id OUTPUT
SET @intPerson_id = @id
— Add a person record in persons table
INSERT INTO PERSONS
(Persons_ID,Persons_FirstName,Persons_MiddleName,Persons_LastName,Persons_EMail,Persons_Address,Persons_City,Persons_States_Abr,Persons_Zip,Persons_Countries_ID,Persons_ModifiedDate,Persons_Valid
) VALUES
(
@intPerson_id,@strFirstName,@strMiddleName,@strLastName,@stremail,@strAddress,@strCity,@strState,@strPostalCode,184,getdate(),1
) — Get a new Id for users table table EXEC @return = sp_getNewID @id OUTPUT
SET @[email protected] — Add a user record in users table table
INSERT INTO USERS
(
Users_ID,Users_Name,Users_PWD,Users_Persons_ID,Users_DateCreated,Users_ModifiedDate,Users_Valid,ProcessLevel
)
VALUES
(
@intUser_id,@strUserName,@strUserName,@intPerson_id,GETDATE(),GETDATE(),1,@strProcessLevel
) — Fetch next records from cursor
FETCH NEXT FROM curExistingRecords into @strEmployeeNumber,@strTitle,@strFirstName,
@strMiddleName,@strLastName,@strAddress,@strCity,
@strState,@strPostalCode,@strCountry,@strProcessLevel,
@strUserName,@strpassword,@stremail
— FETCH NEXT FROM curAttributeTable
END
–Close and deallocate the cursor
CLOSE curExistingRecords
DEALLOCATE curExistingRecords GO
Did any one got solution for my query???
You haven’t answered my questions.
Hi ya, as others have said the database structure and decision to not autogenerate what seems to be a normal identity column are the cause of your performance slow down, as it forces you to use the cursor. One thing you could perhaps do is modify the proc sp_getNewID to add a new optional parameter (set to 1 by default) @num_to_get In this proc set it to the number of rows in your source table, and call it before you fetch anything from the cursor. Then within the cursor you know that you have ‘reserved’ the correct number of identity values and so can just keep increasing your intperson_id and intuser_id variables. you could also possibly drop the users table and replace it with a view on the persons table (using person_id for both person_id and user_id) This would allow the application to use the users view without even realising, and saves you inserting the same data twice? Cheers
Twan
]]>