SELECT subquery into temporary table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 Administration

SELECT subquery into temporary table?

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2014-05-21 : 18:29:54

I have a working query which does what I want, but it's painfully slow because it runs the same subquery several times. Instead, I wanted to insert the results into a temporary table & then reference that table. The problem is that I keep getting errors.

Basically, my UPCs are either in a field called UPC or a field called Alias in a different table. I do two queries, join them, find the ones which appear more than once & output that to a resulting table. I have tried this, but the resulting error message is below.

Is it that my syntax is incorrect or is it just not possible to insert to a virtual table from a subquery?

Declare @tmpdb Table(
	UPC nvarchar(100)
	)

SELECT 	UPC
INTO @tmpdb
FROM (
	SELECT 
		ppa.ProductID					AS 'SKU'
		,LTRIM(RTRIM([UPC]))			AS 'UPC'
	FROM [SC].[dbo].[bvc_Product_Properties_Amazon] ppa
	WHERE UPC IS NOT NULL
	AND LEN(UPC)>0
	UNION ALL
	SELECT 
		pa.ProductID				AS 'SKU'
		,LTRIM(RTRIM([Alias]))		AS 'UPC'
	FROM [SC].[dbo].[bvc_Product_Alias] pa
	) t
GROUP BY (UPC)
HAVING COUNT(UPC)>1

quote:
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '@tmpdb'.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near 't'.

-Sergio
I use Microsoft SQL 2008

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-21 : 18:39:21

Specify INSERT INTO, not SELECT INTO.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources