{Next Article in Series}
Having found out a bit more about the way OPENROWSET works, it seems to me that if you are using a format file for an ad hoc INSERT of file data this is the only way to go. The rest of the statement works like standard SQL, so you have better control (compared with BULK INSERT) over which columns are selected and which are written. Continue reading OPENROWSET and BULK INSERT (1)
Monthly Archives: October 2014
File Imports and Sql Server Language Settings
I had a file (historical FTSE values) to load into a database for some development work so I started up the SSIS Import and Export Wizard to bring it in. There was no error but after three or four attempts it was still failing to bring any rows in. Although I’ve done a lot of ETL I’ve generally used utilities or import exes, so this seemed like a opportunity to get my head around BULK INSERT and bcp. Continue reading File Imports and Sql Server Language Settings
Sequential uniqueidentifiers in Sql Server Tables
I’ve just come across this feature of Sql Server which apparently has been around since Sql 2008 (code in this example tested in Sql Server 2014). There might be good reasons for wanting to use a GUID in a clustered primary key, but since the key is random there may be issues later on with INSERTs into the table. Continue reading Sequential uniqueidentifiers in Sql Server Tables
A Column-level Query No-No in SELECT statements
You may sometimes want to do a lookup and return it as a column in your query results – but you don’t want to reference the source table in the FROM clause. In this case you may decide to use a column-level query. I’m not arguing the rights or wrongs of column-level queries, either on performance or theoretical grounds. The problem is that if this approach is applied naively it’s a bug. Continue reading A Column-level Query No-No in SELECT statements
SELECT statement gotchas in Oracle and Sql Server
Here are some issues which may catch you out from time to time. In each case Sql Server and Oracle, or Oracle Sql and PL/Sql, or versions of Oracle, work differently. Continue reading SELECT statement gotchas in Oracle and Sql Server
Debugging sql joins and filters in Oracle
This is a basic tip that will save time when a query isn’t returning the expected results (typically, it’s bringing back an empty set) and you need to find out which join or WHERE clause filter is causing the problem. It’s not impossible to do, of course, but it’s nice to have a method that gets a result quickly without too many edits. Take the following query, which should run on your Oracle instance: Continue reading Debugging sql joins and filters in Oracle
Apportionment calculations in Sql Server and Oracle
As you probably realise, this post deals with apportionment in finance data warehouses, not the U.S. Constitutional Apportionment Problem, interesting though that topic is. Continue reading Apportionment calculations in Sql Server and Oracle
