Query for importing datas from XL to SqlServer DB

No.of Views	:	613
Bookmarked	:	0 times
Downloads	:
Votes	:	0

By vinothnat On 15 Feb 2010 22:02:37

Tag : Sql Servers , T-SQL

Skill Level: Intermediate

Images in this article missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at info@codegain.com

Here is the simple query for importing data's from Excel sheet to SqlServer Data base.

Just use the following query in your sqlserver query window.

{codecitation class="brush:sql; gutter: false;" width="600px"}

INSERT INTO [dbo].[TableName] ([FieldName1]
,[FieldName2]
,[FieldName3]
,[FieldName4]
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\db.xls;Extended Properties=Excel 8.0')...[SheetName$]

{/codecitation}

Note: Above query support Excel sheet 2003 format, if you need import from the MS Excel 2007 you need change data source like followings.

{codecitationclass="brush: sql; gutter: false;" width="600px"}

Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

{/codecitation}

that's is...

...S.VinothkumaR.

About Author

vinothnat

Occupation	-	Not Provided
Company	-	Not Provided
Member Type	-	Junior
Location	-	Not Provided
Joined date	-	03 Jun 2009
Home Page	-	Not Provided
Blog Page	-	Not Provided


Other popularSectionarticles
Use of Rank() function in SQL Server Use of Rank() function in SQL Server Published Date : 15/Feb/2010 Use of Dense_Rank() function in SQL Server Use of Dense_Rank() function in SQL Server Published Date : 15/Feb/2010 How to Get First,Last and No of Days in Month Using T-Sql I have read few questions in forums, about how to select first, last date month and how many days in a month. In my project had used some query to perform these tasks. I will share those with article. Published Date : 17/Dec/2010 How to select random records in SQl Server How to select random records in SQl Server Published Date : 15/Feb/2010 how to get how many records entered in day by a user in sql server. how to get how many records entered in day by a user in sql server. Published Date : 15/Feb/2010

Comments

There is no comments for this articles.