SQL SERVER – Use INSERT INTO … SELECT instead of Cursor

SQL SERVER - Use INSERT INTO ... SELECT instead of Cursor TSQLWednesday 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 (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – The SQL Hands-On Guide for Beginners – Book Available for SQL Server Certification
Next Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Aggregates with the Over Clause – Day 10 of 35

Related Posts

37 Comments. Leave new

  • Hi Pinal, both the codes are same .

    Reply
  • We can make this even simple by using Select Into…

    SELECT ServerID, ServerName into NewServerTable
    FROM ServerTable

    Reply
    • Ian Westerfield
      January 25, 2012 12:05 am

      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.

      Reply
  • I read this topic and I think I lost my time.
    Conclusion, you don’t be a genius to be a sql guru :D …

    Reply
  • Very nice that you show your own example.

    Great job Pinal!

    Reply
  • hi pinal,
    i am java core developer,i like very much your blog
    kkmishra
    iNdia

    Reply
  • Kalyanasundaram.K
    August 12, 2011 6:23 pm

    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.

    Reply
  • and now You are the Team Leader!

    Reply
  • 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!

    Reply
  • Varinder Sandhu
    August 16, 2011 11:25 am

    You can refer the this also

    Reply
  • 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

    Reply
    • Suvendu Shekhar Giri
      October 16, 2011 12:51 pm

      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.

      Reply
  • Very useful to freshmen like me. Thanks for sharing.

    Reply
  • 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)

    Reply
  • Nice work.

    Reply
  • 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
    —-

    Reply
    • 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

      Reply
    • 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

      Reply
  • 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???

    Reply
  • @Hardik, Refer link

    https://blog.sqlauthority.com/2007/08/15/sql-server-insert-data-from-one-table-to-another-table/

    Read this wonderful article and I’m sure you will get your answer!!

    Reply
  • It’s an awesome paragraph in favor of all the web users; they will get advantage from it I am sure.

    Reply
  • 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.

    Reply
  • Pinal Dave, I appreciate your straightforward and modest approach to everything. You are a good man.

    Reply
  • 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.

    Reply

Leave a Reply

Menu