question

Binod avatar image
Binod asked

Resultset from CTE to Table

How can I pull the resultset of CTE into a table? I am trying to include: IF OBJECT_ID('dbo.Scores', 'U') IS NOT NULL DROP TABLE dbo.Scores; in my cte, but unfortunately it is throwing error. Below is the code. ;WITH DataSource AS ( SELECT DISTINCT Silver ,[copper] ,[Nickel] FROM @TABLE ) IF OBJECT_ID('dbo.Scores', 'U') IS NOT NULL DROP TABLE dbo.Scores; SELECT * into Scores FROM DataSource DS CROSS APPLY ( SELECT STUFF ( ( SELECT DISTINCT ';' +[Params] FROM @TABLE T WHERE T.Silver = DS.Silver AND T.[copper] = DS.[copper] AND T.[Nickel] = DS.[Nickel] AND [Params] <> '' FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1 ,1 ,'' ) ) DS1([Params]) CROSS APPLY ( SELECT STUFF ( ( SELECT DISTINCT ';' +[Note] FROM @TABLE T WHERE T.Silver = DS.Silver AND T.[copper] = DS.[copper] AND T.[Nickel] = DS.[Nickel] FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1 ,1 ,'' ) ) DS2 ([Note]) Appreciate your valuable help. Thanks a ton..........
sql-server-2008t-sqlcte
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
GPO avatar image
GPO answered
Move your test for the existence of the dbo.scores to just prior to the CTE.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Binod avatar image Binod commented ·
:)..................
2 Likes 2 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.