Importing Excel data into MS SQL Server
I recently had to import a bunch of data from Excel into MS SQL Server. I kept getting failures, because I orignially tackled the problem by first designing and creating the table, then trying to import into my existing table. This was a pain in the neck. So what I decided to do was import the data and let it the table get automatically created. Then I edited the table to my liking truncated and re-imported. The step I took are found below.
Importing Excel data into SQL (if you are creating new tables.)1) Get your data ready in Excel, exactly how you want to see it, including an ID column if that is going to act as your unique identifier.
Example:
2) Next do an import using Microsoft SQL Server Managment Studio
3) When you get to this page…make sure the Destination table is named exactly like you will want it. This will save you from having to edit later
If you have an ID column that you want to auto increment…change it from
[ID] [INT] NOT NULL,
to
[ID] [int] IDENTITY(1,1) NOT NULL,
4) Now copy your finalized create statement.
5) Drop the original table you just created.
6) Run the edited Create Statement to recreate an empty table
7) Rerun the import but now point it specally to the empty table as the destination.
