subquery and multi-row insert trouble

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

subquery and multi-row insert trouble

Author

Topic

cupeet987
Starting Member

11 Posts

Posted - 2008-01-08 : 13:09:30

I browser a lot about multi-row inserts and I only find stuff like:


INSERT INTO MyTable  (FirstCol, SecondCol)
SELECT  'First' , 1
UNION ALL
SELECT  'Second' , 2
UNION ALL
SELECT  'Third' , 3

It doesn't solve my problem if I have to insert some 1000 rows selecting data from a function [getdate()], static ['string'] and also from another table. In this case I would have to write 1000 lines - impactical. I tried the following but it doesn't seem to work:


INSERT INTO MyTable3
	([column_1]
	,[column_2]
	,[column_3])
	SELECT 
		(SELECT Table2.Col6  -- there are many values in this subquery.
		 FROM Table2, Table1 --  I get the error "Subquery returned
		 WHERE (Table2.Col8 = Table1.Col7)) -- more than 1 value."
	,'I love you people from SQL Team.com'
	,getdate()
	FROM usuario, usuario_disciplina

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-08 : 13:27:42

Is this what you are looking for?


create table #temp (rowid int, dt datetime);

with cte (rowid) as
(
select top 10
row_Number() over (order by id)
from	sysobjects
)
insert #temp (rowid, dt)
select	rowid
		,getdate()
from cte

select * from #temp

drop table #temp

output:
rowid       dt
----------- -----------------------
1           2008-01-08 13:26:30.690
2           2008-01-08 13:26:30.690
3           2008-01-08 13:26:30.690
4           2008-01-08 13:26:30.690
5           2008-01-08 13:26:30.690
6           2008-01-08 13:26:30.690
7           2008-01-08 13:26:30.690
8           2008-01-08 13:26:30.690
9           2008-01-08 13:26:30.690
10          2008-01-08 13:26:30.690

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources