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;
FOR iVal IN 1..200 LOOP
INSERT INTO [TABLE] (column1, column2) VALUES (:array1[iVal], :array2[iVal]);
where array1 and array2 are PLSQLAssociativeArray arrays.
See also Oracle: Using PLSQLAssociativeArray with ODP.NET
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:
DROP TABLE #[TABLE]
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.
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 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)
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 🙂