select * into table problem - SQL Server Forums

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)

select * into table problem

Author

Topic

mmdullah
Starting Member

10 Posts

Posted - 2009-06-09 : 04:40:15

dear all,
would you please run the following script in a single command.
it does not work. specially when to execute the select * into....
please help..

declare @retVal table(
[SLNo] int,
[Name] varchar(100))
select * into @retVal from(
select '1' as SLNO,'mir' as Name
union
select '2' as SLNO,'mamun' as Name)
select * from @retVal

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-09 : 04:43:25

I you are using a derived table you have to give an alias.
And select * into means the dest table should not already exist.
declare @retVal table(
[SLNo] int,
[Name] varchar(100))

insert @retval
select * from
(
select '1' as SLNO,'mir' as Name
union
select '2' as SLNO,'mamun' as Name
) as t

select * from @retVal

No, you're never too old to Yak'n'Roll if you're too young to die.

mmdullah
Starting Member

10 Posts

Posted - 2009-06-09 : 04:59:08

gr8!!! thank you.
Experts are always helpful....

mmdullah
Starting Member

10 Posts

Posted - 2009-06-09 : 05:15:34

Excuse Me!!!!!!!

but why doesn't this work:

declare @retVal table(
[SLNo] int,
[Name] varchar(100))

declare @qry varchar(400)
set @qry='insert @retVal select * from(
select ''1'' as SLNO,''mir'' as Name
union
select ''2'' as SLNO,''mamun'' as Name
) as T'
exec(@qry)

error message:
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@retVal".

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-09 : 05:18:29

The scope of dynamic query is out of range for the table variable.
WHY are you using dynamic sql at all?

E 12°55'05.63"
N 56°04'39.26"

mmdullah
Starting Member

10 Posts

Posted - 2009-06-09 : 05:30:58

is it really out of scope....
I have fixed it.

the code:

declare @retVal table(
[SLNo] int,
[Name] varchar(100))

declare @qry varchar(400)
set @qry=' select * from(
select ''1'' as SLNO,''mir'' as Name
union
select ''2'' as SLNO,''mamun'' as Name
) as T'
insert @retVal execute(@qry)
select * from @retVal

result:
1 mir
2 mamun

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-09 : 05:34:19

WHY are you using dynamic sql at all?

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources