CODEWITHSUNDEEP
sqlsql-server-2008
user1292656
user1292656

Reputation: 2560

Error on SELECT * INTO statement

I want to transfer all data from one table into another with the following code:

INSERT INTO tblpremier 
   SELECT * INTO #TempTable
   FROM dbo.IntermediateTable

ALTER TABLE #TempTable
DROP COLUMN id

SELECT * FROM #TempTable

And I get an error

Incorrect syntax near the keyword 'INTO'

at the second line of this code. Any help?.

Upvotes: 0

Views: 1124

Answers (3)

Bridge
Bridge

Reputation: 30661

What you've written doesn't make any sense. The first "statement" is this:

INSERT INTO tblpremier 
SELECT * INTO #TempTable
FROM dbo.IntermediateTable

Now are you inserting into #temptable, or tblpremier? I'm guessing you wanted to perform all these operations and then insert into tblpremier - in which case split it into separate statements. I'm guessing you wanted to do:

SELECT * INTO #TempTable
FROM dbo.IntermediateTable

ALTER TABLE #TempTable
DROP COLUMN id

INSERT INTO tblpremier
SELECT * FROM #TempTable

But rather than need #Temptable which is the same as IntermediateTable minus the ID column, why not just select the correct columns you need from IntermediateTable in the first place rather than using *?

Edit:

Here's what I meant. Write the insert statement so you've got all the column names specified, and don't include the ID column. You'll get all new ID numbers on the copy of the table.

INSERT INTO tblpremier (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM IntermediateTable

However, if you wanted to to keep the same ID numbers in the copy of the table, and the columns are in the same order on both intermediate table and tblpremier, then you could write:

SET IDENTITY_INSERT tblpremier ON

INSERT INTO tblpremier
SELECT * 
FROM IntermediateTable

SET IDENTITY_INSERT tblpremier OFF

But you would still need to watch out for trying to insert duplicate IDs if tblpremier isn't empty at first.

Upvotes: 2

Vikdor
Vikdor

Reputation: 24134

You cannot use both INSERT INTO TABLE SELECT FROM and SELECT INTO at the same time. You should break that statement into 

INSERT INTO tblpremier 
SELECT * FROM dbo.IntermediateTable

SELECT * INTO #TempTable
FROM dbo.IntermediateTable

ALTER TABLE #TempTable
DROP COLUMN id

SELECT * FROM #TempTable

Upvotes: 1

SchmitzIT
SchmitzIT

Reputation: 9552

You have both a SELECT INTO and INSERT INTO in the same statement.

Upvotes: 0

Related Questions