Sql loop Statement

shaalini · #1 (**permalink**) 04-25-2008, 02:30 AM

Hi,
How to fetch row by row in sql server without using cursor

poornima · #2 (**permalink**) 04-26-2008, 12:25 AM

Hi Shaalini,
Could u explain ur need in detail?
How u want the records to be displayed explain with sample eg?

shaalini · #3 (**permalink**) 04-26-2008, 12:59 AM

Hi poornima
for ex: select * from users it return multiple rows.
I have to get each row user id and perform some insert and update function
Just like for loop function.

Falcon · #4 (**permalink**) 05-02-2008, 10:44 PM

Hi

We can archive the Loop statement in SQL. Here is some Example
For SQL Server

Code:

DECLARE @CNT INT
DECLARE @Q NVARCHAR(1000)

SET @CNT = 100
WHILE(@CNT < 1000)
BEGIN
    SET @Q = 'Insert into infostorageCompanies
    Values(' + CAST (@CNT AS NVARCHAR) + ',' + '''testCompany_9''' + ',' + '''''' + ',' + '''''' + ')'
    PRINT @Q
    EXEC sp_executesql @Q
    SET @CNT = @CNT + 100
END

For MS SQL

Code:

declare @i int
while (@i<10)
begin
insert into dbname.dbo.table_name values @i
@i=@i+1
end

For MySql

Code:

declare @i int
while (@i<10)
begin
insert into dbname.dbo.table_name values @i
@i=@i+1
end

Regards
Falcon

shaalini · #5 (**permalink**) 05-04-2008, 11:04 PM

Hi Falcon,
I have my table structure like this
Rec
RecId Name Description
1 Chicken chicken
2 Pizza Pizza

and other table as RecItem

RecItemId ReciId ItemId
1 1 23
2 1 24
3 1 26
4 2 43
5 2 52
I have to clone both these rec and recitem and i have to get result as follow
Rec
RecId Name Description
1 Chicken chicken
2 Pizza Pizza
3 chicken chicken
4 pizza pizza

RecItem

RecItemId ReciId ItemId
1 1 23
2 1 24
3 1 26
4 2 43
5 2 52
6 3 23
7 3 24
8 3 26
9 4 43
10 4 52

How to perform without using cursor

bluesky · #6 (**permalink**) 05-05-2008, 09:01 PM

Hi shaalini.
Can u try with the following query

DECLARE @MaxRecId as INT
SELECT @MaxRecId = MAX(RecId )
FROM Rec

SET IDENTITY_INSERT Rec ON
INSERT INTO Rec (RecId,[Name],Description)
SELECT RecId = RecId + @MaxRecId ,
[Name] = [Name],
Description= Description,
FROM Rec
SET IDENTITY_INSERT Rec OFF

INSERT INTO RecItem(RecId,ItemId)
SELECT RecId = RecId + @MaxRecId ,
ItemId= ItemId
FROM RecItem

shaalini · #7 (**permalink**) 05-06-2008, 02:26 AM

Hi, blue sky its working fine. but can u explain about the SET IDENTITY_INSERT

bluesky · #8 (**permalink**) 05-06-2008, 02:28 AM

hi,
Identity_Insert
Allows explicit values to be inserted into the identity column of a table.
SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }
database_name

Is the name of the database in which the specified table resides.

schema_name

Is the name of the schema to which the table belongs.

table

Is the name of a table with an identity column.

bluesky · #9 (**permalink**) 05-06-2008, 02:30 AM

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server 2005 returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

shaalini · #10 (**permalink**) 05-06-2008, 02:37 AM

Hi blue sky,
Thanks for your information. But as of now my requirement i have to clone the rec based on some condition.when i follow the code which u have mentioned above i m getting the following error Violation of PRIMARY KEY constraint

nnraja · #11 (**permalink**) 05-06-2008, 10:06 PM

Hi shaalini,
Provide the table structure for ur new requirement

shaalini · #12 (**permalink**) 05-06-2008, 10:15 PM

Hi nnraja
Rec
RecId Name Description locationId
1 Chicken chicken 1
2 Pizza Pizza 1
3 chickenbreast chickenbreast 2

and other table as RecItem

RecItemId ReciId ItemId locationId
1 1 23 1
2 1 24 1
3 1 26 1
4 2 43 1
5 2 52 1
6 3 23 2
7 3 42 2
I have to clone both these rec and recitem and i have to get result as follow
Rec
RecId Name Description
1 Chicken chicken
2 Pizza Pizza
3 chicken chicken
4 pizza pizza

RecItem

RecItemId ReciId ItemId
1 1 23
2 1 24
3 1 26
4 2 43
5 2 52
6 3 23
7 3 24
8 3 26
9 4 43
10 4 52
This is my table structure i have to clone based on the locationId

bluesky · #13 (**permalink**) 05-08-2008, 09:14 PM

DECLARE @LocationSource INT --The location we want to clone
DECLARE @LocationTarget INT --The newly cloned location

--===== Identify the loaction numbers involved in the cloning
SET @LocationSource = 1
SET @LocationTarget = 4

--===== Start a transaction because this all has to happen together
BEGIN TRANSACTION

--===== Get ALL the information we need at once including the new recipe ID's
;WITH
cteKnowAll AS
(
SELECT RecNumber = DENSE_RANK() OVER (ORDER BY s.RecId),
oldRecId =s.RecId,
s.[Name],
OldLocationID = s.LocationID,
NewLocationID = @LocationTarget,
i.ItemId
FROM Rec WITH (TABLOCKX), --Lock the table so no one can insert/update/delete
RecItem WITH (TABLOCKX) --Lock the table so no one can insert/update/delete
WHERE s.recId= s.recId
AND s.LocationID = @LocationSource
)
SELECT NewrecId = k.RecNumber +IDENT_CURRENT('rec'),
[Name] = k.[Name],
NewLocationID = k.NewLocationID,
itemId = k.ItemId
INTO #Clones
FROM cteKnowAll k

--===== Clone the rec
SET IDENTITY_INSERT recON
INSERT INTO rec
(recId,[Name],LocationID)
SELECT DISTINCT
NewrecId ,[Name],NewLocationID
FROM #Clones
SET IDENTITY_INSERT rec ON

--===== Clone the rec items
INSERT INTO recItem
(recId,ItemId)
SELECT DISTINCT NewrecId ,ItemId
FROM #Clones

--===== If we made it to here, we can commit
COMMIT

--===== Verify the original condition of the test tables
SELECT * FROM rec
SELECT * FROM recItem

shaalini · #14 (**permalink**) 05-13-2008, 12:45 AM

hi bluesky,
I have followed the same procedure as u have mentioned. Its working fine. If i execute the code more than once i got the error as There is already an object named '#Clones' in the database.So i have changed the '#Clones' in to different name when each and every time it executes.

bluesky · #15 (**permalink**) 05-13-2008, 12:48 AM

Hi shaalini,
Not quite the way to do it... if you convert the code to a stored procedure and run it, #Clone will drop itself at the completion of the stored procedure, automatically. You're trying to run it more than once as a simple script and you need to forcibly drop #Clone for additional runs

bluesky · #16 (**permalink**) 05-13-2008, 12:51 AM

hi shaalini,
Try with the following code

--===== If the clone table already exists, drop it...
IF OBJECT_ID('TempDB..#Clone','U') IS NOT NULL
DROP TABLE #Clones
DECLARE @LocationSource INT --The location we want to clone
DECLARE @LocationTarget INT --The newly cloned location

--===== Identify the loaction numbers involved in the cloning
SET @LocationSource = 1
SET @LocationTarget = 4

--===== Start a transaction because this all has to happen together
BEGIN TRANSACTION

--===== Get ALL the information we need at once including the new recipe ID's
;WITH
cteKnowAll AS
(
SELECT RecNumber = DENSE_RANK() OVER (ORDER BY s.RecId),
oldRecId =s.RecId,
s.[Name],
OldLocationID = s.LocationID,
NewLocationID = @LocationTarget,
i.ItemId
FROM Rec WITH (TABLOCKX), --Lock the table so no one can insert/update/delete
RecItem WITH (TABLOCKX) --Lock the table so no one can insert/update/delete
WHERE s.recId= s.recId
AND s.LocationID = @LocationSource
)
SELECT NewrecId = k.RecNumber +IDENT_CURRENT('rec'),
[Name] = k.[Name],
NewLocationID = k.NewLocationID,
itemId = k.ItemId
INTO #Clones
FROM cteKnowAll k

--===== Clone the rec
SET IDENTITY_INSERT recON
INSERT INTO rec
(recId,[Name],LocationID)
SELECT DISTINCT
NewrecId ,[Name],NewLocationID
FROM #Clones
SET IDENTITY_INSERT rec ON

--===== Clone the rec items
INSERT INTO recItem
(recId,ItemId)
SELECT DISTINCT NewrecId ,ItemId
FROM #Clones

--===== If we made it to here, we can commit
COMMIT

--===== Verify the original condition of the test tables
SELECT * FROM rec
SELECT * FROM recItem

shaalini · #17 (**permalink**) 05-13-2008, 12:56 AM

Hi bluesky.
Its working fine as the result i expected.thanx bluesky

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Loop in Mysql	Murali	Database Support	0	02-12-2008 05:37 AM
Statement Coverage	vigneshgets	Software Testing	2	01-24-2008 02:26 AM
Mysql - what is the use of DO statement?	write2ashokkumar	Database Support	1	08-14-2007 12:34 AM
How can i do loop testing manually?	sundarraja	Software Testing	1	07-25-2007 12:02 AM
Javaa:Tutorial - The Loop	pranky	Java Programming	0	02-23-2007 11:58 PM