September 18, 2003 at 2:54 pm
I have a table that is created from a manually created Excel file. The file contains information to load three separate tables in the database. When I try to load the data using a stored procedure I get an error because it selects two records with the same key since it has different descriptions of the part. My method is to concatenate to compound keys and use 'NOT IN' to determine if the key is already loaded prior to selecting. This DOES NOT exclude two parts with the same key since the part is not yet loaded. How do I cause the stored procedure to load the part number that is first selected and skip over the error when it tries to load it the second time. I know that a cursor would work but isn't it also possible to process the stored procedure with errors?
September 18, 2003 at 3:58 pm
Your procedure will have to do a self-join from itself to a derived table of itself that is grouped by the primary key you are describing. In order to accomplish this, you will more than likely need to dump the tables to keyless temp tables that have IDENTITY keys, so that you can do an INNER JOIN on the IDENTITY when making the final insert.
--
For instance, the procedure would pull the three tables into 3 temp tables with a PRIMARY KEY defined on an IDENTITY field. Once populated, you would do some like this:
-- Example temp table:
CREATE TABLE #Temp1
(
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
, MyPKField1 CHAR(10) NOT NULL
, MyPKField2 CHAR(10) NOT NULL
-- , OtherFields...
)
-- SELECT FROM the Main Table, Joining to a subset of itself
-- with only unique keys.
SELECT
a.MyPKField1
, a.MyPKField2
--, a.OtherFields...
FROM #Temp1 a
INNER JOIN
(
SELECT
MIN(b.ID) AS FirstID
, b.MyPKField1
, b.MyPKField2
FROM #Temp1 b
GROUP BY
b.MyPKField1
, b.MyPKField2
) AS c
ON a.ID = c.FirstID
HTH,
Jay
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply