I cannot find the syntax error in the following statement:
CREATE TABLE dbo.statslogsummary as
(SELECT COUNT(logID) AS userid, logUserID,MAX(logDateTime)
FROM statsLog
GROUP BY logUserID);
Tells me "invalid syntax near AS"
Add a comment
|
2 Answers
A CREATE TABLE statement shouldn't have a SELECT statement in it. A CREATE TABLE statement should only be defining the table structure.
If you're trying to create a table by selecting data from another table, you need to use the Select Into syntax.
SELECT COUNT(logID) AS userid, logUserID,MAX(logDateTime) AS logDateTime
INTO dbo.statslogsummary
FROM statsLog
GROUP BY logUserID
3 Comments
David
You've done away with using the "CREATE TABLE" part of your statement, right? My entire code snippet will create the table without explicitly using the CREATE TABLE command. Also, check the link I included (the words "Select INTO") which documents how it works.
Yahia
@DavidStratton your statement won't work because the last column (MAX...) doesn't have an alias...
David
AHHH Thanks! Good eye! Voting you up since you caught that detail amd edited my code.
UPDATE - after is is clear that this is SQL Server:
SELECT COUNT(logID) AS userid, logUserID,MAX(logDateTime) AS maxlogtm
INTO dbo.statslogsummary
FROM statsLog
GROUP BY logUserID
1 Comment
Yahia
@user990016 you didn't say which DB you are on, the above works just fine on Oracle... as I see you added a tags saying it is SQL Server... for SQL Server use my updated answer above...