June 7, 2016 at 8:00 am
I am fairly new to T-SQL and have some experience in Oracle PL/SQL.
Here is the case:
I have to read records sequentially from some table in MSSQL database and (on some condition) do something with read data.
In Oracle I use following code:
begin
for i in (select ID, Name from table where ID between 1 and 3)
loop
dbms_output.put_line(i.Name); --simplified example
end loop;
end;
In this case there will be written 3 names in the console window.
How can I do the same in T-SQL, in simplest code?
Thanks in advance,
Tom
----------------------------------------
I miss SQL Server so much. Now I'm working on Oracle and hate it
June 7, 2016 at 8:08 am
This type of processing is referred to as RBAR (Row By Agonizing Row), because it is a HORRIBLY inefficient approach in the vast majority of cases. Why don't you tell us what you're actually trying to accomplish, and we'll help you figure out if it can be done with a set-based approach instead.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 7, 2016 at 8:09 am
Sounds like your after a *shivers* Cursor.
Would this do the job?
Create Table #Loop (ID int, Name varchar(20))
Insert into #Loop
VAlues (1, 'Steve'),
(2, 'Jane'),
(3, 'George'),
(4, 'Harold')
Declare @Name varchar(20)
Declare Loop_Cursor Cursor for
Select Name
from #loop
where id between 1 and 3
Open Loop_Cursor
Fetch NEXT from Loop_Cursor INTO @Name
While @@FETCH_STATUS = 0
Begin
print @name
Fetch NEXT from Loop_Cursor INTO @Name
End
Close Loop_cursor
Deallocate Loop_cursor
Drop table #Loop
drew.allen (6/7/2016)
This type of processing is referred to as RBAR (Row By Agonizing Row), because it is a HORRIBLY inefficient approach in the vast majority of cases. Why don't you tell us what you're actually trying to accomplish, and we'll help you figure out if it can be done with a set-based approach instead.Drew
Of course, as Drew is said, doing this set-based would be a much better idea.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 7, 2016 at 8:22 am
This will give you the outcome you requested, if not the implementation.
select Name from table where ID between 1 and 3 order by ID;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 7, 2016 at 8:26 am
tomd-982312 (6/7/2016)
I am fairly new to T-SQL and have some experience in Oracle PL/SQL.Here is the case:
I have to read records sequentially from some table in MSSQL database and (on some condition) do something with read data.
In Oracle I use following code:
beginfor i in (select ID, Name from table where ID between 1 and 3)
loop
dbms_output.put_line(i.Name); --simplified example
end loop;
end;
In this case there will be written 3 names in the console window.
How can I do the same in T-SQL, in simplest code?
Thanks in advance,
Tom
1) Your code has nothing that I see that GUARANTEES the order of the rows unless Oracle GUARANTEES that reading from a table like you did returns the rows in a certain order. In SET BASED PROCESSING according to the ANSI standard there is NO guarantee of order unless you EXPLICITLY specify it. SQL Server certainly has no such guarantee. Even selecting data from a clustered index is not guaranteed to return the data in clustered index order unless you include an order by clause that duplicates the clustered index key(s).
2) Your output can be achieved with this simple set-based statement:
SELECT Name
FROM table
WHERE id BETWEEN 1 and 3
ORDER BY "whatever the heck you want the data ordered by"
BTW, that statement should work on any ANSI compliant RDBMS platform.
3) As others said, row-by-agonizing-row processing is unbelievably inefficient in SQL Server.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 7, 2016 at 8:41 am
Having to use dbms_output.put_line(); is one of the many reasons that I hate Oracle.
T-SQL has a simpler way to work as stated before. If you need help on the "do something with read data." part ask about that so we can help you see your problem as set based instead of using row by row processing.
First step towards the paradigm shift of writing Set Based code:
"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
June 8, 2016 at 12:34 am
drew.allen (6/7/2016)
This type of processing is referred to as RBAR (Row By Agonizing Row), because it is a HORRIBLY inefficient approach in the vast majority of cases. Why don't you tell us what you're actually trying to accomplish, and we'll help you figure out if it can be done with a set-based approach instead.Drew
I have to migrate some data from old DB to new DB. Database models are different so I have to do it RBAR way. When I get one record, depending on some values, I have to decide what's next.
So, I need to read table sequentially, sometimes I did that in Oracle and now I have to do it MSSQL.
----------------------------------------
I miss SQL Server so much. Now I'm working on Oracle and hate it
June 8, 2016 at 12:51 am
tomd-982312 (6/8/2016)
drew.allen (6/7/2016)
This type of processing is referred to as RBAR (Row By Agonizing Row), because it is a HORRIBLY inefficient approach in the vast majority of cases. Why don't you tell us what you're actually trying to accomplish, and we'll help you figure out if it can be done with a set-based approach instead.Drew
I have to migrate some data from old DB to new DB. Database models are different so I have to do it RBAR way. When I get one record, depending on some values, I have to decide what's next.
So, I need to read table sequentially, sometimes I did that in Oracle and now I have to do it MSSQL.
Database models being different does not mean you need to do it RBAR - Over the last 4 years I was the lead architect of a project that did the migration of 4 different insurance models to another one and no RBAR used at all.
you may need to use temp tables - yes
you may need to do more than one pass at the data - yes
you may need to have several blocks of code, each one processing a block of mapping rules - yes.
but use RBAR ? - highly unlikely
so if you wish us to help you give us some data and a sample of what you are trying to do with that data - original model, destination model and mapping rules.
June 8, 2016 at 12:52 am
Guys, this topic is not about efficiency, or RBAR, or order by, or dbms_output.put_line().
I'm just trying to find simple code that reads sequentially row by row from table. It's a one-time job.
Thom A, thank you, I'll try with your suggestion.
Regards,
Tom
----------------------------------------
I miss SQL Server so much. Now I'm working on Oracle and hate it
June 8, 2016 at 1:06 am
frederico_fonseca (6/8/2016)
tomd-982312 (6/8/2016)
drew.allen (6/7/2016)
This type of processing is referred to as RBAR (Row By Agonizing Row), because it is a HORRIBLY inefficient approach in the vast majority of cases. Why don't you tell us what you're actually trying to accomplish, and we'll help you figure out if it can be done with a set-based approach instead.Drew
I have to migrate some data from old DB to new DB. Database models are different so I have to do it RBAR way. When I get one record, depending on some values, I have to decide what's next.
So, I need to read table sequentially, sometimes I did that in Oracle and now I have to do it MSSQL.
Database models being different does not mean you need to do it RBAR - Over the last 4 years I was the lead architect of a project that did the migration of 4 different insurance models to another one and no RBAR used at all.
you may need to use temp tables - yes
you may need to do more than one pass at the data - yes
you may need to have several blocks of code, each one processing a block of mapping rules - yes.
but use RBAR ? - highly unlikely
so if you wish us to help you give us some data and a sample of what you are trying to do with that data - original model, destination model and mapping rules.
There are 116 tables in the old model and 88 tables in the new model. Almost everything is different in the new model. Therefore, it is obvious why it isn't possible to produce original and destination models in this topic.
When I get one row from old table I have to decide where should I put it in the new model depending on some value.
Example:
- value 1 > it goes to Table7
- value 2 > column1, column3, column9 goes to Table 24; column2, column11, column12 goes to Table7 and so on.
...
- 10 other rules
...
----------------------------------------
I miss SQL Server so much. Now I'm working on Oracle and hate it
June 8, 2016 at 2:07 am
very raw example
insert into table7
select column1
from OldTable1
where columnx = someconditionvalue1
order by desiredordercolumns
insert into table24
select column1
, column3
, column9
from OldTable1
where columnx = someconditionvalue2
order by desiredordercolumns
insert into table7
select column11
, column12
from OldTable1
where columnx = someconditionvalue2
order by desiredordercolumns
now there is more to it obviously as on your own example you are inserting into table7 different columns based on a value condition so the remaining columns would need to be populated according to some rules.
but what you need to start thinking is not what to do with a row of input data, but in terms of blocks of conditions and requirements alongside each condition per destination table, not source table.
And then for each condition you do the required sql.
you probably have cases where for a single destination row you get data from 2 or more tables on your source model so you join them as needed and insert/update the destination table.
as per putting the model here I am pretty sure you could easily post here the definition for one of your destination tables, and for the source tables that would be the processed to generate that destination table along side the rules for each row. No need for ALL the model.
design pattern
destination table A
- populate from source table A where column1 = x and column2 = y - use column a,b,c,d,e from source, map to columns 5,6,7,8,9 on destination - remaining destination columns should be populated with (whatever values)
- populate from source table B where column1 = x and column2 = y - use column a1,b1,c1 from source, map to columns 10,11,12 on destination - remaining destination columns should be populated with (whatever values)
destination table B
- populate from source table A where column3 = z - use column c,d,e from source, map to columns 7,8,9 on destination - remaining destination columns should be populated with (whatever values)
- populate from source table C where column1 = x and column2 = y join with table E - use columns a1,b1,c1 from source C, columns j,k from source E, map to columns 10,11,12,24,28 on destination - remaining destination columns should be populated with (whatever values)
June 8, 2016 at 7:04 am
tomd-982312 (6/8/2016)
Guys, this topic is not about efficiency, or RBAR, or order by, or dbms_output.put_line().I'm just trying to find simple code that reads sequentially row by row from table. It's a one-time job.
Thom A, thank you, I'll try with your suggestion.
Regards,
Tom
Thom A's sample code WILL NOT PROCESS THE DATA IN ANY PARTICULAR, GUARANTEED ORDER!! His SELECT to populate the cursor does NOT have an ORDER BY clause. Without that, as I very pointedly said, there is NO ORDER GUARANTEED.
Also, if you need a cursor to be efficient and it only goes through the data forward one record at a time you should use a FAST_FORWARD cursor.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 8, 2016 at 7:10 am
For a fast sequential read of a table to move data from one system to another, you could also use SSIS.
You're doing ETL work, use the ETL tool.
June 8, 2016 at 7:55 am
Luis Cazares (6/8/2016)
For a fast sequential read of a table to move data from one system to another, you could also use SSIS.You're doing ETL work, use the ETL tool.
This would definitely be a better option if you can do it. SSIS is very well equipped for tasks such as this, and there's plenty of resources to help you get started if it looks foreign to you.
On the Ordering, yes, I'm aware it's not. I simply provided as was asked for. I'd rather not give the complete solution for a scenario in my responses, it gives people the option of a bit of further self learning if the code they asked for is complete, but needs a little work for their own environment, or to behave the way they want/need it to 😀
If the OP wants to follow this solution (yes, we have discussed maybe it's not the "right" one, but my point remains), then it's a good starting point, but also introduces them to CURSORS, which isn't a bad thing. Even if you don't end up using them, it's good to get as much understanding of a language as you can. Knowing how to use something that is deemed "bad" and why and how, in my opinion, is just as important as knowing how to use something that is "good" and why and how.
I hate to sound like a moderator, but if everyone wants to discuss all the pros and cons of CURSORS, then maybe this isn't the best place, as the OP has received an answer for their question. I'm more than happy to talk about it elsewhere though. If they want to follow a different solution, I'm sure there are many of us who have posted here who can give them the guidance further to use a data-set approach, or SSIS (or other ETL tool), to achieve their goals.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 8, 2016 at 8:06 am
Thom A (6/8/2016)
If the OP wants to follow this solution (yes, we have discussed maybe it's not the "right" one, but my point remains), then it's a good starting point, but also introduces them to CURSORS, which isn't a bad thing. Even if you don't end up using them, it's good to get as much understanding of a language as you can. Knowing how to use something that is deemed "bad" and why and how, in my opinion, is just as important as knowing how to use something that is "good" and why and how.
Indeed, cursors aren't bad. The problem is the row-by-row processing mentality that makes people think they need a cursor. I use cursors, not nearly as often as I did when I started using SQL Server, but I longer use or suggest cursors with the default settings.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply