Importing and Exporting bulk data using Sql Server 2005 using openrowset and opendatasource

For Bulk Import and Export Data in SQL Server 2005 mainly we will use

1) OPENDATASOURCE
2) OPENROWSET

Types of Error Message while Excecuting Bulk Queries:

Exporting Data SQL server Data to Excel:

Initially I tried to Excecute the OPENROWSET for Inserting the values from Excel Sheet to SQL server.

My Excel sheet is on My D:Malli Queries Folder and my Excel name is Book1.xls.

Then I Excecuted the following Query

INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database= D:\Malli queries\Book1.xls;',
'SELECT * FROM [Sheet1$]')
SELECT * from mandalmaster

But this has given the following Error


Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


This information in not correctly helpfull for me.From the above Error message I created a linked server for Excel where my Excel File Path is there.Then I Excecuted,Even though it was not worked for me.I found that my Excel was opened.then I close my Excel and Run the same Query.It shows the following Error Message.This above type error also thrown if we use Excel 2007 instead of Excel 2003 even though we closed this Excel.This Type of Exception also occur if the Filename (db) not found or spell mistake.


Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

I Recognised that I had not given the Comun Names in Excel.No of Columns Must match with no of columns given in Select Statement.Then I have Given the Column names.Then The above query Exceuted Sucessfully.

OPENDATASOURCE is also working for inserting the bulk data as same the openrowset.but with different syntax as shown below.

insert into OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\Test.xls;Extended Properties=Excel 8.0') ... Sheet2$
SELECT * from mandalmaster



For Getting Excel Data to Sql Server:

For getting Data from Excel and to Display in SQL server using opendatasource.

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=D:\Malli queries\Book1.xls;Extended Properties=Excel 8.0')...Sheet4$

Same Data will be getting by Excecuting this query as OPENROWSET.

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\Malli queries\Book1.xls', Sheet4$)


For Importing Excel Data to Sql Server:


For inserting Excel data into Sql server.

Suppose I have District Data in Excel then I have to import Data
From Excel to Sql Server then this query will insert the data into SQL server.

Using OPENROWSET :

use MYdatabase;
insert into dbo.DistrictMaster
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\Test.xls', Sheet2$)

Here My Bulk Data is in Excel, which is in C:\Test.Xls.
Where Sheet2 is the Worksheet name.
Where MyDatabase is the Database name. And dbo.DistrictMaster is table that the Data to be inserted.
Make sure that the Datatype length should be Sufficient length.other wise it will through an exception.
Msg 8152, Level 16, State 2, Line 1
String or binary data would be truncated.
The statement has been terminated.
This Message shows that one of our column length should be less than the inserted column data.
Even though We can also Directly copy & Paste the Excel data into sql server this is better and faster.
Using OPENDATASOURCE:
OPENDATASOURCE also used for inserting bulk data into excel.
insert into dbo.m_Mandal
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\Test.xls;Extended Properties=Excel 8.0')...Sheet2$
Where sheet2 is the Source table from excel sheet.
If the inserting table(dbo.m_Mandal.ditrictid) have relation ship with the foreign key table(district.districtid). If the foreign key table has no data(districtid) then it will thrown excption.

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_m_mandal_m_district". The conflict occurred in database "MyDatabase", table "dbo.m_district", column 'districtid'.
The statement has been terminated.

Inorder to avoid this type of exception ensure that the foreign key table(district) has the data corresponds to (district.districtid).

With out creating a table in sql server we can inporting using
SELECT into statement as shwon below
This will create a new table(Tablenameuwantto) with Excel Data in Database.

SELECT * into [DetinationDatabase].[Tablenameuwantto]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\Malli queries\Book1.xls', Sheet4$)

For Copying Data from one table to another table in the same server from two different databases in SQL Server 2005:

Two ways we can import data from one table to another table in SQL server 2005.

Insert .... Select

Select ... into

Insert … Select:

The query fro insert .. select as shown below.

insert into DestinationDatabase.District(districtid,districtname,stateid)
select districtid,districtname,stateid from SourceDatabase.[dbo].Districtmaster
In the above we copy data from SourceDatabase.[dbo].Districtmaster table to DestinationDatabase.District table.where SourceDatabase and DestinationDatabase are two different databases.
This is only for inserting into empty table.if we inserting into table which has already data and if the destination has primary key and we want o insert duplicate table the it show error message.

Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK__district__1CF15040'. Cannot insert duplicate key in object 'TelemedMain.District'.
The statement has been terminated.

Inorder to copy bulk data we want to check whether duplicate data is allowed.and also check if there is any identity column.other wise it will thrown error.

Msg 544, Level 16, State 1, Line 2
Cannot insert explicit value for identity column in table 'district' when IDENTITY_INSERT is set to OFF.

Then we manully set identity off we want to add explicit column to identity column.Generally primary key comuln is the identity column.set identity off as shown.Change Yes to No as shown in circle.


Other wise not supply explicit value to the identity column.

Select Into:
This is the second way we can insert bulk data from one table to another table.
select * into [DestinationDatabase].[dbo].[District]
from [SourceDatabase].[dbo].Districtmaster

But there is difference between the two statements.


Using Insert ..Select you must have Destination table to copy the Source table data where as using Select ..Into you do not need Detination table it will automatically create a new table and copy the data.