?
Solved

DML on CTE?

Posted on 2013-10-24
4
Medium Priority
?
536 Views
Last Modified: 2013-11-04
what DMLs can do inside a CTE resultset?
0
Comment
Question by:25112
  • 2
  • 2
4 Comments
 
LVL 5

Expert Comment

by:MohitPandit
ID: 39596811
Hello,

You can have
SELECT
statement inside of CTE parenthesis but after close CTE parenthesis then you can have
INSERT, DELETE, UPDATE, SELECT

For example,
USE AdventureWorks2012;
GO
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
        b.EndDate, 0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
        bom.EndDate, ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom 
        INNER JOIN Parts AS p
        ON bom.ProductAssemblyID = p.ComponentID
        AND bom.EndDate IS NULL
)
UPDATE Production.BillOfMaterials
SET PerAssemblyQty = c.PerAssemblyQty * 2
FROM Production.BillOfMaterials AS c
JOIN Parts AS d ON c.ProductAssemblyID = d.AssemblyID
WHERE d.ComponentLevel = 0; 

Open in new window


Kindly take a look over it and let me know in case you have any concern.

Best Regards,
Mohit Pandit
0
 
LVL 5

Author Comment

by:25112
ID: 39596944
thanks Mohit.. in your example, can you do INSERT, DELETE, UPDATE on Parts CTE?
0
 
LVL 5

Accepted Solution

by:
MohitPandit earned 2000 total points
ID: 39597000
Hello,

Please find below example with temp table data:

CREATE TABLE #T1 (Pid Int IDENTITY PRIMARY KEY, Name VarChar(50), Address VarChar(50))

INSERT INTO #T1(Name)
VALUES ('Ram'), ('Sham'), ('Sudhir')

-- Just fetch
WITH parts (id, name)
AS
(
	SELECT Pid, Name
	FROM #T1
)
SELECT * FROM parts;

-- Insert using CTE
WITH parts (id, name)
AS
(
	SELECT Pid, Name
	FROM #T1
)
INSERT INTO parts (Name)
VALUES ('Henri');

-- Update using CTE
WITH parts (id, name)
AS
(
	SELECT Pid, Name
	FROM #T1
)
UPDATE parts
SET name = 'Ramlal'
WHERE id = 1;

-- Delete using CTE
WITH parts (id, name)
AS
(
	SELECT Pid, Name
	FROM #T1
)
DELETE parts
WHERE id = 2

SELECT * FROM #T1;

-- Can create different table as well, here temp table
	-- on this #T2 temp table, you can do further calculation if want.
WITH parts (id, name)
AS
(
	SELECT Pid, Name
	FROM #T1
)
SELECT * INTO #T2 FROM parts

SELECT '#T2 Table', * FROM #T2

DROP TABLE #T1
DROP TABLE #T2

Open in new window


Kindly take a look over it and let me know in case you have any concern.

Best Regards
Mohit Pandit
0
 
LVL 5

Author Comment

by:25112
ID: 39622418
thank you Mohit
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question