CODEWITHSUNDEEP
sqlunionteradata
John Henry
John Henry

Reputation: 165

Using Union with Insert to add one row

I have a query which is made up of two select statements with a union in the middle. This works for what I need it for. However, there is one value missing which I want to manually enter. What I'm looking to is:

select * from tab1
union
select * from tab2
union
insert values('John',cast('2013-01-01' as date), 'Jim', 130)

Unfortunately this is not working. Can someone suggest how I do this please? I'm using Teradata.

Upvotes: 2

Views: 5780

Answers (4)

Craabe
Craabe

Reputation: 1

I am affraid the union must reference a table and if you need to add the data that does not exist in your database, try this:

select * from tab1
union
select * from tab2
union
select * from (SELECT 'John' as col1 ,cast('2013-01-01' as date) as col2, 'Jim' as col3, '130' as col4) dummy

You will, of course, have to change the name of the columns to fit those from your db (i.e. dont use col1, col2 etc.).

Good luck!

Upvotes: 0

Hart CO
Hart CO

Reputation: 34774

You just want to SELECT the data, not INSERT it.

Not very familiar with TeraData, perhaps you need a FROM in which case limiting to 1 record would also make sense:

select * from tab1
union
select * from tab2
union
SELECT 'John',cast('2013-01-01' as date), 'Jim', '130' FROM dbc.columns 1

Upvotes: 1

Jonathan Leffler
Jonathan Leffler

Reputation: 753585

You need to keep selecting:

select * from tab1
union
select * from tab2
union
select 'John', cast('2013-01-01' as date), 'Jim', 130 from dual

The name dual is used in Oracle for a table with one row (and one column). Depending on the DBMS you use, you may be able to omit that final FROM altogether (and you may be able to do this in Oracle too):

select * from tab1
union
select * from tab2
union
select 'John', cast('2013-01-01' as date), 'Jim', 130

or you may have to choose from a system catalog table and ensure you get one row returned (FROM systables WHERE tabid = 1 was the classic mechanism in Informix, though you could also use 'sysmaster':sysdual instead of dual, etc, too), or you can select from any other table with a query that is guaranteed one row. There are probably ways to do it using a VALUES clause too.

Note the change from double quotes to single quotes. In strict standard SQL, double quotes enclose a delimited identifier, but single quotes surround strings.

Upvotes: 6

AndreKR
AndreKR

Reputation: 33678

From your question I' guessing you just want to SELECT that row, not INSERT it (into the database):

select * from tab1
union
select * from tab2
union
select "John", cast('2013-01-01' as date), "Jim", 130

Upvotes: 1

Related Questions