Paste MS Excel data to SQL Server

Question

I have a bunch of rows in Excel that I want to paste into a new table in MS SQL. Is there a simple way ?

Answer 1

The simplest way is to create a computed column in XLS that would generate the syntax of the insert statement. Then copy these insert into a text file and then execute on the SQL. The other alternatives are to buy database connectivity add-on's for Excel and write VBA code to accomplish the same.

Answer 2

If the interface works the way it did last I used it, you can select the region in Excel, copy it, open SQL Server and paste the data into the table as you would with Access.

Or you could setup an ODBC link between Excel and SQL Server.

Answer 3

I'd think some datbases can import data from CSV (comma separated values) files, wich you can export from exel. Or at least it's quite easy to use a csv parser (find one for your language, don't try to create one yourself - it's harder than it looks) to import it to the database.

I'm not familiar with MS SQL but it wouldn't suprise me if it does support it directly.

In any case I think the requrement must be that the structure in the Exel sheet and the database table is similar.

Answer 4

I have used this technique successfully in the past:

Using Excel to generate Inserts for SQL Server

Answer 5

why not just use export/import wizard in SSMS?

Answer 6

If you have SQL Server Management Studio, you can just Copy from Excel and Paste into the table in Management Studio, using your mouse. Just

1. go to the table you want to paste into,
2. select "Edit top 1000 rows", and then
3. right-click anywhere and select Paste.

Before you do this, you must match the columns between Excel and Management Studio. Also, you must place any non-editable columns last (right-most) using the Table Designer in Management Studio.

The whole procedure takes seconds and doesn't require any SQL statements.