Different Options for Importing Data into SQL Server

In addition to using the Import / Export wizards and/or DTS or SSIS to move data into SQL Server there are also a few other options for doing this that are built into SQL Server.  Some these other options include bcp, BULK INSERT, OPENROWSET as well as others.  The following examples show you some of these different options for importing data and how you can use some of these inline with your T-SQL code as well as others that can be run from the command line.

BCP
This is one of the options that is mostly widely used.  One reason for this is that it has been around for awhile, so DBAs have come quite familiar with this command.  This command allows you to both import and export data, but is primarily used for text data formats.  In addition, this command is generally run from a Windows command prompt, but could also be called from a stored procedure by using xp_cmdshell or called from a DTS or SSIS package.

Here is a simple command for importing data from file C:\ImportData.txt into table dbo.ImportTest.

bcp dbo.ImportTest in 'C:\ImportData.txt' -T -SserverName\instanceName

BULK INSERT
This command is a T-SQL command that allows you to import data directly from within SQL Server by using T-SQL.  This command imports data from file C:\ImportData.txt into table dbo.ImportTest.

BULK INSERT dbo.ImportTest
FROM 'C:\ImportData.txt'
WITH ( FIELDTERMINATOR =',', FIRSTROW = 2 )

OPENROWSET
This command is a T-SQL command that allows you to query data from other data sources directly from within SQL Server.  By using this command along with an INSERT INTO command we can load data from the specified data source into a SQL Server table.

This command will pull in all data from worksheet [Sheet1$]. By using the INSERT INTO command you can insert the query results into table dbo.ImportTest.

INSERT INTO dbo.ImportTest
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\ImportData.xls', [Sheet1$])

Here is another example where data is pulled from worksheet [Sheet1$] by using a SELECT * FROM command. Again, by using the INSERT INTO command you can insert the query results into table dbo.ImportTest.   The query can be any valid SQL query, so you can filter the columns and rows by using this option.

INSERT INTO dbo.ImportTest
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\ImportData.xls', 'SELECT * FROM [Sheet1$]')

OPENDATASOURCE
This command is a T-SQL command that allows you to query data from other data sources directly from within SQL Server. This is similar to the OPENROWSET command.

INSERT INTO dbo.ImportTest
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\ImportData.xls;Extended Properties=Excel 8.0')...[Sheet1$]

OPENQUERY
Another option is OPENQUERY.  This is another command that allows you to issue a T-SQL command to select data and again with the INSERT INTO option we can load data into our table.  There are two steps with this process, first a linked server is setup and then second the query is issued using the OPENQUERY command.  This option allow you to filter the columns and rows by the query that is issued against your linked data source.

EXEC sp_addlinkedserver 'ImportData',
   'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
   'C:\ImportData.xls',
   NULL,
   'Excel 8.0'
GO

INSERT INTO dbo.ImportTest
SELECT *
FROM OPENQUERY(ImportData, 'SELECT * FROM [Sheet1$]')

Linked Server
Here is yet another option with setting up a linked server and then issuing a straight SQL statement against the linked server.  This again has two steps, first the linked server is setup and secondly a SQL command is issued against the linked data source.

EXEC sp_addlinkedserver 'ImportData',
   'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
   'C:\ImportData.xls',
   NULL,
   'Excel 8.0'
GO

INSERT INTO dbo.ImportTest
SELECT * FROM ImportData...Sheet1$

Export data from SQL Server to Excel

     Exporting data from SQL Server to Excel can be achieved in a variety of ways.  Some of these options include Data Transformation Services (DTS), SQL Server Integration Services (SSIS) and Bulk Copy (BCP).  Data Transformation Services (SQL Server 2000) and SQL Server Integration Services (SQL Server 2005) offers a GUI where widgets can be dragged and dropped Each option has advantages and disadvantages, but all can do the job.  It is just a matter of your comfort level with the tools and the best solution to meet the need.
     Another option that is available directly via the T-SQL language is the OPENROWSET command (SQL Server 2000 and SQL Server 2005).  This command can be called directly in any stored procedure, script or SQL Server Job from T-SQL..
     Below is a simple example of writing out the Job name and date to Sheet1 of an Excel spreadsheet in either SQL Server 2005 or 2000.

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;',
'SELECT Name, Date FROM [Sheet1$]')
SELECT [Name], GETDATE() FROM msdb.dbo.sysjobs
GO

Dynamic SQL

SQL Server offers a few ways of running a dynamically built SQL statement.  These ways are:

1. Writing a query with parameters
2. Using EXEC
3. Using sp_executesql

For more information see: http://www.mssqltips.com/tip.asp?tip=1160

Factory Class for SQL and OLEDB Data Providers

Introduction

