RSS

Use insert into select instead of cursor

01 Sep

In this article I will explain by code how can you make cursor to select data from table and insert it into another table but this to learning but if you want to insert data from Table to another Table I suggest to use Insert into select instead of Cursor

— 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

Remarks

To see what happens when you run this script enter F11 to open Debug Query and enter F10 to see effect step by step

 
Leave a comment

Posted by on September 1, 2011 in Transact-SQL

 

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