How to use a case statement in an insert into stat

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to use a case statement in an insert into stat

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2009-10-14 : 22:43:31

Hello All,

I have two slightly different insert statements with different criteria, I think a case function would be appropriate in combining the two insert into statement. Does anyone know the syntax for a case statement for insert into statement?
Sample of the insert into statement

INSERT INTO table_name
(COL1, COL3)
SELECT COL1, COL3
FROM table_name
WHERE COL1 = 1

INSERT INTO table_name
(COL1, COL2, COL3)
SELECT COL1, COL2, COL3
FROM table_name
WHERE COL2 = 5

Please advice.

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-15 : 00:10:54

INSERT INTO Target_table_name
(COL1,COL2,COL3)
SELECT
CASE WHEN COL1=1 OR COL2=5 THEN COL1 ELSE NULL END,
CASE WHEN COL2=5 THEN COL2 ELSE NULL END,
CASE WHEN COL1=1 OR COL2=5 THEN COL3 ELSE NULL END
FROM Source_table_name

pras2007
Posting Yak Master

216 Posts

Posted - 2009-10-15 : 21:53:28

Thanks for the response Sanoj_av, I just want to make sure I have the format correct:

Below is my Separate insert statements:

INSERT INTO Table_1
(COL1, COL2, COL3, COL4, COL5)
SELECT A.COL1, A.COL2, B.COL3 AS Prev_COL3, A.COL4, B.COL5 AS Prev_COL5
FROM Table_1 AS A FULL OUTER JOIN
Table_1 AS B ON A.COL2 = B.COL2
WHERE (A.COL1 IS NULL) AND (B.COL4 = 0)

INSERT INTO Table_1
(COL1, COL2, COL3, COL4, COL5)
SELECT A.COL1, A.COL2, B.COL3 AS Prev_COL3, A.COL4, B.COL5 AS Prev_COL5
FROM Table_1 AS A FULL OUTER JOIN
Table_1 AS B ON A.COL2 = B.COL2
WHERE (A.COL1 IS NULL) AND (B.COL4 = 1)

and below is my insert statement using the case function:

SELECT
CASE WHEN (A.COL1 IS NULL) AND (B.COL4 = 0)
THEN
'',
A.COL2 = B.COL2,
A.COL3 = B.COL3,
A.COL4 = COL4 + COL5,
A.COL5 = B.COL5

ELSE
'',
A.COL2 = B.COL2,
A.COL3 = B.COL3,
A.COL4 = COL4 + COL5,
A.COL5 = B.COL5
END,
CASE WHEN (A.COL1 IS NULL) AND (B.COL4 = 1)
THEN
'',
A.COL2 *(-1) = B.COL2,
A.COL3 *(-1) = B.COL3,
A.COL4 *(-1) = B.COL4,
A.COL5 *(-1) = B.COL5
ELSE
'',
A.COL2 = 0,
A.COL3 = 0,
A.COL4 = 0,
A.COL5 = 0
END

COL1 is an identity field...

Can anyone tell me if this syntax is correct?

Please advice.

Thanks.

pras2007
Posting Yak Master

216 Posts

Posted - 2009-10-16 : 05:44:08

Any suggestions?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources