Thursday, December 15, 2016

How to Import Excel or CSV data into SQL Server using SSDT

I have recently rebuilt my computer and decided to give SSDT a try.  This is SQL Server Data Tools.  I would use it instead of SQL Server Management Studio (SSMS).

It has been going really well so far.  I'm able to do all the normal things I'm used to.  Until now.  I wanted to import an Excel file into a table.  I've done it a million times in SSMS, but how do I do it in SSDT?

After some googling, I came up empty.  I should note that I am a C# developer.  I'm not using database projects.

Anyway, I figured out a really easy and almost ridiculous way to import the data.

Before you do this, make sure your database is backed up.  Only do this with small data sets.

  1. Open Visual Studio with SSDT.  To see if you have SSDT, look in your Help > About dialog and look in the list of Installed products.
  2. Open the SQL Server Object Explorer view.  (It's in the View menu).
  3. Connect to your DB and drill down to the table you want to import to.
  4. Right click the table and select View Data.
  5. Now go into Excel and select the data you want to import and hit our good old friend, CTRL-C or Copy.
  6. Switch back to Visual Studio, click on the row with all of the NULL values.  This row is intended for adding a single new row.  However, with the row selected, you can hit CTRL-V and paste all of the data into the new table.  Make sure the data fields line up.
  7. This will even work with IDENTITY columns.  Just make sure to have a column in Excel filled with zeros.
  8. That's it!  You might have to wait a while!
I should note that this is not the best plan if you have many rows of data.  If you have 1000 or less, this seems like a decent approach.  It may take a little while to import the data via copy/paste though.

No comments:

Post a Comment