SELECT INTO - 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

General SQL Server Forums

New to SQL Server Programming

SELECT INTO

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-06-28 : 12:26:03

Hi,

i wanna get a data from a select statement and i wanna know if i can get it inside a variable like statement below:

SELECT column1 INTO :@var1 FROM table1

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-06-28 : 12:30:01

You can initialise a variable with a value from a single row:

SELECT  @var1 = column1 
FROM    table1 
WHERE   column2 = 'whatever'

Mark

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-28 : 12:35:08

quote:
Originally posted by raysefo

Hi,

i wanna get a data from a select statement and i wanna know if i can get it inside a variable like statement below:

SELECT column1 INTO :@var1 FROM table1

Or declare the table variable matching the source table like this.

DECLARE @var1 TABLE (...column definitions here...)

INSERT @var1 SELECT ...columns here... FROM Table1

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2006-06-28 : 12:51:03

quote:
Originally posted by mwjdavidson

You can initialise a variable with a value from a single row:
SELECT @var1 = column1
FROM table1
WHERE column2 = 'whatever'

Mark

Better make sure it's a singleton SELECT. If you don't you will only get the last value in the result set.

And here all along I thought this was a bug...only to find out that they call it an undocumented "feature"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-29 : 01:28:30

>>Better make sure it's a singleton SELECT. If you don't you will only get the last value in the result set.

Yes. Actually if there are more than one value returned, it must throw error something like "Query Returns More than one value". As last returned value is assigned, it may lead to confusion if this feature is not known

Madhivanan

Failing to plan is Planning to fail

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-07-02 : 06:29:28

quote:
>>Better make sure it's a singleton SELECT. If you don't you will only get the last value in the result set.

And this is how to prevent that 'gotcha'

SET @var1 = (SELECT column1 
FROM    table1 
WHERE   column2 = 'whatever')

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-03 : 02:17:52

quote:
Originally posted by PSamsig

quote:
>>Better make sure it's a singleton SELECT. If you don't you will only get the last value in the result set.
And this is how to prevent that 'gotcha'
SET @var1 = (SELECT column1
FROM table1
WHERE column2 = 'whatever')

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

What happens if subquery returns more than one vale?

Madhivanan

Failing to plan is Planning to fail

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-07-03 : 02:49:43

You get the error you talked about.

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources