CODEWITHSUNDEEP
sqlsql-servert-sqlsql-insert
ThomasRones
ThomasRones

Reputation: 683

Iterating through values to insert... or another way? SQL Server

Addtional Question: I cannot alias the ETL.Dim_Status in the INSERT statement ... so this leads me to believe that the insert into and the select/from/leftjoin/where are two separate statments... So why cant I put the insert into beneath everything??

Are they two separate statements or are they one? If they are one, then why can't I use an alias in the insert into portion?

I the select a subquery?... Then why isn't it in parentheses?

So if I try to run this, then I get an error telling me that the Sub-Query returned more than one value. I am aware of this, I need all those values to be inserted. 

INSERT INTO ETL.Dim_Status ([Status]) 
VALUES(
          --- I need to iterate through each of the new distinct values 

            (
            SELECT DISTINCT 
                I.[Status] 
            FROM 
                dbo.Invitee I
            LEFT JOIN
                ETL.Dim_Status S
                ON  I.[Status] = S.[Status]
            WHERE 
                S.[Status] IS NULL
            )

   );

Could I make this a set based operation rather than inserting the values row by row?

I've tried a cursor, but I don't even know if I put everything in the right place:

  DECLARE insertNewStatus CURSOR    
  INSERT INTO ETL.Dim_Status ([Status]) 
  VALUES(
      --- I need to iterate through each of the distinct values 
          FOR
             ( 
             SELECT DISTINCT 
                I.[Status] 
             FROM 
                dbo.Invitee I
             LEFT JOIN
                ETL.Dim_Status S
                ON  I.[Status] = S.[Status]
             WHERE 
                S.[Status] IS NULL
             )
         OPEN insertNewStatus
         FETCH NEXT FROM insertNewStatus

   );

Upvotes: 0

Views: 49

Answers (2)

huMpty duMpty
huMpty duMpty

Reputation: 14470

INSERT INTO ETL.Dim_Status ([Status])      
SELECT DISTINCT I.[Status] 
FROM dbo.Invitee I
     LEFT JOIN ETL.Dim_Status S ON  I.[Status] = S.[Status]
 WHERE S.[Status] IS NULL

Update - if you trying to add the status from Invitee, which are not already in ETL.Dim_Status

   INSERT INTO ETL.Dim_Status ([Status])    
   SELECT DISTINCT [Status] 
   FROM dbo.Invitee 
   WHERE [Status] NOT IN ( SELECT DISTINCT [Status] FROM ETL.Dim_Status)

See more about Adding Rows by Using INSERT and SELECT

Upvotes: 3

Nahuel Fouilleul
Nahuel Fouilleul

Reputation: 19335

Just remove values keyword, sql operation are set operations. It's a standard SQL syntax.

INSERT INTO ETL.Dim_Status ([Status]) 
SELECT DISTINCT 
    I.[Status] 
FROM 
    dbo.Invitee I
LEFT JOIN
    ETL.Dim_Status S
    ON  I.[Status] = S.[Status]
WHERE 
    S.[Status] IS NULL

Upvotes: 2

Related Questions