Create a temp table but got an error

Question

Create a temp table but got an error

adonetok

SSCrazy Eights

Points: 8395
More actions
May 27, 2014 at 7:14 am

#286957

I try to modify a code to create a temp table but got an error.
How to fix it?
------------------------------
Declare @headerid Int
Declare @Providerid Int
BELOW CODE IS WORKING:
------------------------------
SELECT @headerid=HEADERID,@Providerid=PROVIDERID
FROM CLAIM WITH(NOLOCK) WHERE EDI_CLM_NUM= 123
BELOW CODE IS NOT WORKING:
------------------------------
SELECT * INTO #HEADER FROM (
SELECT @headerid=HEADERID,@Providerid=PROVIDERID
FROM CLAIM WITH(NOLOCK) WHERE EDI_CLM_NUM= 123) AS P
Error:
Incorrect syntax near '='.
Incorrect syntax near the keyword 'with'.
If this statement is a common table expression,
an xmlnamespaces clause or a change tracking context clause,
the previous statement must be terminated with a semicolon.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

adonetok (5/27/2014)
I try to modify a code to create a temp table but got an error.
How to fix it?
------------------------------
Declare @headerid Int
Declare @Providerid Int
BELOW CODE IS WORKING:
------------------------------
SELECT @headerid=HEADERID,@Providerid=PROVIDERID
FROM CLAIM WITH(NOLOCK) WHERE EDI_CLM_NUM= 123
BELOW CODE IS NOT WORKING:
------------------------------
SELECT * INTO #HEADER FROM (
SELECT @headerid=HEADERID,@Providerid=PROVIDERID
FROM CLAIM WITH(NOLOCK) WHERE EDI_CLM_NUM= 123) AS P
Error:
Incorrect syntax near '='.
Incorrect syntax near the keyword 'with'.
If this statement is a common table expression,
an xmlnamespaces clause or a change tracking context clause,
the previous statement must be terminated with a semicolon.

What are you actually trying to do here? You have a couple of variables but then you are trying assign them values and insert into a table at the same time? If I can understand this you have a CLAIM table with two columns that you want to insert into a temp table?

SELECT HEADERID, PROVIDERID

into #Header

FROM CLAIM

WHERE EDI_CLM_NUM = 123

Since your where clause has what looks like it might be returning a single row, why use a temp table? You will get far better performance if you just set your variables and skip the temp table. This is what you did in the code you say is working.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

adonetok (5/27/2014)
I try to modify a code to create a temp table but got an error.
How to fix it?
------------------------------
Declare @headerid Int
Declare @Providerid Int
BELOW CODE IS WORKING:
------------------------------
SELECT @headerid=HEADERID,@Providerid=PROVIDERID
FROM CLAIM WITH(NOLOCK) WHERE EDI_CLM_NUM= 123
BELOW CODE IS NOT WORKING:
------------------------------
SELECT * INTO #HEADER FROM (
SELECT @headerid=HEADERID,@Providerid=PROVIDERID
FROM CLAIM WITH(NOLOCK) WHERE EDI_CLM_NUM= 123) AS P
Error:
Incorrect syntax near '='.
Incorrect syntax near the keyword 'with'.
If this statement is a common table expression,
an xmlnamespaces clause or a change tracking context clause,
the previous statement must be terminated with a semicolon.

Here is a way to fix that:

SELECT HEADERID, PROVIDERID INTO #HEADER

FROM CLAIM WITH WHERE EDI_CLM_NUM = 123

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Looks like Sean beat me on this one. I blame 3rd world internet out here in Afghanistan.

adonetok SSCrazy Eights Points: 8395 More actions · Answer 4

This a part of one sp in which there are about 20 select statements.

Of all select statement, they need @headerid and @Providerid value.

If using

SELECT HEADERID, PROVIDERID INTO #HEADER

FROM CLAIM WITH WHERE EDI_CLM_NUM = 123

How to assign value to @headerid and @Providerid?

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

adonetok (5/27/2014)
This a part of one sp in which there are about 20 select statements.
Of all select statement, they need @headerid and @Providerid value.
If using
SELECT HEADERID, PROVIDERID INTO #HEADER
FROM CLAIM WITH WHERE EDI_CLM_NUM = 123
How to assign value to @headerid and @Providerid?

You posted that in your original post.

Declare @headerid Int
Declare @Providerid Int
BELOW CODE IS WORKING:
------------------------------
SELECT @headerid=HEADERID,@Providerid=PROVIDERID
FROM CLAIM WITH(NOLOCK) WHERE EDI_CLM_NUM= 123

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/