Why SQL Server Row Constructor not working with select query

0 votes
asked Jun 13, 2013 in SQL Server by Yadev

I know we can write insert query with collection of rows to insert with in a single query like below.

Insert Into Employees Value
(1, "Robin", 28),
(2, "Albert", 32),
(3, "Sunil", 44),
(3, "Peter", 37)

Consider I have another table called Managers where I am inserting some entries also, I am trying to read value from Employees table to insert during that bulk insert. But I SQL Server does not allow me to do that. Why?

Sample query

INSERT INTO Managers VALUES
("Ajay", 43),
(SELECT name, age FROM Employees WHERE age > 35),
("Yadev", 52)
Share

1 Answer

0 votes
answered Jun 14, 2013 by Aadhira (1,213 points)
selected Jun 18, 2013 by administrator
 
Best answer

Thats because Row Constructor will accept only scalar values. You can do the below but it may not be that valuable.

INSERT INTO Managers VALUES
("Ajay", 43),
((SELECT name FROM Employees WHERE Id = 3), (SELECT age FROM Employees WHERE Id = 3)),
((SELECT name FROM Employees WHERE Id = 4), (SELECT age FROM Employees WHERE Id = 4)), 
("Yadev", 52)

Your answer

Preview

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
Anti-spam verification:
To avoid this verification in future, please log in or register.
site design / logo / content © 2013 - 2015 pinfaq.com
...