SQL SERVER – Use INSERT INTO … SELECT instead of Cursor

This blog post is written in response to the post showing some of the worst practices of past. Well, just like last month’s theme, everybody learns by doing it one step at a time. In my case, I started my career as a network engineer and had no database knowledge during that time. I can still remember my old code which became quite a laughingstock when it was sent for a code review. This story is indeed interesting, so instead of writing shortly, I am going to write today in detail.

It happened about 8 years ago when I was working as Network Engineer in United States. I was responsible for a large data center and we had more than 40 servers under my watch. In our SQL Server database we had one table for the inventory of all the servers. On that table we used to keep a lot of details of the server. To keep it simple, I will say that we have only two columns in that table: ServerID and ServerName. I was asked to write a script which was intended to retrieve all the data from this table and insert them into the new table that was created by the database administrator. I thought I could handle SQL Server script well enough because I learned SQL a bit when I was younger. I ended up writing a script where I wrote cursor to SELECT single row from the table and insert into new table. Well, this went very fine. The script was taking a while to perform its tasks; nevertheless, it worked. I used this script for a short while.

Here is the script that I wrote. I am using sample and simple database for example. Please note that this is not a good practice.

-- Create Table and Populate with Sample Data
CREATE TABLE ServerTable (ServerID INT, ServerName VARCHAR(100))
INSERT INTO ServerTable (ServerID, ServerName)
SELECT 1, 'First Server'
UNION ALL
SELECT 2, 'Second Server'
UNION ALL
SELECT 3, 'Third Server'
-- Creating New Table
CREATE TABLE NewServerTable (ServerID INT, ServerName VARCHAR(100))
-- Insert Logic
DECLARE @Flag INT
SELECT
@Flag = COUNT(*) FROM ServerTable
WHILE(@Flag > 0)
BEGIN
INSERT INTO
NewServerTable (ServerID, ServerName)
SELECT ServerID, ServerName
FROM ServerTable
WHERE ServerID = @Flag
SET @Flag = @Flag - 1
END
SELECT
ServerID, ServerName
FROM NewServerTable
-- Clean up
DROP TABLE ServerTable
DROP TABLE NewServerTable

This script worked very well till we had changed in company policy. We then had a newly- appointed CTO (which I was about to become 3 years after this incidence), who introduced the requirement of Code Reviewing of all both old codes and new ongoing codes. When my code went to a code review, my Team Leader really laughed at my code. So he sent me new piece of the code which was much shorter and more efficient. The code which he proposed was as follows. I honestly say this was much better than my code.

-- Create Table and Populate with Sample Data
CREATE TABLE ServerTable (ServerID INT, ServerName VARCHAR(100))
INSERT INTO ServerTable (ServerID, ServerName)
SELECT 1, 'First Server'
UNION ALL
SELECT 2, 'Second Server'
UNION ALL
SELECT 3, 'Third Server'
-- Creating New Table
CREATE TABLE NewServerTable (ServerID INT, ServerName VARCHAR(100))
-- Insert Logic
INSERT INTO
NewServerTable (ServerID, ServerName)
SELECT ServerID, ServerName
FROM ServerTable
SELECT ServerID, ServerName
FROM NewServerTable
-- Clean up
DROP TABLE ServerTable
DROP TABLE NewServerTable

If you’ve noticed, the new insert code is just reduced to two lines only. Today you might think it is a very simple code, but for me it was a very new thing during those times. Now comes the best part of this whole story.

My team leader, who was very supportive and extremely positive person, asked me to demonstrate this whole incidence on our next team meeting. Honestly, I did not feel bad about accepting my mistake and learning new things. During the team meeting, I first accepted the fact that I was wrong with my code and thanked the Team Leader for giving me the opportunity to improve and develop myself. Because of his encouragement to accept my own mistake and improve from that incident, today I have reached this place.

Please get rid of cursors and use INSERT INTO…SELECT or SELECT INTO logic.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

About these ads

