Solved

Insert an excel file to an existing sql server datatable

Posted on 2012-03-15
3
438 Views
Last Modified: 2012-03-16
Goal: Inserting an excel file to an existing sql server datatable

By google search, there are some code for importing an excel file to a datatable. But it may infer an empty datatable, if I understand it correctly.



So please help me a little bit of C# code.
SQL Server 2008 and C# 4.0/3.5.

Thanks.
0
Comment
Question by:zhshqzyc
3 Comments
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 250 total points
ID: 37726484
--Use a Linked Server
--To simplify queries, you can configure an Excel workbook as a linked server in SQL Server. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
--Q306397 HOWTO: Use Excel with SQL Server Linked Servers and Distributed Queries
--The following code imports the data from the Customers worksheet on the Excel linked server "EXCELLINK" into a new SQL Server table named XLImport1:
SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]
--You can also execute the query against the source in a passthrough manner by using OPENQUERY as follows:
SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,
    'SELECT * FROM [Customers$]')


--Use Distributed Queries
--If you do not want to configure a persistent connection to the Excel workbook as a linked server, you can import data for a specific purpose by using the OPENDATASOURCE or the OPENROWSET function.
--The following code samples also import the data from the Excel Customers worksheet into new SQL Server tables:
SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')




ALL IN ONE!!!

How to import data from Excel to SQL Server
http://support.microsoft.com/kb/321686

How to use Excel with SQL Server linked servers and distributed queries
http://support.microsoft.com/kb/306397

openrowset excel 2007
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/23e7b80d-c7ec-4875-bd3a-aa137fd95dfb
0
 

Author Comment

by:zhshqzyc
ID: 37726700
The sql table is an existing one. I want to append excel data to the table. If I use import method, I worry that the old table will be overwritten.
0
 
LVL 3

Accepted Solution

by:
tsnirone earned 250 total points
ID: 37728850
Hi,

I would import the new data to a #temp table (either by using "select into" or rather bcp which is faster), then insert the data to the existing table.

Do however consider the amount of data you're inserting. Reason is that "select into" is not logged in the same way as regular "mass-inserts".

Good luck!

:)
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question