Technical Article

CTE DML

,

This script is an example explaining how to write DML using CTE.

IF OBJECT_ID('tempdb..#Students') IS NOT NULL
  BEGIN
    DROP TABLE #Students
  END
GO
CREATE TABLE #Students
  (
    [StudentID] [INT] NULL
  , [LastName] [VARCHAR](50) NULL
  , [FirstName] [VARCHAR](50) NULL
  , [HomeTown] [VARCHAR](50) NULL
  )
GO
INSERT #Students
  VALUES
    ( 1, 'Smith', 'Bill', 'New York' )
      ,
    ( 2, 'Jones', 'Sue', 'Chicago' )
      ,
    ( 3, 'Chavez', 'Juan', 'Atlanta' )
      ,
    ( 4, 'Smith', 'Bill', 'New York' )
GO
WITH  Student_CTE ( [StudentID], [LastName], [FirstName], [HomeTown] )
        AS ( SELECT
                [StudentID]
              , [LastName]
              , [FirstName]
              , [HomeTown]
              FROM
                #Students
           )
      
  INSERT Student_CTE
    VALUES
      ( 5, 'Wu', 'Xing', 'San francisco' )
GO
SELECT 
    *
  FROM
    #Students
GO

Rate

1 (7)

You rated this post out of 5. Change rating

Share

Share

Rate

1 (7)

You rated this post out of 5. Change rating