Paste MS Excel data to SQL Server
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 ?
I have used this technique successfully in the past:
(...) 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. (...)
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 1000 rows", and then
- 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.