While developing a real life software project you need to take in to account many things. One of these things is the choice of database. Most of the times your client will have predefined database (e.g. SQL Server or Oracle). However, some times situation arises such that your database is not fixed at development time. For example if you are developing a product then you will not be knowing the database of your prospective clients in advance. Similarly, if your client demands for database independence (may be because he is migrating his database) then also the database is not fixed. The common solution can be to develop two different sets of data access components - one for SQL server and another for Oracle. However, this will increate your maintenance and version tracking. .NET provides an easy way to tackle this problem. All the data access classes i.e. classes from System.Data.SQLClient and System.Data.OleDb namespaces actually implement certain interfaces from System.Data namespace. In order to make your data access layer components independent of any database you can code against these interfaces rather than actual classes. In this article we will develop a factory class that returns objects from appropriate namespace based on the data provider type you supply. We will also see how to code your data access layer against these interfaces.

System.Data Interfaces

All the .NET providers ( SQL and OleDB) implement certain set of interfaces found in System.Data namespace. Following is the list of some of these interfaces:

• IDbConnection
• IDbCommand
• IDbDataAdapter
• IDataParameter

You will easily be able to identify the mapping between the interfaces and actual classes.

Developing a factory class

We will develop a factory class that will simply return you objects of specified data provider. For example if you specify that your data provider is SQL server, you will be returned a SQLConnection object where as if you specify that your data provider is any OLEDB compatible database then a OleDbConnection will be returned. Following is the complete code for the class.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace DALFactory
{
public class DALObjects
{

public static IDbConnection
GetConnection(DataProviderType type) 
{
if(type==DataProviderType.SQL)
{
		return new SqlConnection();
	}
	else
	{
		return new OleDbConnection();
	}
}

public static IDbCommand
GetCommand(DataProviderType type)
{
 	if(type==DataProviderType.SQL)
	{
		return new SqlCommand();
	}
	else
	{
		return new OleDbCommand();
	}
}

public static IDbDataAdapter
GetDataAdapter(DataProviderType type)
{
	if(type== DataProviderType.SQL)
	{
		return new SqlDataAdapter();
	}
	else
	{
		return new OleDbDataAdapter();
	}
}


public static IDataParameter
GetParameter(DataProviderType type)
{
	if(type== DataProviderType.SQL)
	{
		return new SqlParameter();
	}
	else
	{
		return new OleDbParameter();
	}
}
}

public enum DataProviderType
{
	SQL=0,OLEDB=1
}
}

As you will notice we are returning objects that can be instantiated on their own. That is why we have not included DataReader here. Note that we have created all the methods as static. This way we can easily make use of our methods without creating class instance. Also, note that we will be using DataProviderType enum in our code that indicates which namespaces to use. Now that your have your factory class ready let us see how to use it.

Coding against System.Data interfaces

As mentioned earlier in this section we will see how to use the factory class in our code. Typically when you develop a data access class your code looks like this:

OleDbConnection cnn;
OleDbDataAdapter cmd;
OleDbDataReader dr;

cnn = New OleDbConnection(connectionstring);
cmd = New OleDbDataAdapter(sqlquery,cnn);

dr=cmd.ExecuteReader();

Now, since we need to make our class database independent we will not explicitly declare objects from OleDb or SqlClient namespaces. Instead we will code as follows:

IDbConnection cnn=
DALObjects.GetConnection(DataProviderType.OLEDB);
cnn.Open();
IDbCommand cmd=
DALObjects.GetCommand(DataProviderType.OLEDB);
cmd.ConnectionString="some_sql";
cmd.Connection=cnn ;
IDataReader dr=cmd.ExecuteReader();

Here, we have avoided using explicit classes of specific data provider. Note that since you are using interfaces here you will not be able to invoke methods and properties specific to a particular provider. For example, if you want to use IDbTransaction interface then you can not call Save() method which is specific to SQL Server. However, what we achieve is database independence. Now, in above example the data provider type parameter can be stored in some external XML file that can be easily changed after installation at client's place.

What is CRM?

      CRM stands for Customer Relationship Management. It is a process or methodology used to learn more about customers' needs and behaviors in order to develop stronger relationships with them. There are many technological components to CRM, but thinking about CRM in primarily technological terms is a mistake. The more useful way to think about CRM is as a process that will help bring together lots of pieces of information about customers, sales, marketing effectiveness, responsiveness and market trends.

CRM helps businesses use technology and human resources to gain insight into the behavior of customers and the value of those customers.

Using CRM, a business can:

• Provide better customer service
• Increase customer revenues
• Discover new customers
• Cross sell/Up Sell products more effectively
• Help sales staff close deals faster
• Make call centers more efficient
• Simplify marketing and sales processes