Posted by Hugo Kornelis on 01/23/06 01:29

On 22 Jan 2006 15:12:16 -0800, cavassinif@gmail.com wrote:

(snip)
>Yes, this would be a solution....but the table in which I'm inserting
>has more than 30 columns....the insert code is huge...and I wouldn't
>like to copy the insert for just one column of difference.

Hi Fabio,

Hmmm. Maybe you could explain in some more detail what is the actual
business problem you're trying to solve. A parameter that governs in
which of 30 columns the current date has to be inserted sounds as if the
best solution would be a redesign of your table - but I can only say for
sure if I know more about your actual problem and your current table
structure.

(snip)
>Hugo:
>I don't want to select a dynamic value... the value will be always the
>current date, I need to dinamically specify in which column I will
>insert the current date

I had used ellipsis as a placeholder for the value to delete. Now that I
know it's the current date, I can complete my proposed code. I've also
added a third column and ellipsis to show how you can extend this to as
many columns as you need.

INSERT INTO OT (OTFechaBorrador, OTFechaAAsignar, ThirdColumn, ...)
SELECT CASE WHEN @eotId = 1 THEN CURRENT_TIMESTAMP ELSE NULL END),
CASE WHEN @eotId = 2 THEN CURRENT_TIMESTAMP ELSE NULL END),
CASE WHEN @eotId = 3 THEN CURRENT_TIMESTAMP ELSE NULL END),
...

If called with @eotId equal to 1, this will create a row with
CURRENT_TIMESTAMP in the first column (OTFechaBorrador) and NULL in the
two (or more) other columns. If @eotId is 2, OTFechaAAsignar will be the
current datetime and the other columns are NULL. Etc, etc.

--
Hugo Kornelis, SQL Server MVP

[Back to original message]