Paste MS Excel data to SQL Server
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
- Go to the table you want to paste into.
- Select "Edit Top 200 Rows".
- 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+.
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 thefollowing formula in it:
="insert into tblyourtablename (yourkeyID_pk, intmine, strval) values ("&A4&", "&B4&", N'"&C4&"')"
Now you’ve got your insert statement fora table with your primary key (PK), an integer and a unicode string. (...)
Excel
- In Excel, highlight and copy the data you want to paste into SQL.
SQL
- Create the table with the desired column names and give you table a name.*Make sure
Identity Specification
is Yes, so it will auto increment your Identity column. - Find your table, and right click on it and choose
Edit Top 200 Rows
from the dialog-box. - Right click on the empty row with the * sign and select paste from the dialog-box