Importing data by CSV | SQL

Let’s continue improving our SampleTest database, today by creating a new table and importing data to it by using a CSV file.

The new table will store the country details that we will use to relate with our customer table, already created on the post.

Schema creation…

Country table creation

Now, that we have our table schema, it’s time to import some data, I’ve created a CSV file with the following rows…

and to perform the data importation the following script:

Above we are invoking a Bulk Insert for our new table Country and the data used for insertion is the one stored on our CSV file. Afterward, on our With clause we are configurating some settings to be considered during the data reading on CSV file, although I’m considering that the settings are quite easy to understand, I would like to highlight the FIRSTROW, where we are saying that due to our column headers on the file, should only considered the data from the 2nd row on.

Time to see if the data ended as expected…

As the last step, let related now this new table with our Customer table, by CountryCode field. For that, we will use the ALTER TABLE statement where we’ll add a Constraint, Foreign Key and we’ll referrer for the field we want on table Country.

Creating Relation

Time to test this new relation. I’ll now add a row with a non-existent country and see if an error is now invoked as supposed.

The new relation is now blocking non-existent countries on Country table for the Customer

Now a test with an existent country…

And that’s it. With the above importation, we can easily add data that can be used to relate with our existent table Customer. Time to try by yourself and let me know the results.

Stay safe!