sql server (alternate): Conditional Column Name On Insert -- sql server (alternate)

Re: Conditional Column Name On Insert

--CELKO--
1/21/2006 6:03:51 AM

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

Re: Conditional Column Name On Insert

Hugo Kornelis
1/21/2006 10:47:15 PM

[quoted text, click to view]

Hi Fabio,

You can't insert into just one column - you insert a complete row, and
you'll have to give values (either real values or NULL) for all columns.
Sure, the language permits you to leave out some columns, but that's
just a shorthand way for specifying that you want to insert the defined
DEFAULT value (if any) or NULL in all the other columns.

To do what you appear to want (and I *really* hope that this is an
extremely simplified illustration of the real problem, because if this
is your real procedure, you have much, much bigger problems), you can
either use the code posted by MGFoster, or use

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

--

Re: Conditional Column Name On Insert

cavassinif NO[at]SPAM gmail.com
1/22/2006 3:12:16 PM

Thanks for all your advices:

MGFoster:
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.

--CELKO--
Here's the explanation of the case:

Suppose that you have a Job Order that goes over diferrent states
(Draft, Confirmed, Assigned, Finished....)
Well, I need to save the Date when the Job Order changed it's state, so
I have the following columns in the JobOrder Table:
DraftDate : Date when the Job Order get's the Draft state
ConfirmedDate : Date when the Job Order get's the Confirmed state
AssignedDate : Date when the Job Order get's the Assignedstate
etc...

That's why I need to create a dynamic Insert, because depending the
state the Job Order will be saved....will depend which column
(DraftDate, ConfirmedDate, etc) it will have to insert the current
date.

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

Best Regards
Fabio Cavassini

Re: Conditional Column Name On Insert

Hugo Kornelis
1/23/2006 12:29:03 AM

[quoted text, click to view]

(snip)
[quoted text, click to view]

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)
[quoted text, click to view]

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.

--

Re: Conditional Column Name On Insert

pb648174
1/23/2006 1:40:29 PM

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.

[quoted text, click to view]

Re: Conditional Column Name On Insert

Ellen K
1/23/2006 4:25:28 PM

The problem is your database design. Instead of 30 columns for dates
for all the events applicable to the job, you should have two tables:
The first table has the job identification, a date column, and a column
for the event or event identifier.
The second table is the lookup table of events. Most people would set
this up with a numeric identifier as the primary key, and a description
column for the event description.

HTH

Re: Conditional Column Name On Insert

Ellen K
1/23/2006 4:25:54 PM

The problem is your database design. Instead of 30 columns for dates
for all the events applicable to the job, you should have two tables:
The first table has the job identification, a date column, and a column
for the event or event identifier.
The second table is the lookup table of events. Most people would set
this up with a numeric identifier as the primary key, and a description
column for the event description.

HTH

Re: Conditional Column Name On Insert

cavassinif NO[at]SPAM gmail.com
1/24/2006 5:06:59 AM

Thanks for all the replies,

I implemented it as Hugo sayed, with condition in the value:

[quoted text, click to view]

I haven't realized that in an insert...in fact all values are
modified....consequently I need to put the condition in the value.

[quoted text, click to view]

I know..that there are many columns....but...the business model
requires it. In addition I don't like to have many tables in my
databases, it's too much simple to maintain a reduced (respecting
normal forms, of course) set of tables.

Best Regards
Fabio Cavassini

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server (alternate) group.