Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

with statement Expand / Collapse
Author
Message
Posted Friday, January 17, 2014 12:54 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 1:56 PM
Points: 1,749, Visits: 3,154
Can I use two ddl after with cte statement?

The first ddl is to use a merget statement to update a column.

the second ddl is to insert the updated record into another log table.


Thanks,
Post #1532243
Posted Friday, January 17, 2014 1:03 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:27 AM
Points: 3,342, Visits: 7,226
Being pedantic, you can't use any ddl statements with a cte.

Without being pedantic and trying to be useful, I assume you want to say DML. However, ctes work only for one statement. You could insert the result of your cte into a temp table and use it for both statements.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1532248
Posted Friday, January 17, 2014 1:05 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 1:56 PM
Points: 1,749, Visits: 3,154
Thanks, you are right I mean DML, one update, one insert,
it looks like using a temp table is a good solution.

Post #1532249
Posted Friday, January 17, 2014 1:10 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:48 PM
Points: 23,015, Visits: 31,536
sqlfriends (1/17/2014)
Thanks, you are right I mean DML, one update, one insert,
it looks like using a temp table is a good solution.



Actually depends what you are attempting to accomplish. Why don't you provide the code you are using and we can see if it needs to be done using a temporary table or not.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1532255
Posted Friday, January 17, 2014 1:10 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:27 AM
Points: 3,342, Visits: 7,226
Have you tried the OUTPUT clause?


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1532256
Posted Sunday, January 19, 2014 4:00 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:11 PM
Points: 15,517, Visits: 27,898
Remember, while it has the word "table" in the definition of Common Table Expression, a CTE is nothing but a query. It's not a table. Comparing it to a temp table means you might be under that impression.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1532436
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse