OPENROWSET function in SQL Server 2005

February 16, 2006

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]

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]

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]

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.

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]

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