Inserting Bulk Data

One of the most common requirements of applications is to "transfer" some amount of data from one table into another table. There are many ways to do this and the most often that I've seen developers write is via cursors, which is very slow!! Most often, people tend to write this kind of code because of their background in structured programming. For example, assuming that you had to do this in an application tool (like VB), the steps you would follow are:
  • Get all the rows from the source
  • For each row in the source
  • Insert data into the destination
  • Get next record from the source and goto (3)

This is precisely what the cursor code in SQL Server would also do, but there are other efficient ways to this in SQL Server. Here is one method:


INSERT INTO newAuthors (au_id, au_lname, au_fname)
	SELECT au_id, au_lname, au_fname FROM authors

In the above statement, we use one variation of the INSERT statement that allows insertion from the output of a SELECT statement. This statement will transfer all the rows into the new table in one go, thus making it faster than the cursor solution.

Home