Import & Export Data – Part 1

There are not many real life databases that do not import data from external sources or do not export data for delivery to external targets. I am going to devote a series of talks on different ways to perform these functions.

The INSERT statement is the easiest way to load small sets of static information into a database. We will be working again with the Boy Scouts of America (BSA) hypothetical database.

The first example loads the rank table with a data. We can see the auto-increment surrogote key is incremented.

On interesting thing about an identity column is that seed value is not reset with the DELETE statement. The example below shows the second set of inserts are given values of 11 to 20.

On the other hand, the TRUNCATE TABLE statement does reset the seed value of the table. The example below shows the third set of inserts which are given values 1 to 10.

Importing data by using INSERT statements is a good technique for small sets of static data. The sqlcmd utility can be used to automate the execution of these statements from a batch program.

With the FULL recovery model, every INSERT statement has an entry sent to the transaction log. Because of this overhead, loading large amounts of data with INSERT statements is not recommended.

Next time, I will be talking about the BULK INSERT statement that can be used to load large amounts of data.

Related posts

5 Thoughts to “Import & Export Data – Part 1”

  1. Keep working, nice post! This was the information I had to know.

  2. wonderful post, very informative. I wonder why the other specialists of this sector do not notice this. You should continue your writing. I’m confident, you’ve a huge readers’ base already!

  3. I have been surfing online more than 3 hours today, yet I never found any interesting article like yours. It is pretty worth enough for me. In my opinion, if all site owners and bloggers made good content as you did, the net will be much more useful than ever before.

  4. A lot of thanks for all your valuable efforts on this web page. Debby enjoys participating in research and it is easy to see why. Most people know all of the compelling means you give vital strategies via this web site and as well cause participation from website visitors on that matter plus my child is being taught a whole lot. Take advantage of the remaining portion of the new year. You have been performing a stunning job.

  5. I just added this feed to my bookmarks. I have to say, I very much enjoy reading your blogs. Keep it up!

Leave a Comment