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

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 200 Rows".
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 (to set-up and start - not necessarily to execute) and doesn't require any SQL statements.

Regarding empty database tables and SSMS v18.1+.

Answer 2

I have used this technique successfully in the past:

Using Excel to generate Inserts for SQL Server

(...) Skip a column (or use it for notes) and then type something like the following formula in it:

="insert into tblyourtablename (yourkeyID_pk, intmine, strval) values ("&A4&", "&B4&", N'"&C4&"')"

Now you’ve got your insert statement for a table with your primary key (PK), an integer and a unicode string. (...)