Error message on INTO keyword
Does anyone know why the INTO keyword works in the first method below, but not the second?
I got both methods from the first 5 mins of this video about creating temporary tables: https://www.youtube.com/watch?v=3ZtYrELHP8M
Method 1
SELECT
CAST (date AS DATE) AS #DateWk
INTO
#DateWk
FROM
AggregatedSalesHistory
WHERE
date >= '1-2-2014'
SELECT * FROM #DateWk
When I try method 2, I get the error message below:
Incorrect syntax near the keyword 'INTO'.
This is the code for method 2:
CREATE TABLE #DateWeek
(
Title VARCHAR(MAX),
ReleaseDate DATETIME
)
INSERT INTO #DateWk
SELECT
CAST (date AS DATE) AS #DateWk,
INTO #DateWk
FROM AggregatedSalesHistory
WHERE date >= '1-2-2014'
SELECT * FROM #DateWk
2 Answers
2
This is not a valid syntax if you are going to compare with method1 :
INSERT INTO #DateWk
SELECT
CAST (date as Date) as #DateWk,
INTO #DateWk
FROM AggregatedSalesHistory
WHERE date >= '1-2-2014'
Whenever you are do :
SELECT CAST (date as Date) as #DateWk INTO #DateWk
FROM AggregatedSalesHistory
This would auto created #DateWk table & insert the date as well. So, SQL Server will not compile your second method which is combo of method or will throw error.
#DateWk
SQL Server
If the table already exists, you cannot use SELECT .... INTO .... anymore - you need to use:
SELECT .... INTO ....
INSERT INTO #DateWk
SELECT CAST (date AS DATE)
FROM AggregatedSalesHistory
WHERE date >= '1-2-2014'
You're already specifying the INSERT INTO at the beginning - do not add an INTO .. after the SELECT and then it should work just fine.
INSERT INTO
INTO ..
SELECT
So if you do
SELECT (list of columns)
INTO SomeTable
FROM ....
WHERE.....
then this SELECT will automatically create the SomeTable table - but this code will fail if SomeTable already exists - in that case use:
SELECT
SomeTable
SomeTable
INSERT INTO SomeTable(list-of-columns)
SELECT (list of columns)
FROM ....
WHERE.....
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
