checking existence of a row before doing a SELECT INTO (SQL Server)

Question

Hi everyone,

I have a SQL statement I'd like to amend. As it stands now I'm running a simple SELECT INTO but I'd like to modify it so only records that don't exist in the destination table (as determined by my criteria) are appended.

Here's my initial statement:

SELECT b.BallotID, m.MeetingDate
    INTO StagingTable
    FROM Ballot INNER JOIN Meeting on b.MeetingID = m.MeetingID
    WHERE b.LastModifiedDate < '01-01-2010' AND ( b.BallotType = 'Agenda Vote' AND m.MeetingDate < GETDATE())  

I'd like to change things so that the StagingTable is populated only when the ballot doesn't already exist. Is this an acceptable way of going about it, or are there better alternatives?

SELECT b.BallotID, m.MeetingDate
    INTO StagingTable
    FROM Ballot INNER JOIN Meeting on b.MeetingID = m.MeetingID
    WHERE b.LastModifiedDate < '01-01-2010' AND ( b.BallotType = 'Agenda Vote' AND m.MeetingDate < GETDATE())   
    AND NOT EXISTS(SELECT 1 from StagingTable where BallotID = b.BallotID)) )

Answer 1

Your technique looks good except that the SELECT...INTO syntax is used to create a brand new table. Instead, you'd want to use the code below to add rows to an existing table:

INSERT INTO StagingTable
    (BallotID, MeetingDate)
    SELECT b.BallotID, m.MeetingDate
        FROM Ballot b
            INNER JOIN Meeting m
                on b.MeetingID = m.MeetingID
        WHERE b.LastModifiedDate < '01-01-2010' 
            AND (b.BallotType = 'Agenda Vote' AND m.MeetingDate < GETDATE())   
            AND NOT EXISTS(SELECT 1 from StagingTable where BallotID = b.BallotID)

Answer 2

SELECT INTO creates a new table rather than Insert data in to an existing table. Because of this I would check if the table exists if it does then drop the table before running your existing SQL. The will ensure that the StagingTable is dropped and recreated each time.

    IF OBJECT_ID('StagingTable','U') IS NOT NULL
    BEGIN
         DROP TABLE StagingTable
    END


        SELECT b.BallotID, m.MeetingDate
        INTO StagingTable
        FROM Ballot INNER JOIN Meeting on b.MeetingID = m.MeetingID
        WHERE b.LastModifiedDate < '01-01-2010' AND ( b.BallotType = 'Agenda Vote' 
        AND m.MeetingDate < GETDATE()) 

If you want to add rows to the existing table then you should use INSERT INTO as per Joe Stefanelli answer.