Menu
I am using SQL Server 2008. For getting some rows I am using a CTE in my stored procedure.
;WITH
CTE AS (
SELECT BrokerId ,
RankId ,
BrokerName ,
RankName ,
BrokerCode ,
IntroducerCode ,
CscName ,
MAX(SIP) AS SIP ,
MAX(Fresh) AS Fresh ,
MAX(FY) AS FY ,
MAX(SY) AS SY ,
MAX(TY) AS TY ,
CscId ,
Promotive ,
NoOfPromotive ,
PlanTypeName ,
PlanYear
FROM @tmp
GROUP BY BrokerId ,
RankId ,
BrokerName ,
RankName ,
BrokerCode ,
IntroducerCode ,
CscName ,
CscId ,
Promotive ,
NoOfPromotive ,
PlanTypeName ,
PlanYear
)
SELECT BrokerId ,
RankId ,
BrokerName ,
RankName ,
BrokerCode ,
IntroducerCode ,
CscName ,
SUM(SIP) AS 'SIP' ,
SUM(Fresh) AS 'Fresh' ,
SUM(FY) AS 'FY' ,
SUM(SY) AS 'SY' ,
SUM(TY) AS 'TY' ,
Promotive ,
Total = ISNULL(( SUM(SIP) ), 0) + ISNULL(( SUM(Fresh) ), 0)
+ ISNULL(( SUM(FY) ), 0) + ISNULL(( SUM(SY) ), 0)
+ ISNULL(( SUM(TY) ), 0) ,
NoOfPromotive ,
PlanTypeName ,
PlanYear ,
CscId
FROM CTE
GROUP BY BrokerId ,
RankId ,
BrokerName ,
RankName ,
BrokerCode ,
IntroducerCode ,
CscName ,
Promotive ,
NoOfPromotive ,
PlanTypeName ,
PlanYear ,
CscId
ORDER BY PlanTypeName
It gives me correct data. Now I want to Insert that data into a table. I have tried like :
INSERT INTO MyTable
( BrokerId ,
RankId ,
BrokerName ,
RankName ,
BrokerCode ,
IntroducerCode ,
CscName ,
SIP ,
Fresh ,
FY ,
SY ,
TY ,
Promotive ,
Total ,
NoOfPromotive ,
PlanTypeName ,
PlanYear ,
CscId
)
( SELECT BrokerId ,
RankId ,
BrokerName ,
RankName ,
BrokerCode ,
IntroducerCode ,
CscName ,
SUM(SIP) AS 'SIP' ,
SUM(Fresh) AS 'Fresh' ,
SUM(FY) AS 'FY' ,
SUM(SY) AS 'SY' ,
SUM(TY) AS 'TY' ,
Promotive ,
Total = ISNULL(( SUM(SIP) ), 0) + ISNULL(( SUM(Fresh) ), 0)
+ ISNULL(( SUM(FY) ), 0) + ISNULL(( SUM(SY) ), 0)
+ ISNULL(( SUM(TY) ), 0) ,
NoOfPromotive ,
PlanTypeName ,
PlanYear ,
CscId
FROM CTE
GROUP BY BrokerId ,
RankId ,
BrokerName ,
RankName ,
BrokerCode ,
IntroducerCode ,
CscName ,
Promotive ,
NoOfPromotive ,
PlanTypeName ,
PlanYear ,
CscId
)
But It gives me error. How can I Insert record in table? Thanks.
458
You can also use CTE to insert data into the SQL table. The CTE query definition includes the required data that you can fetch from existing tables using joins. Later, query CTE for inserting data into the target table.
You can use a common table expression (CTE) to simplify creating a view or table, selecting data, or inserting data. Use a CTE to create a table based on another table that you select using the CREATE TABLE AS SELECT (CTAS) clause.
If your CTE is based on a single table then you can update using CTE, which in turn updates the underlying table.
Try this one -
;WITH CTE AS
(
SELECT ...
FROM @tmp
)
INSERT INTO dbo.tbl (....)
SELECT ..
FROM CTE
GROUP BY ...
ORDER BY ...
NOTE: This has been answered correctly for SQL-Server. But if you've stumbled upon this post, looking for the same answer but you're using some other DBMS (namely SYBASE, ORACLE, or maybe others), this won't work. You cannot use the INSERT statement immediately after the CTE. In these cases, try putting the insert statement first:
INSERT INTO someTable (Col1,Col2,Col3)
WITH CTE AS (
SELECT someColA,
someColB,
someColC
FROM anotherTable
)
SELECT someColA,
someColB,
someColC
FROM CTE
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
