How to Insert & Update Two Tables .? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How to Insert & Update Two Tables .?

Author

Topic

roxcy
Yak Posting Veteran

58 Posts

Posted - 2006-10-11 : 08:55:26

Hi,
I am using sqlServer 2000.I want to insert as well as update Two Tables at the Same Time using storedprocedure.Both My Tables Have the Same columns only difference being the name.My Tables are,
ADDRESS AND ADDRESSBOOK and their values are Name, Address, Place,
Date,City.
How can achieve this task using stored Procedures.?

Any Help would be appreciated....

Thanks...

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-10-11 : 08:59:32

I guess if everything is same then why dont you write a trigger on one of the table. so when you insert the records in one of the table the second table will automatically be updated.

for example create the trigger on the table Address and in the insert trigger what ever records which are getting insert into this table migrate it to the table AddressBook.

I hope that i have understood you right.

Chirag

http://chirikworld.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-11 : 10:04:17

or

In the procedure have Insert then update queries

Madhivanan

Failing to plan is Planning to fail

roxcy
Yak Posting Veteran

58 Posts

Posted - 2006-10-12 : 03:30:00

Hi,
I Tried out The Following Code using Triggers.I have been able To insert AND Update the records But While updating records are getting duplicated.Here is my code For Insert

CREATE trigger amcTrg_InsertCallDetails on
ADDRESS FOR INSERT
AS

INSERT ADDRESSBOOK( ID,Name, Address, Place,Date,City)

SELECT ID,Name, Address, Place,Date,City

FROM INSERTED

Code for Updating Trigger

CREATE TRIGGER amcTrg_UpdateCallDetails ON ADDRESS
FOR UPDATE
AS
INSERT ADDRESSBOOK (ID,Name, Address, Place,Date,City)

SELECT ID,Name, Address, Place,Date,City

FROM DELETED

INSERT ADDRESSBOOK

SELECT ID,Name, Address, Place,Date,City

FROM INSERTED

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-10-12 : 03:40:38

For your update trigger.



CREATE TRIGGER amcTrg_UpdateCallDetails ON ADDRESS
FOR UPDATE
AS

Delete A From ADDRESSBOOK A Inner Join Deleted D where A.[ID] = D.[ID] 

INSERT ADDRESSBOOK (ID,Name, Address, Place,Date,City)

SELECT ID,Name, Address, Place,Date,City

FROM DELETED

INSERT ADDRESSBOOK

SELECT ID,Name, Address, Place,Date,City

FROM INSERTED

Chirag

http://chirikworld.blogspot.com/

roxcy
Yak Posting Veteran

58 Posts

Posted - 2006-10-12 : 05:37:37

Hi,
I tried the code but I am getting an Error Incorrect Syntax near the keyword where.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-12 : 05:57:26

Change line in update trigger to:

Delete A From ADDRESSBOOK A Inner Join Deleted D on A.[ID] = D.[ID]

Harsh Athalye
India.
"Nothing is Impossible"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-10-12 : 06:40:04

Opps, Thanks Harsh it was typo

Chirag

http://chirikworld.blogspot.com/

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-10-12 : 09:56:28

I don't know much about much, but it seems to me that having two tables with the same schema and data that get the same DML at the same time could lead to a data integrity problem. Have you considered doing away with one of the tables and refactoring your system to use a single source of record?

Jay White

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources