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)
37 Comments. Leave new
Hi Pinal, both the codes are same .
We can make this even simple by using Select Into…
SELECT ServerID, ServerName into NewServerTable
FROM ServerTable
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.
I read this topic and I think I lost my time.
Conclusion, you don’t be a genius to be a sql guru :D …
Very nice that you show your own example.
Great job Pinal!
hi pinal,
i am java core developer,i like very much your blog
kkmishra
iNdia
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.
and now You are the Team Leader!
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!
You can refer the this also
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.
Very useful to freshmen like me. Thanks for sharing.
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)
Nice work.
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
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???
It’s an awesome paragraph in favor of all the web users; they will get advantage from it I am sure.
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.
Pinal Dave, I appreciate your straightforward and modest approach to everything. You are a good man.
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.
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.