Union All

Question

Hi,

can we use union all in insert statements in sql server query

like

insert into table values(1,5)

union all 

values(2,2)

union all

values(3,3)

 Like this is there any way to insert rows

Thanks,

Answer 1

Hello,

Yes you can and you have two ways to do so:

CREATE TABLE #test (id1 int, id2 int);
GO

-- 1: With Select + UNION ALL
insert into #test (id1, id2)
SELECT 1 as id1, 5 as id2
union all 
SELECT 2, 2
union all
SELECT 3,3;
Go

-- 2: With Multi Value
insert into #test (id1, id2)
values(1,5) ,
      (2,2),
      (3,3);
GO

SELECT * FROM #test;

GO
DROP TABLE #test;

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

Hello - Yes certainly possible, try these options:

-- First Option, selecting fixed values
INSERT INTO t21
SELECT 21
UNION ALL
SELECT 22

-- Second Option, selecting fixed values
INSERT INTO t21
SELECT * FROM
( SELECT 21 AS Val1
UNION ALL
SELECT 22 AS Val1 ) A

-- Third Option, selecting from another table
INSERT INTO t21
SELECT * FROM
( SELECT ID FROM Table1
UNION ALL
SELECT ID FROM Table2 ) A

Hope this helps

---

Good Luck!
Please Mark This As Answer if it solved your issue.
Please Vote This As Helpful if it helps to solve your issue

Answer 3

if you want to insert static values you don't even need the union all

This works also

insert into mytable values(1,5),(2,2),(3,3)

Answer 4

This UNION ALL syntax an old Sybase legacy that will not port or be readable to anyone with SQL experience.

INSERT INTO Foobar 
VALUES (1,5), (2,2),(3,3);

The ANSI/ISO Standard VALUES() clause is called a row constructor and you will find in any SQL book or Google it.

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 5

Hi chandu999,

Regarding VALUES operator, in addition to all above, there's a new feature introduced since SQL Server 2008. You can specify multiple values as a derived table in a FROM clause with VALUES. See more details from Table Value Constructor.

SELECT *
FROM ( 
VALUES(1,5),(2,2)
) v(m,n)

SELECT * FROM
(VALUES(1,5)) v(m,n)
UNION ALL
SELECT * FROM
(VALUES(2,2)) v2(m,n) 

If you have any question, feel free to let me know.

---

Eric Zhang
TechNet Community Support