I am working on a process in my applicaiton that is used to update a table in our database called ListDetails. The idea behind this process is that the data is in a text file that has been parsed into a fixed, known format by another, outside process. We take this text file, parse it into records, and insert each into a temporary table. When all of the data is loaded into the temporary table, we use a stored procedure to merge the data from the temporary table into the live table.

This process was originally built using SQL Server and works there. I am porting it to Sql Anywhere 12.0.1. This process is built in C#.

My problem is in the loading into the temporary table phase. Here's the code for creating the temporary table:

new SACommand( @"DROP TABLE IF EXISTS tempimport;
       SELECT * INTO LOCAL TEMPORARY TABLE tempimport FROM ListDetails WHERE 1 = 0;", saConnection, saTransaction ).ExecuteNonQuery();

This executes without any errors.

In SQL server, we used bulk copy to load the data. It turns out that a dedicated SACommand that runs an INSERT statement is actually faster that the SA ADO.net SABulkCopy class, so I created just that:

SACommand insertCommand = new SACommand( "INSERT INTO tempimport " +
                                             "( ListDetailId, ListId, CountryId, LocaleCode, Plate, HashedPlate, AlarmClassId, BeginDate, EndDate, " +
                                               "ListPriorityId, VehicleTypeId, PlateClassId, MakeId, ModelId, Year, ColorId, " +
                                               "Notes, OfficerNotes, CreateDate, Subscriber, InstanceId ) " +
                                         "VALUES " +
                                             "( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ", saConnection, saTransaction );

The next code creates a bunch of SAParameter objects, one for each ?, and adds them to the SACommand object's Parameters collection. Then, in a loop, I parse the file, load the data into each SAParameter object's Value property as needed, and finally execute the SACommand.

The problem is that when I execute the SACommand, I get an error that states that the table tempimport does not exist. What am I doing wrong? Should I use a DECLARE LOCAL TEMPRORARY TABLE command? The advantage of the way I'm doing it is that this code doesn't have to change if the ListDetails table ever changes.

Just to make sure it's clear, the SAConnection and SATransaction objects in the two SACommands are the same objects, and neither the transaction or the connection end until after all of the work is done.