Insert problem? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Insert problem?

Author

Topic

Riku Tuominen
Starting Member

22 Posts

Posted - 2002-04-24 : 10:55:35

I would need to copy multiple records from table to an other. Sametime also copying multiple records from a subtable to an other table.

I would like to do it with a stored procedure.
Problem is that idDepartment and idPerson primary fields as ex. are of type auto number therefore idDepartment value in PERSON2 table needs to be corrected to relate to the new records idDepartment value.
Reminding that DEPARTMENT1, PERSON1 tables are related to each other.

DEPARTMENT1 PERSON1
idDepartment idPerson
name name
idDepartment

DEPARTMENT2 PERSON2
idDepartment idPerson
name name
idDepartment

Any ideas?

izaltsman
A custom title

1139 Posts

Posted - 2002-04-24 : 16:43:58

If you are just copying data into empty tables, you should simply issue a SET IDENTITY_INSERT command in order to preserve the existing person and department ids.

So I'm thinking something along these lines (haven't tested):


DECLARE @rollback int

SET @rollback = 0

BEGIN TRAN
SET IDENTITY_INSERT Department2 ON 
insert into Department2
Select * from Department1

IF @@ERROR <> 0 
BEGIN
   SET @rollback = 1
   GOTO CommitRollback
END

SET IDENTITY_INSERT Department2 OFF

SET IDENTITY_INSERT Person2 ON 
insert into Person2
Select * from Person1

IF @@ERROR <> 0 
BEGIN
   SET @rollback = 1
   GOTO CommitRollback
END

SET IDENTITY_INSERT Person2 OFF


CommitRollback:
IF @rollback = 0
BEGIN
   COMMIT TRAN
END
ELSE 
BEGIN
   ROLLBACK TRAN
END

If the target tables already contain some data, the script will be a little bit more complex: at the beginning of transaction you will have to grab the highest identity values from both target tables, and add them to the appropriate key values during your inserts.

Edited by - izaltsman on 04/24/2002 16:47:33

Riku Tuominen
Starting Member

22 Posts

Posted - 2002-04-24 : 17:43:57

Thanks but, I am not copying to empty tables.
Any ideas?

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-04-24 : 18:20:07

If the destination tables only receive data from this process, then you can still use the SET IDENTITY_INSERT ON because the numbers will always match. BUT, if they can have data entered into them some other way, which would cause the IDENTITY numbers to get out of sync with the source tables, then maybe this will work:

Add a column to the destination table to hold the original ID value from the source table. Then do something like

UPDATE destChild
SET destChild.LinkID = destParent.NewID
FROM destChild
JOIN destParent on destChild.NewID = destParent.OldID

Riku Tuominen
Starting Member

22 Posts

Posted - 2002-04-25 : 03:46:18

Thank you Mark for your reply!

I created one solution yesterday. I wonder though if there is any easyer solution. I don't like having to add extra columns to the database, maybe it is a must?

My solution:
http://www.benchmarking.fi/programming
goto "tips and tricks" - "sql" - "How do I copy multiple records from main table and it's sub tables to two other tables"

izaltsman
A custom title

1139 Posts

Posted - 2002-04-25 : 08:30:24

quote:

Thanks but, I am not copying to empty tables.
Any ideas?

For some reason the portion of my reply that dealt with your situation didn't get rendered correctly. Here it is again:

If the target tables already contain some data, the script will be a little bit more complex: at the beginning of transaction you will have to grab the highest identity values from both target tables, and add them to the appropriate key values during your inserts. Be sure to lock target tables exclusively for the duration of the transaction.

---------------
Strong SQL Developer wanted in the Boston area. Please e-mail if interested.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources