How to insert artificial data into a select statement

Question

Hi guys:

Suppose we got a original query as follows:

SELECT A, B, C FROM tblA

Now, I need to additional artificial rows like SELECT 'Kyala', B, C FROM tblA when, for example, C = 100 to be inserted into the resultset.

As an example, if the tblA hold one row:

A      B      C
John   1      100

my goal is to return two rows like below with a single SQL query.

A      B      C
John   1      100
Kyala  1      100

How could I achieve it using a single SQL instead of relying on table variable or temp table?

Answer 1

You could achieve this with the UNION operator.

SELECT A, B, C from tblA
UNION
SELECT 'Kyala', B, C FROM tblA WHERE C = 100

In response to the question in the comments about improving performance so that the table is only queried once - you could add a covering index over columns C and B so that the second part of the query uses that index rather than querying the table:

CREATE NONCLUSTERED INDEX [IX_tblA_CD] ON [dbo].[tblA] 
(
    [C] ASC
)
INCLUDE ( [B]) ON [PRIMARY]
GO

However, depending on the use case (this sounds like some kind of ad-hoc process for testing?), you might prefer to take the hit of two table scans rather than adding a new index which might not be appropriate for use in production.

Answer 2

You can use UNIION statement:

SELECT A, B, C FROM tblA
UNION
SELECT 'Kyala', B, C FROM tblA WHERE C = 100

Answer 3

I need to additional artificial rows like SELECT 'Kyala', B, C FROM

tblA when, for example, C = 100 to be inserted into the resultset.

Now, read up on.... * IF in SQL Server *SWITCH etc.

Basically, you can define an additional column as was shown

(SELECT 'test', A, B, C FROM...)

But instead of 'test' you can put in an if or switch and work with the other fields to determine the exact stuff to output.

SELECT IF (xxxx) AS FirstColumn, A, B,
C FROM ...

Answer 4

Just refined the query to resolve error on Union:

SELECT A, B, C from tblA
UNION
SELECT 'Kyala' as A, B, C FROM tblA WHERE C = 100

And if you don't want the others where c=100 and still getting the A in the result (from the first Select in the union), you can do it like:

SELECT A, B, C from tblA WHERE C <> 100
UNION
SELECT 'Kyala', B, C FROM tblA WHERE C = 100

or

SELECT CASE(C)
           when 100 then 'Kyala'
           else A
       END as A, B, C from tblA

Answer 5

You can use a CASE:

SELECT B, C, 
       CASE 
          WHEN C = 100 THEN 'Kyala'
          ELSE A
       END
FROM tblA