inserting data in two tables

Question

Hi,

Im migrating some legacy data into a table called persons.

i will be inserting 10000 records into the persons table using an insert statement.

the problem is the persons table also has a history table called person_history.

the primary key of persons table is the foreign key for the persons_history table and all other columns are the same as the parent table.

how can i update both the table

can i make use of scope_identity function.

if so how could i insert each record in the persons_history table while i run the insert script on the parent persons table.

note : i need to make sure the primary key from the persons table gets updated in the history table.

thanks

justin

Answer 1

use the output clause.  There are examples in BOL if needed.  Alternatively, create a trigger to do this. 

Answer 2

Im not aware of BOL. could you please explain

Answer 3

The clause OUTPUT clauses gives you the abilitiy to access the inserted and deleted tables that in version previous SQL Server 2005 were accesible only through triggers, for example:

INSERT INTO Customers (CustomerID,Names,Surnames)
OUTPUT getdate(),inserted.CustomerID INTO Audit
VALUES (1234,'Octavio','Hernandez')

In this case you can see how to use this clause with audit purposes.

I recommended this article about this topic:

www.simple-talk.com/.../implementing-the-output-clause-in-sql-server-2008/

---

"Talent is a tough discipline and a long patience"  Gustave Flaubert

Email: [e-mail] Blog: geeks.ms/blogs/ghernandez

 

 

 

Answer 4

Hi Justin,

BOL = Books Online, which is our product documentation.  Scott's recommendations are correct.  Using either the OUTPUT clause wth your INSERT statement or an AFTER INSERT trigger would work for you.  The OUTPUT clause was introduced in SQL Server 2008, so if you're using a version earlier than that, you'll need a trigger.

You probably have Books Online already installed on your machine, however, you should get the latest version of it.

Here are links to the SQL Server 2008 R2 BOL topics for OUTPUT and CREATE TRIGGER.  There are examples at the bottom of the topics.

http://msdn.microsoft.com/en-us/library/ms189799.aspx

http://msdn.microsoft.com/en-us/library/ms177564.aspx

Most folks just use a search engine to find Books Online topics, but you can also download BOL to your local machine.

SQL Server 2008 BOL download:  http://msdn.microsoft.com/en-us/sqlserver/cc514207

SQL Server 2008 R2 BOL download: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=9071

SQL Server 2005 BOL download: http://www.microsoft.com/download/en/details.aspx?id=4152

 

Kind regards,

Gail

 

---

Gail Erickson [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights

Answer 5

1) First of all why a forieghn key contraint exist here, I dont think there should be one... If in case a Person record is deleted from the persons table, all his history will be lost, which is not the intended behaviour. ( If you handling the delete over a flag as if as active/deleted, it may not be good as your data is too large..)

2) The output clause is introduced in the SQL Server 2008, so if you are migrating your data into a earlier version (<2008) then the below solution wont work.

UPDATE PERSONS
SET <your_update_conditions>
OUTPUT deleted.* INTO persons_history
WHERE <your_where_clause>

3) incase if you are migrating to an earlier version of SQL Server, ( say for instance SQL Server 2005). The simpler way to accomplish this task is to use a DML trigger

CREATE TRIGGER persons_tr
ON PERSONS
AFTER UPDATE
AS
BEGIN
insert into persons_history
SELECT deleted.* FROM persons
END

 

---

Nothing is Permanent... even Knowledge....
My Blog

Answer 6

Fro inserting new rows in primary table and then in history table, you would better go for TRIGGER option or a post insert batch operation.

For updating the PRIMARY KEY value and automatically reflecting the same in Foreign KEY you need is to set on update cascade propert of your FOREIGN KEY

I have tried to exemplify the same as below :

/***********************************

Create example tables with Primary KEY and Foreign KEY  relationships

*/

create table t(a int primary key, b varchar(10))

create table t1(a int, c varchar(10))

 

--create foreign key

alter table t1 add foreign key(a) references t(a)

on update cascade

 

/***********************************

--Insert data in Primary key table

*/

 

insert into t values(1, 'Alpha')

insert into t values(2, 'Beta')

insert into t values(3, 'Gama')

 

 

/***********************************

--Insert data in foreign key table

*/

insert into t1 values(1, 'Ray1')

insert into t1 values(2, 'Ray2')

insert into t1 values(3, 'Ray3')

insert into t1 values(1, 'Radio1')

insert into t1 values(2, 'Radio2')

insert into t1 values(3, 'Radio3')

 

 

/***********************************

--View the data before updating primary key value

*/

select * from t1

1     Ray1

2     Ray2

3     Ray3

1     Radio1

2     Radio2

3     Radio3

 

/***********************************

--Run UPDATE statement against primary key

*/

update t set a=4 where a=1

 

 

/***********************************

--View the data of foreign key table after updating primary key value

*/

select * from t1

 

4     Ray1

2     Ray2

3     Ray3

4     Radio1

2     Radio2

3     Radio3

 

 

---

Warm Regards, Ajay

Answer 7

Example for the OUTPUT clause:

http://www.sqlusa.com/bestpractices2008/output/

 

---

Kalman Toth, SQL Server & Business Intelligence Training; sqlusa.com