Select a temp table wrong - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select a temp table wrong

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-08-12 : 15:54:16

Thanks

DECLARE @Data TABLE
	(
		PLU_ID VARCHAR(24),
		RTL_PRC money
	)

		INSERT @Data
	(
		PLU_ID,RTL_PRC
	)
		SELECT PLU_ID,RTL_PRC
	        FROM PLU

SELECT * FROM @Data

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-12 : 15:56:29

What is your question?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-08-12 : 15:59:37

Get an error

Must declare the table variable "@Data".

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-12 : 16:02:33

There is nothing wrong with the code that you posted. Are you sure you've posted the whole thing?

What does SELECT @@VERSION show?
What compatibility level is your database using?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-08-12 : 16:09:58

SQL SERVER 2005
I want to create a temp table.

CREATE TABLE @Data(PLU_ID VARCHAR(24),RTL_PRC money)
INSERT INTO @Data(PLU_ID,RTL_PRC)
SELECT PLU_ID,RTL_PRC FROM PLU
SELECT * FROM @Data

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@Data'.
Msg 1087, Level 15, State 2, Line 2
Must declare the table variable "@Data".
Msg 1087, Level 15, State 2, Line 4
Must declare the table variable "@Data".

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-12 : 16:11:31

You can't create a table variable with the CREATE TABLE statement. You must use DECLARE like in your first post.

You could instead use a temp table (you keep referring to your issue as a temp table, however you are using table variables) if you must use CREATE TABLE. If that's what you want, then just switch the @ signs to # signs.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-08-12 : 16:18:24

Okay.
Should I have drop it if completing my task?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-12 : 16:20:53

You can't drop table variables.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 16:24:56

This is a continunation from here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=108660

E 12°55'05.25"
N 56°04'39.16"

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-08-12 : 16:31:28

You mean every time I have to declare it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-12 : 16:33:20

If the table variable doesn't exist in your session, then yes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-08-12 : 16:48:46

[code]DECLARE @Data TABLE(PLU_ID VARCHAR(24),RTL_PRC money,REUSE_FLG CHAR(1))
INSERT INTO @Data(PLU_ID,RTL_PRC,REUSE_FLG)
SELECT PLU_ID,RTL_PRC,'N' FROM PLU
SELECT *FROM @Data

UPDATE @Data
SET
REUSE_FLG=(SELECT PKG_GRP_REUSE_FLG FROM PKGTSKP
WHERE SKU_PKG_ID=15007)
FROM @Data
INNER JOIN PLU U
ON(@Data.PLU_ID=U.PLU_ID)
WHERE U.PLU_ID=1000008
SELECT *FROM @Data WHERE PLU_ID=1000008[/code]
So what is wrong here?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-12 : 16:54:13

UPDATE d
SET
REUSE_FLG=(SELECT PKG_GRP_REUSE_FLG FROM PKGTSKP
WHERE SKU_PKG_ID=15007)
FROM @Data d
INNER JOIN PLU U
ON d.PLU_ID=U.PLU_ID
WHERE U.PLU_ID=1000008

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-08-12 : 16:59:40

Thank you so much.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 17:05:22

[code]UPDATE d
SET d.REUSE_FLG = x.PKG_GRP_REUSE_FLG
FROM @Data AS d
INNER JOIN PLU AS u ON u.PLU_ID = d.PLU_ID
LEFT JOIN PKGTSKP AS x ON x.SKU_PKG_ID = 15007
WHERE u.PLU_ID = 1000008[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources