Posted by pb648174 on 01/23/06 23:40

Do it in two steps: Insert the common column data first, then do an
update to the appropriate row/column based on the new entry and the
type.

Hugo Kornelis wrote:
> 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]