Which Method is best to copy data when the condition meets

Question

Which Method is best to copy data when the condition meets

zhtway

Old Hand

Points: 325
More actions
August 24, 2010 at 1:15 pm

#157166

Hi,
I have table1 and table2 and wants to MOVE the selected (on condition) rows from table1 to table2 when the condition meets.
Which method is the best? Trigger method or with Agent?
rgds,
zhtway

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

jvanderberg SSCertifiable Points: 6762 More actions · Answer 1

It depends on your needs. Using agent means it will operate on a schedule. If you wanted to go through and do this, say once a day or even every 5 minutes, agent will do that. Triggers are real-time. The whole operation will complete before the transaction ends. So if you move something from table1 to table2, it will effectively have never existed in table1.

It sounds like what you're looking for is an INSTEAD OF INSERT trigger. Instead of inserting into table1, you want anything that meets the condition to go into table2 instead.

zhtway Old Hand Points: 325 More actions · Answer 2

Hi,

Thanks for your info.

I am new to sql server and I want to know when the trigger kicks in.

You said " The whole operation will complete before the transaction ends."

OK. Here is what I am doing right now.

In table1, there are "ID" and "Status" columns and "Status" data will be True or False.

Users will be updating data through Replication process or from terminals.

I want to copy the particular "ID" rows when "Status" becomes "True".

As long as one status is still False for that ID, those will not be copied.

If one ID have all True value in "Status", that's needs to be copy to table2.

So that's why I am thinking of DML trigger. Any idea???

jvanderberg SSCertifiable Points: 6762 More actions · Answer 3

You've said move and copy and I'm not sure which you mean. When Status is TRUE, does the data still reside in table1, and copied to table 2, or is it removed from table1 and placed in table 2? For now I will assume copy.

Also, before it sounded like you only worried about INSERTS, but now it sounds like an UPDATE could make the status true, and the trigger should work then too. I'm going to assume this is the case.

Either way, this is a good start:

CREATE TRIGGER trgCheckStatusTrue ON Table1

FOR INSERT UPDATE

AS

INSERT INTO Table2

SELECT * FROM Inserted WHERE Status = 'True'

-- If you have multiple status fields, combine them here with AND

zhtway Old Hand Points: 325 More actions · Answer 4

Hi jvanderberg,

Thanks for your reply and I am sorry for the confusion.

Here is an example..

In Table1

row ID Status

1 1 True

2 1 True

3 1 False

4 2 False

5 2 False

Now, if user updates ROW-3 Status to "True"....then all the Status for ID=1 becomes "True". Then I want to COPY it to table2 and DELETE on table1. (which means the same as MOVE) 😀

even if ROW=4 is updated to "True", that will still be in table1 bcoz ROW=5 is still "False".

So basically, trigger will kick in after the updates on the table1.

I hope you understand.

thanks

zhtway

jvanderberg SSCertifiable Points: 6762 More actions · Answer 5

jvanderberg

SSCertifiable

Points: 6762

August 24, 2010 at 2:21 pm

#1211288

What do you copy then? Do you copy all three rows?

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

zhtway (8/24/2010)
I have table1 and table2 and wants to MOVE the selected (on condition) rows from table1 to table2 when the condition meets.
Which method is the best? Trigger method or with Agent?

I'll feel more comfortable answering your question after you clear a couple of doubts I have...

-1- MOVE?

Is it "move" as in row goes to table2 and is not longer on table1?

Or you mean "copy" as in a copy of the row gets inserted into table1?

-2- Selected On Condition

When is this move/copy supposed to happen?

Is it at the time you insert on table1 or is it at the time you decide it is time to move/copy rows matching some condition?

Thanks.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

zhtway Old Hand Points: 325 More actions · Answer 7

Hi All,

for jvanderberg,

Yes, All three rows will be moved to table2.

for PaulB-TheOneAndOnly,

1) All rows (when condition meets) will be moved to table2. "Status" will be updated to "True" by user at first. Then when the condition meets, will be moved to table2. So it will not be in table1.

2) Conditions is when Status for Particular ID becomes "True". All rows for that ID will be moved.

Thanks

zhtway

jvanderberg SSCertifiable Points: 6762 More actions · Answer 8

Something like this:

CREATE TRIGGER trgCheckForStatusTrue ON Table1

FOR INSERT UPDATE

AS

INSERT INTO Table2

SELECT Table1.* FROM Inserted

LEFT JOIN Table1 ON Inserted.ID = Table1.ID

WHERE NOT EXIST

(SELECT 1 FROM Table1 WHERE ID = Inserted.ID

AND Status = 'False')

DELETE FROM Table1

WHERE ID IN

(SELECT ID FROM Inserted WHERE NOT EXISTS

(SELECT 1 FROM Table1 WHERE ID = Inserted.ID

AND Status = 'False')

Test, test, test before you actually deploy something like this.

zhtway Old Hand Points: 325 More actions · Answer 9

Hi,

In 2005 Management Studio, the table1 was expended and right click on trigger and then New trigger was chosen.

When the windows was opened, I clear those codes and type your code.

and I just press the save button.

But it didn't appear under triggers?

Do you know where it goes?

thanks

zhtway

jvanderberg SSCertifiable Points: 6762 More actions · Answer 10

You don't save, you need to hit execute. BEFORE YOU DO THAT, confirm that you are on a development, test, or sandbox instance. This script could potentially delete records that you don't wish to have deleted. DO NOT run it on a production server until you have ensured it works fine. I just put it together in a few minutes, there could be bugs, don't treat it as gospel.

zhtway Old Hand Points: 325 More actions · Answer 11

Hi jvanderberg,

It seems like working. Well..I'll have to test with some data and have to make sure ev'thing is right.

thanks for your help.

zhtway

jvanderberg SSCertifiable Points: 6762 More actions · Answer 12

Glad to hear. Make sure to test the extremes. The script I gave you isn't very NULL resistant, so if you've got any nullable columns, test putting a NULL in that column.

Good luck.

jvanderberg SSCertifiable Points: 6762 More actions · Answer 13

Another thing I'd test is performance. I don't know how big your table is now, but operate under the assumption that it will grow to be much larger (businesses want to grow). What if you had 100,000 or a million rows, and you updated that status to 'True' all at once? How long does that take to process? What if you updated them all to 'False'? Compare these to how long it takes without the trigger. If a large insert or update is going to take a long time, you'll want to know about it. Also, you may be able to optimize.

zhtway Old Hand Points: 325 More actions · Answer 14

Hi jvanderberg,

Thanks for your inputs and idea.

Actually table1 is like collecting daily transactions, so probably it is just only 300 records in a day and may be it will grow to 500. Not more than that.

Table2 will be like a history table, so eventually it will grow day by day. I will have think of it ...

rgds,

zhtway