question

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..........