SELECT INTO: How to Use with WITH command
Hi,
I'm trying to create a table that stores deduplicated rows using the following syntax. The WITH statement works fine but as soon as I add it to a select into it falls over. I did start by using the SORT object in SSIS but this took hours where as using the row_number function took minutes. Just can't get the data into a table. What am I missing?
Thanks
Marcus
select
*into dedupe_temp FROMWITH DedupedEmailAS
(
SELECT custid, email,ROW_NUMBER
()OVER(partitionby emailORDERBY custid)as RowNumberFROM
dbo.customer_info)SELECT
*FROM
DedupedEmailWHERE
RowNumber=1order
by custid
[2258 byte] By [
m14cus] at [2007-12-16]
Where are you executing this (e.g. SSIS, Query Analyser)? It sounds like a T-SQL issue to me.
Just as a complete stab in the dark....if you're issuing the query from SSIS then check what actually gets issues against SQL Server usnig Profiler...maybe the OLE DB Provider changes it on the way!
-Jamie
Though I can't find articles on MS website to point you at, my understanding is that a Common Table Expression (WITH table) is destroyed after the first statement the uses it as a source. In your case the Select * INTO would cause the CTE to be destroyed before the SELECT * from DedupedEmail would run.
That is, if you wish to use the data in the WITH table more than once, you'll need to place it into a (temporary) table (which you are already doing) or use a cursor.
For your example above, you could just change your 2nd statements FROM to the temporary table:
select * into dedupe_temp FROM WITH DedupedEmail AS
(
SELECT custid, email, ROW_NUMBER
() OVER (partition by email ORDER BY custid)as RowNumber FROM
dbo.customer_info ) SELECT
* FROM
dedupe_temp WHERE
RowNumber =1 order
by custid 