4USING OPENROWSETIN THIS EXERCISE, YOU WILL CREATE A TEXT F...

EXERCISE 8.4

U

^sing

oPENROWSET

In this exercise, you will create a text file with some sample data in it.

You will then use the OPENROWSET() function to query data from the

text file from within SQL Server. This exercise assumes that you have

administrative privileges on the SQL Server instance you are working

with, that you have the AdventureWorks2008 sample database installed

on your SQL Server instance, and that you are running the wizard from

the same computer where the SQL Server instance is installed. You must

have completed Exercise 8.3 to enable ad hoc distributed queries as well.

1. Create a directory off the root of the C: Drive named C:\BCP (if it

doesn’t already exist from before).

2. Using Notepad create a new text file named C:\BCP\Presidents.csv.

Enter the following text into the file and save it:

PresidentID,FirstName,LastName

1,George,Washington

2,John,Adams

3,Thomas,Jefferson

3. In a query window in SSMS enter the following query to select

the data from the text file:

SELECT

*

FROM OPENROWSET (

'MSDASQL',

'Driver={Microsoft Text Driver (

*

.txt;

*

.csv)};

DBQ=c:\bcp;',

'SELECT

*

from presidents.csv');

4. Try creating some different text files to query from. How about

a tab-delimited file?

Modifying Data with OPENROWSET

OPENROWSET can be used with INSERT, UPDATE, and DELETE statements

as well. Just as you could query data in a remote data source using a SELECT

statement with OPENROWSET, you can also change data in a remote data store.

The trick is to place the OPENROWSET(…) function call where the table

reference would normally exist.

Here is an example of change data in the WebSQL.AdventureWorks.Person.

Person table:

UPDATE OPENROWSET(

'SQLNCLI',

'Server=WEBSQL;Trusted_Connection=Yes;Database=AdventureWorks;',

AdventureWorks2008.Person.Person)

SET FirstName = 'Ken'

WHERE BusinessEntityID = 1;

We will talk a little more about this in a later section.

Copying Data with OPENROWSET

Another powerful thing to do with that is to store the data you query from the

external data source as data in your own SQL server instance. You can do this with

the SELECT…INTO or INSERT…SELECT statements.

The following example shows both methods to copy data from the WebSQL.

AdventureWorks2008.Sales.SalesOrderHeader table to a local table called

AdventureWorks2008.Sales.SalesOrderHeaderCopy:

--Create a local table as a result of the copy

SELECT * INTO AdventureWorks2008.Sales.SalesOrderHeaderCopy

FROM OPENROWSET(

'Server=WebSQL;Trusted_Connection=Yes;Database=AdventureWorks;',

AdventureWorks2008.Sales.SalesOrderHeader);

--Insert the selected data into an existing table

INSERT INTO Sales.SalesOrderHeaderCopy

SELECT *