从临时表 SQL 导入

Question

尝试将临时表中的行添加到表中时遇到问题。问题是它会添加 TempDealer 表中的行，即使它们已经在经销商表中（请注意，我在 WHERE 语句中指定了 WHERE td.supplier_ref不在（从@dealerStatus中选择supplier_ref）。每次我运行存储过程时，它都会再次将 TempDealer 中的所有行添加到经销商表中，而它应该只添加一次。有什么想法吗？提前谢谢。

        INSERT INTO @dealerStatus (dealerId, supplier_ref, [add], [timestamp])
            SELECT NULL, td.supplier_ref, 1, GETDATE()
                FROM TempDealer td 
                WHERE td.supplier_ref NOT IN (SELECT supplier_ref FROM @dealerStatus) 
            INSERT INTO Dealership( 
                    dealership_name, 
                    telephone, 
                    fax, 
                    sales_email, 
                    support_email, 
                    service_mask, 
                    address1, 
                    address2, 
                    town, 
                    county, 
                    postcode, 
                    website, 
                    date_modified, 
                    supplier_ref, 
                    dealer_type, 
                    county_id, 
                    town_id, 
                    area_id, 
                    district_id,
                    longitude,
                    latitude
                    ) 
                SELECT DISTINCT
                        [updateSource].leasing_broker_name, 
                        [updateSource].telephone, 
                        [updateSource].fax_number, 
                        [updateSource].email, 
                        [updateSource].support_email, 
                        [updateSource].service_mask, 
                        [updateSource].address1, 
                        [updateSource].address2, 
                        [updateSource].town, 
                        [updateSource].county, 
                        [updateSource].post_code, 
                        [updateSource].web_address, 
                        GETDATE(), 
                        [updateSource].supplier_ref, 
                        1, 
                        [updateSource].county_id, 
                        [updateSource].town_id, 
                        [updateSource].region, 
                        [updateSource].district, 
                        [updateSource].longitude,
                        [updateSource].latitude
                    FROM 
                        @dealerStatus dealerUpdateStatus INNER JOIN 
                        TempDealer [updateSource] ON dealerUpdateStatus.supplier_ref = updateSource.supplier_ref
                    WHERE 
                        dealerUpdateStatus.[add] = 1    

Answer 1

我是这样整理的：

        INSERT INTO @dealerStatus (dealerId, supplier_ref, [add], [timestamp])
            SELECT NULL, td.supplier_ref, 1, GETDATE()
                FROM TempDealer td 
                WHERE td.supplier_ref NOT IN (SELECT supplier_ref FROM Dealership WHERE dealership.supplier_ref IS NOT NULL and dealership.dealer_type = 1)