In a procedure I wrote, I wanted to allow the flexibility to support various types for a flat-file. This required an IF…ELSE block. Inside the block, the data would be imported, scrubbed, and loaded into a temporary table.
I had difficulty finding any documentation for formatting the sheet name when using the OLEDB ACE engine to access an Excel file. I had originally attempted the following query: SELECT * FROM OPENROWSET(’Microsoft.ACE.OLEDB.12.0′, ‘Excel 12.0;HDR=YES;Database=C:\Report Output.xlsx’, sheet1$)SELECT * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’,… Continue Reading
In this post, I hope to show how to load in a CSV (comma-separated values) flat-file with an optional double quote (“) text qualifier and a XML format file using BULK INSERT and OPENROWSET.
A query will fail when a column is aliased and then referenced in the WHERE clause. SELECT col1, col2 AS c2 FROM tbl1 WHERE c2 BETWEEN 2 AND 6SELECT col1, col2 AS c2 FROM tbl1 WHERE c2 BETWEEN 2 AND… Continue Reading
I will briefly explain the differences between temporary tables and table variables. Temporary Tables (#) CREATE TABLE #temp (col1 …)CREATE TABLE #temp (col1 …) Table Variables (@) DECLARE @temp TABLE (col1 …)DECLARE @temp TABLE (col1 …) What do the signs… Continue Reading
There may be instances where you need to prepare columns for a SELECT statement or for a SELECT … PIVOT.
This post will show an example of how to join a table against itself to return a running total.
The following query is useful when trying to select an inclusive range of records using DATETIME. For this example, we will use GETDATE() which returns the current date & time. You can modify this at any instance in the code… Continue Reading
The following example will return two columns (first name, last name) based on a single, concatenated, comma separated name. table —— name —— Chin, Cheryl Frank, Robert SELECT RIGHT([name], LEN([name]) – CHARINDEX(’,’, [name]) – 1) AS [first], LEFT([name], CHARINDEX(’,’, [name])-1)… Continue Reading
You may receive the following errors if you incorrectly alias your column names: Invalid column name No column was specified Check to make sure that your column names are encased in brackets and are not encased by quotes. Incorrect SELECT… Continue Reading