Select Into query - 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 query

Author

Topic

mshsilver
Posting Yak Master

112 Posts

Posted - 2011-11-14 : 06:09:43

Hi,
I have a query below that takes values from column headings and where a value of true is found in a field it concatinates the names of the headings and places them into a virtual table. I am now trying to get those reults into a new physical table that I have created but using SELECT INTO I can’t get it working. I think it’s because of the complex SELECT query but maybe someone can tell me from my examples where I am going wrong.

This first code creates table1 and table2, table1 is being populated with some example data and table2 is empty ready for the next block of code that manipulates table1. table2 is where I am having issues, I can’t get the next block of code to take the data from table1 and insert it into table2


CREATE TABLE TABLE1 (ID INT, GroupA VARCHAR(10), GroupB VARCHAR(10), GroupC VARCHAR(10))
INSERT INTO TABLE1
SELECT 1, 'True', 'False', 'True'
UNION ALL SELECT 2, 'False', 'False', 'True'
UNION ALL SELECT 3, 'True', 'True', 'True'

CREATE TABLE [dbo].[table2](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[result] [varchar](50) NULL
) ON [PRIMARY]

GO

Complex select query that retrieves data from table1 in the format I need ready to insert the results into table2 column, “result”


SELECT RTRIM(COALESCE(CASE WHEN GroupA = 'True'                    
				THEN 'GroupA;' 
				ELSE NULL END + ' ', '') +
	  COALESCE(CASE WHEN GroupB = 'True'              
				THEN 'GroupB;'              
				ELSE NULL END + ' ', '') +
	  COALESCE(CASE WHEN GroupC = 'True'              
				THEN 'GroupC;'              
				ELSE NULL END + ' ', ''))
				FROM TABLE1

Here is my failed attempt to build the select into query with the above select statement. I’m sure anyone experienced will look at my code and tell me what I am doing wrong…

I’m not very experienced with advanced SQL queries so this one is over my head.

Thanks for looking.


SELECT RTRIM(COALESCE(CASE WHEN GroupA = 'True'                    
				THEN 'GroupA;' 
				ELSE NULL END + ' ', '') +
	  COALESCE(CASE WHEN GroupB = 'True'              
				THEN 'GroupB;'              
				ELSE NULL END + ' ', '') +
	  COALESCE(CASE WHEN GroupC = 'True'              
				THEN 'GroupC;'              
				ELSE NULL END + ' ', ''))
INTO table2 result
				FROM TABLE1

Error
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near 'result'.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-11-14 : 06:18:41

you can't alias a into table name:

SELECT RTRIM(COALESCE(CASE WHEN GroupA = 'True'
THEN 'GroupA;'
ELSE NULL END + ' ', '') +
COALESCE(CASE WHEN GroupB = 'True'
THEN 'GroupB;'
ELSE NULL END + ' ', '') +
COALESCE(CASE WHEN GroupC = 'True'
THEN 'GroupC;'
ELSE NULL END + ' ', ''))
INTO table2
FROM TABLE1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-14 : 06:25:30

it should be


SELECT RTRIM(COALESCE(CASE WHEN GroupA = 'True'                    
				THEN 'GroupA;' 
				ELSE NULL END + ' ', '') +
	  COALESCE(CASE WHEN GroupB = 'True'              
				THEN 'GroupB;'              
				ELSE NULL END + ' ', '') +
	  COALESCE(CASE WHEN GroupC = 'True'              
				THEN 'GroupC;'              
				ELSE NULL END + ' ', '')) AS result
INTO table2 
				FROM TABLE1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-14 : 06:27:11

quote:
Originally posted by RickD

you can't alias a into table name:

SELECT RTRIM(COALESCE(CASE WHEN GroupA = 'True'
THEN 'GroupA;'
ELSE NULL END + ' ', '') +
COALESCE(CASE WHEN GroupB = 'True'
THEN 'GroupB;'
ELSE NULL END + ' ', '') +
COALESCE(CASE WHEN GroupC = 'True'
THEN 'GroupC;'
ELSE NULL END + ' ', ''))
INTO table2
FROM TABLE1

hmm? why not?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mshsilver
Posting Yak Master

112 Posts

Posted - 2011-11-14 : 06:40:25

Thanks for the replies.

What would my options be then? Could really do with some helpful advise on this one.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-14 : 06:55:05

quote:
Originally posted by mshsilver

Thanks for the replies.

What would my options be then? Could really do with some helpful advise on this one.

what? i gave you the option right?
what else you're expecting?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mshsilver
Posting Yak Master

112 Posts

Posted - 2011-11-14 : 07:16:00

Sorry i thought you gaev an example then under you said you can't alias into table name. I have tested it and it works fine thank you, I see the As result is the fixing part to the solution.

Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-14 : 07:20:48

quote:
Originally posted by mshsilver

Sorry i thought you gaev an example then under you said you can't alias into table name. I have tested it and it works fine thank you, I see the As result is the fixing part to the solution.

Thanks again.

yep exactly
that was my point

sorry I didnt sound clear in my suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mshsilver
Posting Yak Master

112 Posts

Posted - 2011-11-14 : 10:39:20

I have another question on this topic. If table2 does not exist then the query creates it and populates the table fine but the scenario i am in is not going to allow me to create the table, it already exists, is there a way around this error?

Error:
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'table2' in the database.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-14 : 10:47:56

yep there's


IF OBJECT_ID('table2') IS NOT NULL
 DROP TABLE table2

SELECT RTRIM(COALESCE(CASE WHEN GroupA = 'True' 
THEN 'GroupA;' 
ELSE NULL END + ' ', '') +
COALESCE(CASE WHEN GroupB = 'True' 
THEN 'GroupB;' 
ELSE NULL END + ' ', '') +
COALESCE(CASE WHEN GroupC = 'True' 
THEN 'GroupC;' 
ELSE NULL END + ' ', '')) 
INTO table2 
FROM TABLE1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mshsilver
Posting Yak Master

112 Posts

Posted - 2011-11-14 : 10:53:26

Thanks :-) You have been a great help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-14 : 11:29:50

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-11-14 : 11:33:42

quote:
Originally posted by visakh16

quote:
Originally posted by RickD

you can't alias a into table name:

SELECT RTRIM(COALESCE(CASE WHEN GroupA = 'True'
THEN 'GroupA;'
ELSE NULL END + ' ', '') +
COALESCE(CASE WHEN GroupB = 'True'
THEN 'GroupB;'
ELSE NULL END + ' ', '') +
COALESCE(CASE WHEN GroupC = 'True'
THEN 'GroupC;'
ELSE NULL END + ' ', ''))
INTO table2
FROM TABLE1

hmm? why not?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Well, look at the original post and you will see what I mean. It has "INTO table2 result", this can not be aliased. The field can of course, but not that table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-14 : 11:53:42

quote:
Originally posted by RickD

quote:
Originally posted by visakh16

quote:
Originally posted by RickD

you can't alias a into table name:

SELECT RTRIM(COALESCE(CASE WHEN GroupA = 'True'
THEN 'GroupA;'
ELSE NULL END + ' ', '') +
COALESCE(CASE WHEN GroupB = 'True'
THEN 'GroupB;'
ELSE NULL END + ' ', '') +
COALESCE(CASE WHEN GroupC = 'True'
THEN 'GroupC;'
ELSE NULL END + ' ', ''))
INTO table2
FROM TABLE1

hmm? why not?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Well, look at the original post and you will see what I mean. It has "INTO table2 result", this can not be aliased. The field can of course, but not that table.

OP meant column aliasing which was obvious as it was a derived column in the select

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources