OPENROWSET function in SQL Server 2005

Among other enhancements in SQL Server 2005, enhancements were
made to the OPENROWSET function. This article illustrates BULK operations of
the OPENROWSET function.

Let us assume that we have text files Asia.txt and NorthAmerica.txt
in C:\Data folder with some data in it as shown below. [Refer Fig 1.0]

Asia.txt

1,
Mizuho, Fukushima, Tokyo

2, Minika,
Pang, Taipei

3, Jen, Ambelang, India

4, Jiang,
Hong, Shangai

5, Ada, Koo, HongKong

NorthAmerica.txt

1, Jim,
Brown, NewYork

2, James,
Brown, Trenton

3, Jane,
March, Smallville

4, Ravi, Shankar,
Chicago

Let us
assume that we have a table, Region, in the database, Companies,
as shown below.


USE [MASTER]
GO
IF EXISTS (SELECT NAME FROM SYS.DATABASES
WHERE NAME = N’COMPANIES’)
DROP DATABASE [COMPANIES]
GO
CREATE DATABASE COMPANIES
GO
USE [COMPANIES]
GO
IF EXISTS
(SELECT * FROM SYS.OBJECTS
WHERE OBJECT_ID =
OBJECT_ID(N’[DBO].[REGION]’)
AND TYPE IN (N’U’))
DROP TABLE [DBO].[REGION]
GO
CREATE TABLE REGION
( ID INT,
REGION VARCHAR(25),
DATA VARCHAR(MAX)
)

Let us
insert a new row in the table, REGION, with 1 as the value for the
column ID, ‘Asia’ as the value for the column REGION and all the
data from C:\Data\Asia.txt as values for the column DATA. This can be
done using the OPENROWSET function and BULK rowset provider.

First,
let us query all the data from Asia.txt, using the OPENROWSET function.


SELECT 1 AS ID, ‘ASIA’ AS REGION,
* FROM OPENROWSET
( BULK ‘C:\DATA\ASIA.TXT’,SINGLE_CLOB)
AS MYTABLE

The
result of the above executed query is shown below. [Refer Fig 1.1]

Fig 1.1

The same
ASIA.TXT can be queried as BLOB data, using OPENROWSET function.


SELECT 1 AS ID, ‘ASIA’ AS REGION,
* FROM OPENROWSET( BULK ‘C:\DATA\ASIA.TXT’,SINGLE_BLOB)
AS MYTABLE

The
result of the above executed query is shown below. [Refer Fig 1.2]



Fig 1.2

Now, let
us insert the data from Asia.txt and NorthAmerica.txt to the table, REGION.


USE [COMPANIES]
GO
INSERT INTO REGION (ID, REGION, DATA)
SELECT 1 AS ID, ‘ASIA’ AS REGION,
* FROM OPENROWSET( BULK ‘C:\DATA\ASIA.TXT’,SINGLE_CLOB)
AS MYTABLE
GO
INSERT INTO REGION (ID, REGION, DATA)
SELECT 2 AS ID, ‘North America’ AS REGION,
* FROM OPENROWSET( BULK ‘C:\DATA\NorthAmerica.TXT’,SINGLE_CLOB)
AS MYTABLE
GO

Let us
Query all the rows from the table, REGION, by using the below query.

SELECT * FROM REGION ORDER BY ID

The
result of the above executed query is shown below. [Refer Fig 1.3]



Fig 1.3

Let us
create a stored procedure that can do the bulk operation insert of all the
files from the given folder using OPENROWSET function.


USE [COMPANIES]
GO
IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N’[DBO].[MYDATA]’) AND TYPE IN (N’U’))
DROP TABLE [DBO].[MYDATA]
GO
CREATE TABLE MYDATA (
ID INT IDENTITY(1,1),
REGION VARCHAR(100),
DATA VARCHAR(MAX))
GO
USE [COMPANIES]
GO
IF EXISTS
(SELECT * FROM SYS.OBJECTS
WHERE OBJECT_ID = OBJECT_ID(N’[DBO].[USP_OPENROWSET_BULK_IMPORT]’) AND TYPE IN (N’P’, N’PC’))
DROP PROCEDURE [DBO].[USP_OPENROWSET_BULK_IMPORT]
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE USP_OPENROWSET_BULK_IMPORT
@DIR VARCHAR(250),
@TYPE VARCHAR(100)
AS
SET NOCOUNT ON
DECLARE @COUNT INT
DECLARE @QUERY VARCHAR(2000)
DECLARE @NAME VARCHAR(100)
DECLARE @FULLNAME VARCHAR(200)
SET @QUERY =”MASTER..XP_CMDSHELL ‘DIR “+@DIR+@TYPE+” /B'”
–PRINT @QUERY
SET @COUNT = 1
CREATE TABLE #DIR (ID INT IDENTITY(1,1), NAME VARCHAR(200))
INSERT #DIR EXEC (@QUERY)
–SELECT * FROM #DIR
WHILE @COUNT<=SCOPE_IDENTITY()
BEGIN
SET @NAME=(SELECT NAME FROM #DIR WHERE ID=@COUNT)
SET @FULLNAME=@DIR+@NAME
IF @NAME IS NOT NULL
BEGIN
SET @QUERY = “INSERT INTO MYDATA(REGION, DATA)
SELECT ‘”+@NAME+”‘ AS REGION, * FROM OPENROWSET( BULK ‘” +@FULLNAME+”‘ ,SINGLE_CLOB)
AS MYTABLE”
PRINT @QUERY
EXEC (@QUERY)
END
SET @COUNT=@COUNT+1
END
GO

Let us
assume that we have three text files in the folder C:\Data as shown in Fig 1.4.



Fig 1.4

Asia.txt
and NorthAmerica.txt have not been changed. We added Europe.txt with data as
shown below.

Europe.txt

1,
Richard, Moore, London

2,
Sophie, Marque, Paris

3,
Robert, Bening, Rome

This
procedure can be executed by passing the folder location and type of file names
as shown below.

EXEC USP_OPENROWSET_BULK_IMPORT 'C:\DATA\','*.TXT'

When this
stored procedure executes, it generates the SQL Statement with the OPENROWSET
function, as shown below, and executes the generated SQL Statement.


INSERT INTO MYDATA(REGION, DATA)
SELECT ‘Asia.txt’ AS REGION, * FROM OPENROWSET( BULK ‘C:\DATA\Asia.txt’ ,SINGLE_CLOB)
AS MYTABLE
INSERT INTO MYDATA(REGION, DATA)
SELECT ‘Europe.txt’ AS REGION, * FROM OPENROWSET( BULK ‘C:\DATA\Europe.txt’ ,SINGLE_CLOB)
AS MYTABLE
INSERT INTO MYDATA(REGION, DATA)
SELECT ‘NorthAmerica.txt’ AS REGION,
* FROM OPENROWSET( BULK ‘C:\DATA\NorthAmerica.txt’ ,SINGLE_CLOB)
AS MYTABLE

Let us Query all of the rows from the table, MYDATA, by
using the following query.

SELECT * FROM MYDATA

The
result of the above executed query is shown below. [Refer Fig 1.5]



Fig 1.5

Note: This stored procedure uses the XP_Cmdshell
command, which by default is disabled. You have to enable it using sp_configure
as shown below:


EXEC sp_configure ‘show advanced options’, 1
GO
RECONFIGURE
GO
EXEC sp_configure ‘xp_cmdshell’, 1
GO
RECONFIGURE with override
GO

Conclusion

We have
illustrated and learned the BULK operation enhancement in the OPENROWSET
function.

»


See All Articles by Columnist
MAK

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles