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.
- 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.
- Open the SQL Server Object Explorer view. (It's in the View menu).
- Connect to your DB and drill down to the table you want to import to.
- Right click the table and select View Data.
- Now go into Excel and select the data you want to import and hit our good old friend, CTRL-C or Copy.
- 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.
- This will even work with IDENTITY columns. Just make sure to have a column in Excel filled with zeros.
- 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.