CTE value in table

Transact-SQL
1

Respected Techie,
can anyone please help me
how can i store the cte result in a table. each time i run the script table recreated with fresh data.
i tried like this but this is giving error.

;WITH DataSource AS
(
-- columns on which we are grouping
SELECT DISTINCT
[BaseVehicleID]
,[ParttypeID]
,[SubModelID]
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.[BaseVehicleID] = DS.[BaseVehicleID]
AND T.[ParttypeID] = DS.[ParttypeID]
AND T.[SubModelID] = DS.[SubModelID]
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.[BaseVehicleID] = DS.[BaseVehicleID]
AND T.[ParttypeID] = DS.[ParttypeID]
AND T.[SubModelID] = DS.[SubModelID]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1
,1
,''
)
) DS2 ([Note])

Thanks.....

2

You should reorganize your query to make the CTE part of the rest of the query. A CTE cannot be a standalone query

IF OBJECT_ID('dbo.Scores', 'U') IS NOT NULL
DROP TABLE dbo.Scores;

;
WITH    DataSource
          AS (
			-- columns on which we are grouping
           SELECT DISTINCT
            [BaseVehicleID] ,
            [ParttypeID] ,
            [SubModelID]
           FROM
            @TABLE)
    SELECT
        *
    INTO
        Scores
    FROM
        DataSource DS
        CROSS APPLY ( SELECT
                        STUFF(
(SELECT DISTINCT
    ';' + [Params]
 FROM
    @TABLE T
 WHERE
    T.[BaseVehicleID] = DS.[BaseVehicleID]
    AND T.[ParttypeID] = DS.[ParttypeID]
    AND T.[SubModelID] = DS.[SubModelID]
    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.[BaseVehicleID] = DS.[BaseVehicleID]
    AND T.[ParttypeID] = DS.[ParttypeID]
    AND T.[SubModelID] = DS.[SubModelID]
                        FOR
                              XML PATH('') ,
                                  TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '') ) DS2 ( [Note] )
1 Like
3

Thanks James.