Select INTO error - 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 error

Author

Topic

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-12-19 : 15:13:24

What is wrong with this syntax?

drop z_TEST
SELECT COUNT(First_Name + Last_Name) as ss, First_Name, Last_Name
INTO z_TEST
FROM Z_Names
GROUP BY First_Name, Last_Name

I am getting tthis error:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'SELECT'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 15:39:22

[code]DROP TABLE z_TEST
GO

SELECT COUNT(*) AS ss,
First_Name,
Last_Name
INTO z_TEST
FROM Z_Names
GROUP BY First_Name,
Last_Name[/code]

E 12°55'05.25"
N 56°04'39.16"

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-12-19 : 15:48:33

Ahhh yess got it.

i was doing this because i want to find te AVG number of times a name appears. Is there a way to put the count SELECT in a AVG() function s i don't have to create a table then calculate the avg?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2007-12-19 : 16:00:55

Like this maybe:

SELECT AVG(ss)
FROM (
  SELECT 
    COUNT(*) AS ss,
    First_Name,
    Last_Name 
  FROM Z_Names 
  GROUP BY First_Name,
    Last_Name) AS dt

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-12-19 : 16:35:11

That's it. Thank you

I had tried that syntax. But I did not know i needed the AS clause on the end. SQL will not reconize the sub query with out an alias?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2007-12-19 : 16:54:56

Yep, you will need an alias as this is defined as a derived table and not a subquery.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-12-19 : 17:26:21

That is a new one on me. What is the difference between a subquery and a derived table? Could u show me an example of a sub query the compare with what the selectu showed me?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2007-12-20 : 02:50:35

A derived table is sort of an inline view created on the fly. I guess you can separate derived tables and subqueries in the sense that derived tables appear in the FROM clause and can be joined as a regular table with regular join syntax, subqueries are usually in the select- or where-part of a query.

Derived tables can be really powerful if you get the concept, have a look at this article and you'll get the idea. The article is somewhat old but it still applies:

http://www.sql-server-performance.com/articles/per/derived_temp_tables_p1.aspx

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-12-20 : 12:10:32

Your explination and the artical are good. Very informative. I learned alot. Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources