September 22, 2011 at 5:16 pm
I am troubleshooting a daily process that bulk loads a New Product table, and then these new products are inserted into the regular Product Table. These two tables have the exact same fields. The problem we are having is that the insert process into the Product Table fails to insert the new products once or twice a month. The current INSERT statement looks like this:
INSERT INTO Products
SELECT New_Products.*
FROM New_Products
LEFT OUTER JOIN Products ON
New_Products.BegSerialNum = Products.BegSerialNum
AND New_Products.EndSerialNum = Products.EndSerialNum
WHERE Products.BegSerialNum = NULL
AND Products.EndSerialNum = NULL
Please note that this table does not have an index field.
The issue I have with this code is that there are no records for the new products in the Product table, so how can a field be null? I have never used code like this for an insert process. I have always written code to look for exact rows using all the rows in the table and would like to replace this code with something like this:
INSERT INTO Products
SELECT New_Products.*
FROM New_Products
WHERE New_Products.BegSerialNum NOT IN
(SELECT BegSerialNum FROM Products
INNER JOIN New_Products
ON Products.BegSerialNum = New_Products.BegSerialNum
AND Products.EndSerialNum = New_Products.EndSerialNum)
AND New_Products.EndSerialNum NOT IN
(SELECT EndSerialNum FROM Products
INNER JOIN New_Products
ON Products.BegSerialNum = New_Products.BegSerialNum
AND Products.EndSerialNum = New_Products.EndSerialNum)
I have written code like this for another database, but am unsure whether SQL Server will find it acceptable syntax. I appreciate any insight or opinions anyone may have to offer.
September 22, 2011 at 7:20 pm
bobznkazoo (9/22/2011)
I am troubleshooting a daily process that bulk loads a New Product table, and then these new products are inserted into the regular Product Table. These two tables have the exact same fields. The problem we are having is that the insert process into the Product Table fails to insert the new products once or twice a month. The current INSERT statement looks like this:INSERT INTO Products
SELECT New_Products.*
FROM New_Products
LEFT OUTER JOIN Products ON
New_Products.BegSerialNum = Products.BegSerialNum
AND New_Products.EndSerialNum = Products.EndSerialNum
WHERE Products.BegSerialNum = NULL
AND Products.EndSerialNum = NULL
Please note that this table does not have an index field.
The issue I have with this code is that there are no records for the new products in the Product table, so how can a field be null? I have never used code like this for an insert process. I have always written code to look for exact rows using all the rows in the table and would like to replace this code with something like this:
INSERT INTO Products
SELECT New_Products.*
FROM New_Products
WHERE New_Products.BegSerialNum NOT IN
(SELECT BegSerialNum FROM Products
INNER JOIN New_Products
ON Products.BegSerialNum = New_Products.BegSerialNum
AND Products.EndSerialNum = New_Products.EndSerialNum)
AND New_Products.EndSerialNum NOT IN
(SELECT EndSerialNum FROM Products
INNER JOIN New_Products
ON Products.BegSerialNum = New_Products.BegSerialNum
AND Products.EndSerialNum = New_Products.EndSerialNum)
I have written code like this for another database, but am unsure whether SQL Server will find it acceptable syntax. I appreciate any insight or opinions anyone may have to offer.
The top sql statement with the left join is correct. When you do a left join and check for that joined tables field to be null, that will only be true when the record does not exist in the table you are left joining to. It is a common practice to use a left join in this way to only insert new records into a table, by first left joining to it to make sure you aren't creating duplicates.
I don't know if that answers your questions or not.
September 24, 2011 at 2:34 pm
You may want to look outside of the script. Is this a stored procedure executed by a SQL job? By an application? Also, when this failure occurs, does it insert some records or none of the new records? I agree that the script that is in place is a normal comparison script that compares the two tables and inserts only the different records. Explain the process that executes the script and we may be able to explore possible failures.
September 24, 2011 at 2:43 pm
You may want to look outside of the script. Is this a stored procedure executed by a SQL job? By an application? Also, when this failure occurs, does it insert some records or none of the new records? I agree that the script that is in place is a normal comparison script that compares the two tables and inserts only the different records. Explain the process that executes the script and we may be able to explore possible failures.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply