Daily Archives: October 10, 2011

Oracle: Bulk insert using an array

You really don’t want to generate an insert for each row you want to insert, this will be slow to create, slow to transmit, slow to parse and slow to process. I also don’t like creating CSVs and importing these via SQL Plus. I’m an application developer, so I want to do bulk operations via ODP.NET.

Instead I use arrays, they truly are wonderful and often overlooked.
A syntax like so is really useful to remember:
DECLARE iVal INT;
BEGIN
FOR iVal IN 1..200 LOOP
INSERT INTO [TABLE] (column1, column2) VALUES (:array1[iVal], :array2[iVal]);
END LOOP;
END;

where array1 and array2 are PLSQLAssociativeArray arrays.

See also Oracle: Using PLSQLAssociativeArray with ODP.NET

Advertisements

SQL Server: Drop a temporary table if it already exists

I recently needed to drop a temporary table if it already existed, I don’t know why but I ended up using the following script:
BEGIN TRY
DROP TABLE #[TABLE]
END TRY
BEGIN CATCH
END CATCH

It’s a familiar construct, we simply try the drop, catch any errors, ignore them and carry on with the rest of the script.

I find this much easier than the normal SQL Server drop scripting you will see when you generate a drop and create script, but of course any real errors won’t be caught by this and I don’t think you can even use that for temporary tables, hence why I used the above instead.

SQL Server: Speeding up slow large inserts

When doing a large / bulk insert I normally disable all indexes:
ALTER INDEX ALL ON [TABLE] DISABLE

Often there maybe be primary keys etc that need to be enabled, enable only those that you need via:
ALTER INDEX [PK_NAME] ON [TABLE] REBUILD

Once the insert is complete simply rebuild all the indexes via:
ALTER INDEX ALL ON [TABLE] REBUILD

SQL Server: Replace PRINT with RAISERROR

SQL Server only displays PRINT messages once the script has finished, if you want your debug output to come out immediately then use RAISERROR:

RAISERROR('message here', 0, 255) WITH NOWAIT

NOWAIT – Sends messages immediately to the client.

See also MSDN RAISERROR (Transact-SQL)

What was the temperature on that ride?

I’ve got into the habit of recording the temperature of each ride and noting what clothing I’m wearing, this helps me guess what clothing I’ll need but also gives me some statistics of what clothes are doing the mileage. I naturally also record the mileage, this gives me an idea of when certain maintenance activities (e.g. Rohloff oil change) are due.
I can see I wear cycling shorts more than any other item of clothing and only at temperatures above about 9 celsius. Anything lower and I need leg warmers, anything below freezing and I opt for roubaix tights. So if the forecast is minimum 12 degrees celsius then I know I can ride comfortably in shorts.


(In the UK) The Met Office has a good website for checking the weather and it also has 24hrs of observations under the “Latest/recent” section. This is my primary source of temperature statistics, yes it’s not as accurate as carrying a thermometer and recording the temperatures myself, but that would be OTT wouldn’t it 🙂