Solved

DML on CTE?

Posted on 2013-10-24
4
438 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 500 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

776 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