CODEWITHSUNDEEP
sqlsql-server
Kumar
Kumar

Reputation: 39

select column with union and insert into table with remaining columns handpicked

TableA => (ColumnA , ColumnB, ColumnC , ColumnD )

I had to select ColumnA from TableA based on union condition below.

Select ColumnA from TableA where ColumnB = 'x'
Union
Select ColumnA from TableA where ColumnB = 'y'

Output:

ColumnA  
123,
456,
789,
543

How to write sql query where I can insert ColumnA from the unionQuery above and ColumnB, ColumnC , ColumnD should be hardcoded (Eg: a, b, c)

Expected Output:

ColumnA , ColumnB, ColumnC , ColumnD
123, a, b, c
456, a, b, c
789, a, b, c
543, a, b, c

Upvotes: 1

Views: 73

Answers (3)

ScaisEdge
ScaisEdge

Reputation: 133360

Just use literal value and alias 

  Select ColumnA, 'b' as columnB, 'c' as ColumnC, 'd' as ColumnD 
   From TableA where ColumnB = 'x'
  Union
  Select ColumnA, 'b', 'c', 'd' ColumnD 
  from TableA where ColumnB = 'y'

and for insert 

  insert into TableA (columnA, columnB, columnC, columnD) 
  Select ColumnA, 'b' as columnB, 'c' as ColumnC, 'd' as ColumnD 
  From TableA where ColumnB = 'x'
  Union
  Select ColumnA, 'b', 'c', 'd' ColumnD
  from TableA where ColumnB = 'y'

Upvotes: 1

Steven Lemmens
Steven Lemmens

Reputation: 1491

I think what you're looking for is this:

INSERT INTO TableA (ColumnName1, ColumnName 2, ColumnName3, ... )
SELECT ColumnA, 'a', 'b', 'c'
FROM TableA WHERE ...
UNION 
Select ColumnB? 'a', 'b', 'c' ...
FROM TABLEB WHERE ....

So basically just write your select query as you already have to output everything you need and then put the INSERT INTO above it with the correct column names.

Upvotes: 1

jarlh
jarlh

Reputation: 44756

Do you want to INSERT those rows into another table? If yes:

insert into newtable (ColumnA , ColumnB, ColumnC , ColumnD)
select distinct ColumnA, 'a', 'b', 'c'  FROM TableA

Or do you just want to SELECT those values?

select distinct ColumnA, 'a' ColumnB, 'b' ColumnC, 'c' ColumnD
FROM TableA

Upvotes: 0

Related Questions