Transact-Sql INTO Clause Syntax

Question

What am I doing wrong here? My use of the INTO clause seems to match the example I found in Microsoft's Transact-SQL reference pages, but I must be missing something.

DECLARE @rowtemp table(TestRunID int, RunOrder int)

SELECT TestRunID, ROW_NUMBER() OVER (ORDER BY TestRuns.TestTime ASC) 
AS 'RunOrder' INTO @rowtemp FROM TestRuns WHERE RunID = @runID

Answer 1

You can't use a table variable with a SELECT INTO. Use a # temp table instead.

Answer 2

You cannot use SELECT INTO with a table variable and also SELECT INTO would create a table so you would not create the table first

try this

DECLARE @rowtemp table(TestRunID int, RunOrder int)

INSERT INTO  @rowtemp
SELECT TestRunID, ROW_NUMBER() OVER (ORDER BY TestRuns.TestTime ASC) 
AS 'RunOrder'  FROM TestRuns WHERE  RunID = @runID

Answer 3

What Joe said. Plus, INTO creates a new table. Since this table already exists (you just created it) the statement fails.

Answer 4

Table variables cannot be used with SELECT INTO, you can use a #temp table or rearrange your statement slightly to read.

DECLARE @rowtemp table(TestRunID int, RunOrder int)

INSERT INTO @rowtemp 
 SELECT 
  TestRunID, 
  ROW_NUMBER() OVER (ORDER BY TestRuns.TestTime ASC) AS 'RunOrder' 
FROM 
 TestRuns 
WHERE 
 RunID = @runID