Append and Update data - 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

SQL Server Development (2000)

Append and Update data

Author

Topic

rmrper99
Starting Member

29 Posts

Posted - 2004-04-05 : 16:04:47

Greetings. I have a situation that I would like help with. I have created a DTS to pull data from one table to another. To help with my scenario, here is part of my table structure

Table1 – (USERID, YEAR, FAMILYID, DELETED, (other fields))
PK – USERID, YEAR

Table2 – (USERID, YEAR, FAMILYID, DELETED)
PK – USERID, YEAR

I would like to update Table2 once a day. If FAMILYID or DELETED has changed for the USERID, YEAR combination I would like to update the record in Table2. If there is not a current USERID, YEAR record in Table2, I would like the record added. How would I do this? I created a DTS package, but it won’t update current records. It wants to drop them and recreate them and I don’t want the record dropped.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-05 : 16:08:43

Do not use DTS for this. Create a trigger on Table1. The trigger will check if it is an update. If it is, it will update Table2 using the INSERTED table (INSERTED is a trigger table). If it isn't an update, it will insert the row into Table2. This trigger will be an AFTER trigger.

I would look up CREATE TRIGGER in SQL Server Books Online for how to create a trigger and what the INSERTED table is. If you need more help, let us know.

If you don't want to use a trigger for this, then still don't use DTS. This should all be done in T-SQL.

Tara

rmrper99
Starting Member

29 Posts

Posted - 2004-04-05 : 16:15:49

Tara,

Thanks. I thought about this, but can I schedule a trigger to run only once a day?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-05 : 16:23:00

No you can't. The trigger would fire each time an UPDATE or an INSERT occurred on the table. If you want just once a day, you can mimic the trigger using a job that runs T-SQL commands. Why do you only want it to run once a day?

Tara

rmrper99
Starting Member

29 Posts

Posted - 2004-04-05 : 16:24:20

Thanks for all of the info. I only wanted it to replicate once a day because the table that the information is coming from is only updated once a day.

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-04-05 : 16:28:42

If it's only once a day, then why not use the trigger?
When the update happens, the trigger will fire for each row. The second table will then be updated once the first table is done updating.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-05 : 16:30:34

For the once a day way:

UPDATE t2
SET FAMILYID = t1.FAMILYID, DELETED = t1.DELETED
FROM Table2 t2
INNER JOIN Table1 t1 ON t1.USERID = t2.USERID AND t1.YEAR = t2.USERID
WHERE t1.DELETED <> t2.DELETED OR t1.FAMILYID <> t2.FAMILYID

The above does the updates, at least it should.

Here's the new ones:

INSERT INTO Table2 (USERID, YEAR, FAMILYID, DELETED)
SELECT USERID, YEAR, FAMILYID, DELETED
FROM Table1 t1
LEFT OUTER JOIN Table2 t2
ON t1.USERID = t2.USERID AND t1.FAMILYID = t2.FAMILYID
WHERE t2.USERID IS NULL AND t2.FAMILYID IS NULL

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-05 : 16:31:29

I agree with Michael.

Tara

rmrper99
Starting Member

29 Posts

Posted - 2004-04-05 : 16:33:48

Yea, Michael, you and Tara are right. I guess I just had it set in my mind that there was a better way. The trigger will work just fine.

Thanks again! I appreciate the info!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources