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.
- Ensure delimited option is chose and that ‘first row has column names is ticked. Hit next
- 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.
- 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.
- 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.