I'm trying to copy data from Excel
into a new table in SQL Server
.
The SQL code I'm using works in Server Manager, copying data into a newly created table & does seem to work here, except the new table doesn't appear in my Database and running the code again seems to produce an "Table already exists"
error.
Any ideas about what I'm doing wrong?
Code as follows:
try
{
string sqlConnectionString = string.Format("Data Source=XXXX\\SQLEXPRESS;Initial Catalog=DbName;" + "Integrated Security=SSPI; User Instance=False");
string excelConnectionString = string.Format("provider=Microsoft.ACE.OLEDB.12.0;Data Source =C:\\user\\ExcelFileName.xlsx; Extended Properties ='Excel 12.0 xml; HDR=YES;IMEX=1'");
using (SqlConnection SQLconnection =
new SqlConnection(sqlConnectionString))
using (OleDbConnection connection =
new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand
("SELECT * INTO dbo.newTable FROM [SheetName$];", connection);
connection.Open();
using (OleDbDataReader dr = command.ExecuteReader())
using (SqlBulkCopy bulkcopy =
new SqlBulkCopy(SQLconnection))
{
bulkcopy.DestinationTableName = "dbo.newTable";
bulkcopy.WriteToServer(dr);
Console.WriteLine("The data has been exported from Excel to SQL.");
Console.ReadLine();
}
connection.Close();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.ReadLine();
}
dbo.
as a prefix for table name? – Yuriy Galanter Sep 16 '13 at 14:40