6 replies
by hmertens
I take you planning to process the imported table after the INSERT/SELECT query, parsing the rows (in the new table) against your VBA function to update the key columns (two step process). The SQL INSERT fails because one or more source table rows fails primary key contraints in the new table; as a single "transaction" INSERT action, the transaction is rollbacked (empty destination table).
Simplest solution; initially create our dest. table as having no keys; parse the rows correcting for new key rules and eliminating rows that are duplicates that would voilate the key; and finally when the port and adjustments are done on the new table, assign the primary key columns again.
I speaking from the perspective of MS-SQL2k, using it's Enterprise Manager and Query Analyzer.
by hmertens
Alternate approach:
Script a batch SQL command to insert the rows one at a time, generating your new keys as required within the batch as opposed to running seperately as VBA code. In T-SQL terms, create a CURSOR of the source table, and insert each row in turn into the new table for each row in the source table (I presume a 1-to-1 row transfer).
If any of your rows fails to insert, you could optionally log it to another table (same as source) where you can track down the problems that arise (duplicate key, data under/over flow, field type conflicts).
by hmertens
by john_wills
by john_wills
by john_wills
This discussion is closed to new replies.
Trending Web Development posts
Sort by Trending