Posted on Leave a comment

Import CSV to SQL Server Database

Quick steps to importing a csv to a sql server database. There are a couple first steps incase you have an existing database that you should make a backup copy of first or you can skip right to step number 3 which highlights the import itself.

1. Creating a backup of existing table

The code to take a table and back it up in case you do something wrong!

SELECT *
INTO tbl_mytable20070110
FROM tbl_mytable

TRUNCATE TABLE tbl_mytable;

That will take all of your existing table data and put it in a safe backup file. The truncate function then erases all of your tbl_mytable data, ready for the new data to be entered.

2. Conforming the CSV to date compliancy

Ensure the dates of your csv are in yyyy-mm-dd format to avoid any global date format issues.

3. Import the CSV data to the SQL database

  • Open SQL server and right click on the database table section.
  • Choose All Tasks > Import Data > Next
  • Choose Text from the drop down as the import file type and then browse to the location of your CSV file.
  • Click for larger example of choosing text

  • Ensure delimited option is chose and that ‘first row has column names is ticked. Hit next
  • Click for larger example of choosing file format

  • Choose Comma delimited and hit next.
  • For the destination source, make sure Microsoft OLE DB Provider for SQL Server is chosen. I used the SQL server authentication. Choose the Database you wish to import the data too and hit next.
  • Click for larger example of choosing destination

  • In the source tables and views window, select destination and choose the tbl_mytable that you will be importing the data to.
  • Click transform (the … button)
  • Ensure that all of the Source items correctly match the Destination. Change as necessary.
  • Click for larger example of column mapping

  • Un-tick Enable identity insert and hit OK.
  • Choose run immediately if not already chosen and hit next and then finish.

Now this is the way that some CSV imports will work, you may have to adjust if you do not use column names in your excel/CSV document for example.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.