CTE DML

Question

CTE DML

barry.mcconnell

Default port

Points: 1447
More actions
June 16, 2014 at 9:42 pm

#287653

Comments posted to this topic are about the item CTE DML
There are no facts, only interpretations.
Friedrich Nietzsche
June 17, 2014 at 5:13 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 33 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Easy one (as I recently read this blogpost: Change data through CTE’s[/url]). Thanks for the question!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

domenico.delbrocco SSChasing Mays Points: 617 More actions · Answer 2

domenico.delbrocco

SSChasing Mays

Points: 617

June 17, 2014 at 12:48 am

#1722141

I've never thought about insert data in a CTE

rhythmk SSCertifiable Points: 7271 More actions · Answer 3

Thanks for question.It reminds me the trick to delete the duplicate records in a table 🙂

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

Neil Burton SSC-Insane Points: 22999 More actions · Answer 4

rhythmk (6/17/2014)
Thanks for question.It reminds me the trick to delete the duplicate records in a table 🙂

Could you enlighten me on that please? It sounds very useful.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

free_mascot One Orange Chip Points: 27169 More actions · Answer 5

Thanks for QOD, Barry.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

danielfountain SSCarpal Tunnel Points: 4229 More actions · Answer 6

I never even considered using a CTE for an insert..... interesting.

palotaiarpad SSCertifiable Points: 5617 More actions · Answer 7

palotaiarpad

SSCertifiable

Points: 5617

June 17, 2014 at 2:10 am

#1722177

I hate DISTINCT *

😛

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 8

Koen Verbeeck (6/17/2014)
Easy one (as I recently read this blogpost: Change data through CTE’s[/url]). Thanks for the question!

+1

Thanks too

Igor Micev,My blog: www.igormicev.com

HanShi SSC-Dedicated Points: 33507 More actions · Answer 9

palotaiarpad (6/17/2014)
I hate DISTINCT *
😛

"hate" is a strong word, but in this QOTD the DISTINCT is absolutely not necessary.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

rhythmk SSCertifiable Points: 7271 More actions · Answer 10

BWFC (6/17/2014)
rhythmk (6/17/2014)
Thanks for question.It reminds me the trick to delete the duplicate records in a table 🙂
Could you enlighten me on that please? It sounds very useful.

Here you go.

CREATE TABLE #TEMP (ID INT,FNAME VARCHAR(50),LNAME VARCHAR(50))

GO

INSERT INTO #TEMP

SELECT 1,'LIONEL','MESSI' UNION ALL

SELECT 2,'NEYMAR','SILVA' UNION ALL

SELECT 3,'CHRISTIANO','RONALDO' UNION ALL

SELECT 4, 'THOMAS','MUELLER'

GO

--SELECT * FROM #TEMP

;WITH DUPL AS

(SELECT ID,FNAME,LNAME,ROW_NUMBER() OVER (PARTITION BY ID,FNAME,LNAME ORDER BY ID,FNAME,LNAME) [RN]

FROM #TEMP)

DELETE FROM DUPL WHERE RN <> 1

--SELECT * FROM #TEMP

--DROP TABLE #TEMP

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

Ed Wagner SSC Guru Points: 287026 More actions · Answer 11

CTEs solve so many problems. Thanks for the question.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Neil Burton SSC-Insane Points: 22999 More actions · Answer 12

rhythmk (6/17/2014)
BWFC (6/17/2014)
rhythmk (6/17/2014)
Thanks for question.It reminds me the trick to delete the duplicate records in a table 🙂
Could you enlighten me on that please? It sounds very useful.
Here you go.
CREATE TABLE #TEMP (ID INT,FNAME VARCHAR(50),LNAME VARCHAR(50))
GO
INSERT INTO #TEMP
SELECT 1,'LIONEL','MESSI' UNION ALL
SELECT 1,'LIONEL','MESSI' UNION ALL
SELECT 1,'LIONEL','MESSI' UNION ALL
SELECT 2,'NEYMAR','SILVA' UNION ALL
SELECT 2,'NEYMAR','SILVA' UNION ALL
SELECT 3,'CHRISTIANO','RONALDO' UNION ALL
SELECT 3,'CHRISTIANO','RONALDO' UNION ALL
SELECT 4, 'THOMAS','MUELLER'
GO
--SELECT * FROM #TEMP
;WITH DUPL AS
(SELECT ID,FNAME,LNAME,ROW_NUMBER() OVER (PARTITION BY ID,FNAME,LNAME ORDER BY ID,FNAME,LNAME) [RN]
FROM #TEMP)
DELETE FROM DUPL WHERE RN <> 1
--SELECT * FROM #TEMP
--DROP TABLE #TEMP

I like that, there's another one to add to the toolbox. Thanks.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

SqlMel SSCrazy Points: 2901 More actions · Answer 13

I actually thought "select distinct * ..." might raise an error.

I have never used this and wonder why it would even be allowed.

---------------
Mel. 😎