37 thoughts on “SQL SERVER – Use INSERT INTO … SELECT instead of Cursor

    • This is nice until the schema of NewServerTable changes. I like named inserts:

      insert into [NewServerTable]
      (
      ServerID,
      ServerName,
      )
      select ServerID, ServerName
      from [ServerTable]

      This way, the insert still works should the DB team reorder the columns in NewServerTable or add nullable columns between ServerID and ServerName.

      Great post, Pinal, however there are times where cursors are a better fit than selection inserts.

  1. Hi Pinal,

    I read this blog, to know ur improvements in ur knowledge and career.

    No one share their Negative points. But you only shared to show your simplicity.

    Really i proud of you.

  2. Thanks a lot sir! You have saved my blushes countless times. I’m humbled that you’d dare to share such a simple lesson you learnt years ago. Once again, thank you!

  3. Yes Pinal sir, very true, we learn from our mistakes.

    One more thing i whould like to add here
    we can also use ‘Select Into’ statement for the same purpose and it is much simpler

    Thanks,
    Santosh

    • You are right ! After googling I found out – there are some advantages and disadvantages as well, using ‘SELECT . INTO’ ahead of ‘INSERT INTO .. SELECT’ like it creates a new table if not already exists but with no constraints. Again for the former one you need to change your recovery model.

      But the former one is faster and can produce excellent result if you really need it for some specific situation.

  4. Pingback: SQL SERVER – Using RANKING Functions Instead of SQL Looping Logic of Cursor – Quiz – Puzzle – 8 of 31 « SQL Server Journey with SQL Authority

  5. In my three years of experience, I never used cursor. I used table variables, #tables to store data temprary using insert into statement. And after use of them, droped them(#tables)

  6. Pingback: SQL SERVER – SQL Server Misconceptions and Resolution – A Practical Perspective – TechEd 2012 India « SQL Server Journey with SQL Authority

  7. Hi Sir, this is karthik… I need to know , to update the row using by CURSOR… I have written some query but it doesnt work properly.. you just rectify the mistake and then send me the correct query…

    Thank you…

    ————————————————————————————————–

    create proc stt
    as
    declare @name varchar(50)
    declare @roll_no int
    set @roll_no=100

    declare stu_cursor cursor for
    select name,roll_no from xyz order by name

    open stu_cursor
    fetch next from stu_cursor into @name,@roll_no

    while @@FETCH_STATUS=0
    begin

    print @name
    print @counter
    update xyz set roll_no=@roll_no where name=@name
    set @roll_no=@roll_no+1
    fetch next from stu_cursor into @name,@roll_no
    end

    close stu_cursor
    deallocate stu_cursor
    —-

    • hi karthik
      this is your solution for sql query and execute it…
      frm jagan

      alter procedure hi
      as

      declare @emp_name varchar(50)
      declare @date date
      declare @sal int
      declare @emp_ad numeric

      declare emp1 cursor for
      select emp_name,date_of_joining,salary from emp order by emp_id

      open emp1
      fetch next from emp1 into @emp_name,@date,@sal
      set @emp_ad=100

      while @@FETCH_STATUS = 0
      begin
      print @emp_name

      update emp set emp_ad_id=@emp_ad from emp where emp_name=@emp_name
      select @emp_ad=@emp_ad+1
      print @emp_ad
      fetch next from emp1 into @emp_name,@date,@sal

      end

      close emp1
      deallocate emp1

    • hi karthik
      this is your solution for sql query and execute it…
      frm jagan
      ————————————–

      alter procedure hi
      as

      declare @emp_name varchar(50)
      declare @date date
      declare @sal int
      declare @emp_ad numeric

      declare emp1 cursor for
      select emp_name,date_of_joining,salary from emp order by emp_id

      open emp1
      fetch next from emp1 into @emp_name,@date,@sal
      set @emp_ad=100

      while @@FETCH_STATUS = 0
      begin
      print @emp_name

      update emp set emp_ad_id=@emp_ad from emp where emp_name=@emp_name
      select @emp_ad=@emp_ad+1
      print @emp_ad
      fetch next from emp1 into @emp_name,@date,@sal

      end

      close emp1
      deallocate emp1

  8. Hey..

    I want to insert data in new table and data are coming from two different tables which i already created so can you suggest me how can i copy data of two table in new table???

  9. Very good written article. It will be beneficial to everyone who employess it,
    including myself. Keep up the good work – looking forward to more posts.

  10. Thanks for the strategies presented. One thing I additionally believe is that credit cards supplying a 0% monthly interest often bait consumers with zero rate of interest, instant authorization and easy on-line balance transfers, but beware of the real factor that will certainly void your 0% easy road
    annual percentage rate as well as throw one out
    into the bad house rapidly.

  11. I believe that avoiding ready-made foods would
    be the first step so that you can lose weight. They will often taste beneficial, but
    prepared foods include very little nutritional value, making you eat more just to have
    enough strength to get through the day. In case
    you are constantly taking in these foods, transferring to whole grains and other complex carbohydrates will aid you
    to have more energy while eating less. Thanks alot : ) for your
    blog post.

  12. Its like you learn my thoughts! You seem to grasp a lot about this,
    like you wrote the book in it or something. I think that
    you could do with a few p.c. to force the message house a little bit, but other
    than that, this is wonderful blog. A great read.

    I will definitely be back.

  13. hello!,I really like your writing so much! share we keep in touch more about your post on AOL?
    I require an expert in this space to resolve my problem.
    Maybe that’s you! Taking a look ahead to see you.

  14. Thanks a lot for sharing this with all of us you really know what you are talking about!
    Bookmarked. Please also visit my website =).
    We could have a link exchange arrangement between us!

  15. Pingback: SQL SERVER – Weekly Series – Memory Lane – #041 | Journey to SQL Authority with Pinal Dave

  16. SELECT .. INTO and INSERT INTO … SELECT * are both bulk logged operations and can benefit from it if the recovery model is SIMPLE or BULK LOGGED

    BTW: Pinal, great blog.
    I’ve learned vey much from it within the last 5 years :)

  17. Though these are fast but create table lock. So in highly concurrent scenarios these does not scale. In scenario where high concurrent insert with bigger row set create problem if use build insert. In these scenarios cursor bases operation is better. So build operation is not always good. Any inputs?

  18. Hi Pinal,
    Need your guidance in solving below query
    -1. Current process has ONE MS Access Database table with all fields of information in it. Lets say STUDENTS ( has all info about students bunched togather)
    -2. Designed a new SQL server 2008 DB with all info grouped togather in resp tables- so there are more than one table now – Student, Personal, Courses, Teachers, Grades, Research
    -3. Need to migrate data from ONE old table and distribute it over into these multiple tables with ‘Student_id’ as primary key of Students table, and ‘Student_id’ – ref as Foreign Key in all other tables
    – But some columns from records of old table has data which needs to be checked for before inserting into new tables ( some data has been added to Static data – Such as Course list, so sourse name has to be one othe entires from COURSE_NAME table, while old data has some free text data inti so insert would fial on FK contraint)
    What is the best way to achieve this data migration into new table stucture?
    Thanks for (any) pointers

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s