Import Data into Sql Server From Excel Spreadsheet

I have an Excel sheet now I need to insert data from that sheet into SQL Server 2008.

I need to create a temp table and insert data into that table. The database name is Employee
Can you pls provide me the syntax for achieving it.

A simple search: http://support.microsoft.com/kb/321686 Probably easiest is SELECT * INTO #tmptable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\xltest.xls', [SheetName$])

 

 Apart from using DTS and Export wizard, we can also use this query to export data from SQL Server2008 to Excel & Excel to Server 2008

Create an Excel file named testing having the headers same as that of table columns and use these queries

1 Export data to existing EXCEL file from SQL Server table

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;', 
    'SELECT * FROM [SheetName$]') select * from SQLServerTable

2 Export data from Excel to new SQL Server table

select * 
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [Sheet1$]')

3 Export data from Excel to existing SQL Server table

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [SheetName$]')

4 If you dont want to create an EXCEL file in advance and want to export data to it, use

EXEC sp_makewebtask 
 @outputfile = 'd:\testing.xls', 
 @query = 'Select * from Database_name..SQLServerTable', 
 @colheaders =1, 
 @FixedFont=0,@lastupdated=0,@resultstitle='Testing details'

(Now you can find the file with data in tabular format)


5 To export data to new EXCEL file with heading(column names), create the following procedure

create procedure proc_generate_excel_with_columns
(
 @db_name varchar(100),
 @table_name varchar(100), 
 @file_name varchar(100)
)
as

--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select 
 @columns=coalesce(@columns+',','')+column_name+' as '+column_name 
from 
 information_schema.columns
where 
 table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)

--Delete dummy file 
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)

After creating the procedure, execute it by supplying database name, table name and file path

EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'

Import Data into Sql Server From Excel Spreadsheet using SQLBulkCopy class

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.OleDb;

using System.Data.Common;

using System.Data.SqlClient;

namespace WindowsFormsApplication1

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        private void button1_Click(object sender, EventArgs e)

        {

            // Connection String to Excel Workbook

            string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Book1.xls;ExtendedProperties=""Excel 8.0;HDR=YES;"";IMEX=1;";

            // Create Connection to Excel Workbook

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))

            {

                OleDbCommand command = new OleDbCommand("Select ID,Data FROM [Data$]", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet

                using (DbDataReader dr = command.ExecuteReader())

                {

                    // SQL Server Connection String

                    string sqlConnectionString = "Data Source=.;Initial Catalog=Test;Integrated Security=True";

                    // Bulk Copy to SQL Server

                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))

                    {

                        bulkCopy.DestinationTableName = "ExcelData";

                        bulkCopy.WriteToServer(dr);

                    }

                }

            }

        }

    }

}