Inserting from another table with conditions for a column

Question

I'm trying to insert all the data from my submit table into my fact table. All the fields line up except for the status column. The status table needs to be filled with a status based on the dates. I have the rules that I need for that, but I'm not sure where I should put that logic or even how to do it. Any help is appreciated.

Insert into dbo.FactSubmit
(
    ProjectKey,
    DueFromSubKey,
    SentToKey,
    DueFromArcKey,
    ReceivedDateKey,
    [Description],
            Status

)
Select 
    dp.ProjectKey,
    CONVERT(int, Convert(varchar, s.Due_From_Sub ,112)),
    CONVERT(int, Convert(varchar,s.Sent_To, 112)),
    CONVERT(int, Convert(varchar,s.Due_From_Arc, 112)),
    CONVERT(int, Convert(varchar,s.ReceivedDate, 112)),
    s.Item_Description
From stg.Submit s
INNER JOIN dbo.DimProject dp
    ON s.ProjectID = dp.ProjectID
INNER JOIN stg.Project sp
    ON sp.ProjectID = dp.ProjectID

What status table? There's nothing in your query that shows anything about a status table — Derek, Oct 10 '13 at 19:25

score 1 · Accepted Answer · edited May 23 '17 at 10:25

1

Something like this should be suitable for what you're doing. "Case" statements are a good approach for select statements like that.

You do the desired select as necessary, assure the columns match up and you are able to insert them

SELECT ..., 'Status' = CASE               --selecting what columns
     WHEN SomeColumn = 50 THEN 'Status1'  --this sets the logic 
     WHEN SomeColumn = 51 THEN 'Status2'  --for your case statement
     ELSE 'DefaultStatusValue'
  END
FROM stg.Submit s
INNER JOIN dbo.DimProject dp
ON s.ProjectID = dp.ProjectID
INNER JOIN stg.Project sp
ON sp.ProjectID = dp.ProjectID),

Referencing another StackQuestion.

MSDN can help too

EDIT: The 'Status' in quotes is saying what you want the new column name to be, and the "Case" statement handles what value will be selected for each row based on the logic there.

edited May 23 '17 at 10:25

Community

1
1

answered Oct 10 '13 at 19:44

ImGreg

2,946
16
43
65

I've looked at case statements, but it doesn't seem like I can insert into my status column since there is no equivalent in the table I'm selecting from. Unless I am misunderstanding how it works. – Danger_Fox Oct 10 '13 at 19:51
Okay I might have written this in a confusing way. Let me edit. – ImGreg Oct 10 '13 at 19:56
That makes sense and makes the other resources I was looking at make more sense. Thanks. – Danger_Fox Oct 10 '13 at 20:06

score 0 · Answer 2 · answered Oct 10 '13 at 19:45

0

You either want a where clause:

insert into table2
(f1, f2, etc)
select f1, f2, etc
from table1
WHERE YOUR CONDITIONS ARE MET

or a case construct

insert into table2
(f1, f2)
select f1
, CASE WHEN A CONDITION IS MET THEN VALUE1 ELSE VALUE2  END

or both

answered Oct 10 '13 at 19:45

Dan Bracuk

20,699
4
26
43

Inserting from another table with conditions for a column

2 Answers2

Linked