INSERT w/ VALUE from ANOTHER TABLE - How?? 2

[eb24]

I have 2 stored procedures I would like to combine to make one to handle the tasks.

My first SP INSERTs values from one table to another:

INSERT INTO Table_1
SELECT Field_1, Field_2, Field_3, NULL FROM Table_2
WHERE Field_1 NOT IN (SELECT Field_1 FROM Table_1)
AND Field_5 = 4

My next SP UPDATEs the INSERTed INTO table with the field from the above SP that has a value of NULL inserted as follows:

UPDATE Table_1
SET Field_4 =
    CASE 
	WHEN Field_2 IN (SELECT Field_9 FROM Table_4)
        THEN 1
	WHEN Field_7 LIKE '%something%' THEN 2
        ELSE 3
    END
WHERE Field_4 IS NULL

Basically, what I would like to do is just keep the first SP and instead of INSERTing a value of NULL, I would like to look for it in something like the CASE statement of my second SP.

Any assistance will be greatly appreciated.

 

[donutman]

INSERT INTO Table_1
SELECT Field_1, Field_2, Field_3, CASE
WHEN Field_2 IN (SELECT Field_9 FROM Table_4) THEN 1
WHEN Field_7 LIKE '%something%' THEN 2
ELSE 3
END
FROM Table_2
WHERE Field_1 NOT IN (SELECT Field_1 FROM Table_1)
AND Field_5 = 4
-Karl

 

[dmcmunn]

Without knowing the details of your schema, this is my guess at what you might want to do ...

INSERT INTO Table_1
SELECT TABLE_2.COLUMN_1,
TABLE_2.COLUMN_2,
TABLE_2.COLUMN_3,
CASE WHEN TABLE_2.COLUMN_2 IN (SELECT COLUMN_9 FROM Table_4)
THEN 1
WHEN TABLE_2.COLUMN_7 LIKE '%something%' THEN 2
ELSE 3
END
FROM Table_2
WHERE Table_2.COLUMN_1 NOT IN (SELECT COLUMN_1 FROM Table_1)
AND TABLE_2.COLUMN_5 = 4

 

[nmlsupport]

May I know if there is any difference between executing the combined statement and executing each statement at sequence?

 

[donutman]

I doubt that there would be much difference, but if you have a very large table you could test it both ways and tell us! For most situations, the difference isn't worth investigating (don't misunderstand, I'm NOT saying you shouldn't be thinking about performance...this particular case isn't, imho, an issue) and since the two statement version isn't really any clearer, I'd vote for the combined.
-Karl