Updating multiple columns

[Gaylord]

Hi,

This maybe a simple question, but I am trying to create a query to update a record within a database with a huge number of columns (somewhere around 200+).

My trigger so far is as follows, where it updates a record with a new date, but I need this to update each column. I read a number of posts and loops seem to cause delays and other problmes, so is there an easy way of doing this or do i have to do it long hand, i.e

data.name = inserted.name, data.date = inserted.date, etc, etc (which would take forever!)

CREATE TRIGGER [UPDATE]
ON [dbo].[TeleForm]
FOR INSERT
AS
UPDATE data
SET endDate = 01/01/2004
FROM inserted
WHERE data.Emp_No = inserted.Emp_No and inserted.complete = 1

Hope you can help me!

Regards

Jamie

 

[mrdenny]

Jamie,
Your trigger should work fine. I'm not clear on what your trying to do that's not working for you. If you need to add additional columns to your update statement you simply add them to the SET portion of the statement. If you can define what you are trying to do more, I can probably be of more help to you.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[Gaylord]

Thanks for answering Denny,

My question relates to if there is an easier way of generating the set command than entering each one, for example

data.name = inserted.name, data.date = inserted.date, and so on for the next 200 columns.

Is there any way of generating this programatically??

Regards

Jamie

 

[mrdenny]

You could use dynamic SQL in the trigger, but Dynamic sql has it's own issues. You could use the INFORMATION_SCHEMA views to get the column names, to make the actual writting of the trigger easier.

But what it comes down to, is that the best way to do it, will be to write it all out. At least you only have to write it out once.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[Gaylord]

I had a feeling that was going to be the case, oh well better bite the bullet and start typing!

Thanks for the help.

If anyone has a simple solution then let me know as it may well save my sanity

Regards

Jamie

 

[SQLSister]

Inthe first place, why are you adding all the other columns if you are just using the inserted values anyway? This isn't an instead of trigger, so they will already be in there.

If you want to specify lots of columns without typing them, I drag them over fromthe object browser in query analyzer, saves lots of time and you know they are spelled correctly.


Questions about posting. See faq183-874