SQLTeam.com | Weblogs | Forums

Add static value to alternate rows of insert statement


#1

Hi Team,
I would like to add static values either "A" or "B" to newly inserted column which will generate records of 5 rows. I will generate this table in dynamic by passing variable so what ever output records, my columns should get alternatively A or B. Please let me know how can I achieve this.

Thank you,
Raj


#2

Hi

SELECT CASE WHEN FLOOR(RAND(CHECKSUM(NEWID()))* 2 + 1 ) = 1 THEN 'A' ELSE 'B' END AS F2
UNION ALL SELECT CASE WHEN FLOOR(RAND(CHECKSUM(NEWID()))* 2 + 1 ) = 1 THEN 'A' ELSE 'B' END
UNION ALL SELECT CASE WHEN FLOOR(RAND(CHECKSUM(NEWID()))* 2 + 1 ) = 1 THEN 'A' ELSE 'B' END
UNION ALL SELECT CASE WHEN FLOOR(RAND(CHECKSUM(NEWID()))* 2 + 1 ) = 1 THEN 'A' ELSE 'B' END
UNION ALL SELECT CASE WHEN FLOOR(RAND(CHECKSUM(NEWID()))* 2 + 1 ) = 1 THEN 'A' ELSE 'B' END

My output is:

F2
A
A
A
A
B

Base on this, you can add this directly or use a scalar function and call it from your code.

SELECT CASE WHEN ROUND(RAND(CHECKSUM(NEWID())) + 1 ,0) = 1 THEN 'A' ELSE 'B' END AS F2
UNION ALL SELECT CASE WHEN ROUND(RAND(CHECKSUM(NEWID())) + 1 ,0) = 1 THEN 'A' ELSE 'B' END
UNION ALL SELECT CASE WHEN ROUND(RAND(CHECKSUM(NEWID())) + 1 ,0) = 1 THEN 'A' ELSE 'B' END
UNION ALL SELECT CASE WHEN ROUND(RAND(CHECKSUM(NEWID())) + 1 ,0) = 1 THEN 'A' ELSE 'B' END
UNION ALL SELECT CASE WHEN ROUND(RAND(CHECKSUM(NEWID())) + 1 ,0) = 1 THEN 'A' ELSE 'B' END

#3

Thank you Stepson, will check and get back.