Discussion Locked

SQL is too cautious

0 helpful
6 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I am trying to convert a table from one format to another. The new format has a key in it, made of 3 columns. The old table has duplicates of the key columns, but often with distinct data. I have written a VBA function to convert one of the columns,taking into account keys and data already passed to the new table. Alas, for every call to the query nothing has been passed, because actual passage of data takes place only after all the input has been processed. There must be a way to have rows passed at once so that succeeding rows can be compared with them. I think there may be a special SQL statement to suspend the caution which is this time being a nuisance, or perhaps there is some adverb for the INSERT statement. Does nayone know what the technique is?

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

I noted from another question posted by you that you appear to using Access flavour of SQL; hence the VB/VBA approach. Both approach discussed above could be implemented under VB environment; you could create a temporary version of your destination table (without primary keys), apply adjustments, and then copy that table to the final destination; again you may be better off inserting the rows one at a time into your final destination table and log which rows specifically are causing a failure on a INSERT action.

by john_wills

The answer I have eventually taken is to read the table sequentially into a recordset with a DCount test for duplicate on the destination table before each write. Conceivably some SQL set-up would do the same, but probably not excluding duplicate data, which I also need to do.

by john_wills

It looks as though I should convert in situ or in an intermediate table, perhaps using UPDATE and some fancy nested SELECT to select the cases(thousands of them,thanks to some earlier programmer) where the key variables are duplicated. Is that what hmertens is thinking of?

This discussion is closed to new replies.