Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

Category Archives: SQL Querying

The BULK INSERT command is used widely for importing data into SQL Server tables. It is very handy when there is a requirement to import large amounts of data from a CSV or Text file. An example follows:-

BULK INSERT MyTable
FROM 'e:\mytextfile.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

However, there are certain scenarios when I do not want to import vast amounts of data into a table, but instead need to be able query the file directly. This can be achieved using OPENROWSET.

SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=e:\MyExcelFile.xls',
                'SELECT * FROM [Sheet1$]')

It is then possible to join data from the file to tables within your database:-

SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=e:\MyExcelFile.xls',
                'SELECT * FROM [Sheet1$]') x
LEFT JOIN CustomerTable c
	on c.customerid = x.custid
Advertisements

Tags: , , , , , ,

The HAVING clause is used in conjunction with a GROUP BY so that aggregate functions can be used in a comparison. Think of it as a WHERE clause for groupings.

The following example should help to explain further.

The following example will fail because the WHERE clause is used to evaluate data on a row by row basis. The query is an attempt to limit the results returned to those customers with an order count greater than five.

SELECT customer_id, COUNT(customer_id) AS 'No of Transactions'
FROM Order_Table
WHERE COUNT(customer_id) > 5
GROUP BY customer_id

You will receive an aggregate related error, as the WHERE clause can only operate on a single row. To achieve the desired result, we remove the WHERE clause and add a HAVING clause after the GROUP By. Running the following query will now work:-

SELECT customer_id, COUNT(customer_id) AS 'No of Transactions'
FROM Order_Table
GROUP BY customer_id
HAVING COUNT(customer_id) > 5

Tags: , , , , ,

%d bloggers like this: