Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Apr 7, 2004

OpenRowSource and OpenRowSet in SQL Server 2000 - Page 3

By Muthusamy Anantha Kumar aka The MAK

How to combine the results of two text files

Create c:\External\serverlist1.txt with the below given data.

Servername

ETL2
YUKON
SQL
SQL2k

Create c:\External\serverlist2.txt with the below given data.

Servername
ETL
YUKON
SQL2k

Execute the SQL query below.

select * from OpenRowset('MSDASQL', 
  'Driver={Microsoft Text Driver 
  (*.txt; *.csv)};
  DefaultDir=C:\External;',
  'select top 5 * from
Serverlist1.txt') union
select * from OpenRowset('MSDASQL', 
  'Driver={Microsoft Text Driver 
  (*.txt; *.csv)};
  DefaultDir=C:\External;',
  'select top 5 * from
Serverlist2.txt')

The results will look like the table below:

ETL

SQL2k

SQL

ETL2

YUKON

How to find the "not found" list comparing two files

Execute the SQL Query below:

--Not found List 
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
  DefaultDir=C:\External;','select top 5 * from
Serverlist1.txt') 
where Servername not in
(select Servername from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
  DefaultDir=C:\External;','select top 5 * from
Serverlist2.txt') )

The results will look like the table below:

ETL2

SQL

How to compare an SQL Table with an External Text File

Create a table ServerList in SQL Server as shown below.

Create table serverlist (Servername varchar(100))
insert into serverlist select 'ProductionSQL'
insert into serverlist select 'Yukon'
insert into serverlist select 'YUKOnBeta'
insert into serverlist select 'SQL2000'

Execute the SQL Query below.

--Compare SQL Table and a external text file 
--Not found list comparing sql table and c:\External\serverlist2.txt
Select * from Serverlist where servername not in 
(select Servername from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
  DefaultDir=C:\External;','select top 5 * from
Serverlist2.txt') )

The results will look like the table below:

ProductionSQL

YUKOnBeta

SQL2000

How to combine the results of two text files and a SQL Server table

Execute the below SQL Query:

--Combined distinct rows from two text files and the SQL Table

select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
  DefaultDir=C:\External;','select top 5 * from
Serverlist1.txt') union
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
  DefaultDir=C:\External;','select top 5 * from
Serverlist2.txt')  union
select * from serverlist

The results will look like the table below:

ETL

SQL2k

ProductionSQL

SQL

ETL2

SQL2000

YUKOnBeta

YUKON

How to create a View using OpenRowset or OpenRowSource

If you are planning to keep the Access, Excel, Text and CSV files in the same folder for a long time, then you can create views similar to the one below.

create view [MyText] as
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
  DefaultDir=C:\External;','select top 5 * from
Mytext.txt')

Go

select * from [MyText]

go

select * from [MyText] where Fname_Lname_Salary_Tax like 'S%'
go

Create view [MyExcel] as 
SELECT * 
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\External\MyExcel.xls";
    User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
go
select * from [MyExcel] where Fname like 's%'

Conclusion

As mentioned before, the intent of this article is to guide the SQL Server Database Administrators in using OpenRowSource and OpenRowSet for infrequent ad-hoc query requests from an external data source.

» See All Articles by Columnist MAK



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM