Insert into SQL Table where Pkey doesn't exist.

I have the following insert statement that I would like to run on a schedule. I want it to only insert records that have not already been inserted previously. The pkey field is my unique identifier.

INSERT  INTO [KLL_Cust].[dbo].[KLLStyleMasterException]
        ( [pkey] ,
		  [season] ,
          [Style] ,
          [lbl_code] ,
          [dimension] ,
		  [Date]
        )
        SELECT DISTINCT
		        L.pkey ,
                L.season ,
                RTRIM(L.style) Style ,
                lbl_code ,
                L.dimension ,
				GetDate() Datetime
        FROM    zzxscolr L
                LEFT OUTER JOIN ( SELECT DISTINCT
				                            pkey ,
                                            style ,
                                            dimension
                                  FROM      zzcordrd
                                  WHERE     total_qty <> 0
                                ) P ON P.style = L.style
                                       AND P.dimension = L.dimension
        WHERE   L.lbl_code <> ''
                AND L.style NOT IN ( SELECT style
                                     FROM   zzxscolr N
                                     WHERE  L.style = N.style
                                            AND L.dimension = N.dimension
                                            AND L.color_code = N.color_code
                                            AND N.lbl_code = '' )
                AND L.season <> 'ANY'
                AND division = 'KLL'
                AND season > '20171'
                AND P.style IS NOT NULL
	    END;

Open in new window

mburk1968Asked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Please try this -

INSERT  INTO [KLL_Cust].[dbo].[KLLStyleMasterException]
        ( [pkey] ,
		  [season] ,
          [Style] ,
          [lbl_code] ,
          [dimension] ,
		  [Date]
        )

		SELECT u.* FROM 
		(
				SELECT DISTINCT
						L.pkey ,
						L.season ,
						RTRIM(L.style) Style ,
						lbl_code ,
						L.dimension ,
						GetDate() Datetime
				FROM    zzxscolr L
						LEFT OUTER JOIN ( SELECT DISTINCT
													pkey ,
													style ,
													dimension
										  FROM      zzcordrd
										  WHERE     total_qty <> 0
										) P ON P.style = L.style
											   AND P.dimension = L.dimension
				WHERE   L.lbl_code <> ''
						AND L.style NOT IN ( SELECT style
											 FROM   zzxscolr N
											 WHERE  L.style = N.style
													AND L.dimension = N.dimension
													AND L.color_code = N.color_code
													AND N.lbl_code = '' )
						AND L.season <> 'ANY'
						AND division = 'KLL'
						AND season > '20171'
						AND P.style IS NOT NULL
			)u LEFT JOIN [KLL_Cust].[dbo].[KLLStyleMasterException] k ON u.pkey = k.pkey
			WHERE k.pkey IS NULL

Open in new window

0
 
mburk1968Author Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.