Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
helpme
Posting Yak Master
141 Posts |
 Posted - 2004-10-13 : 21:07:52
|
| I am new to sql server. I have the following in oracle. How would be the best way to do something like this in sql (I don't think sql has the 'for loop' option that is in oracle). The number of records in table1 is unknown. Any help is appreciated! Thanks!declare cursor fix_data_cursor is select ssn1, address1 from table1;beginfor tst in fix_data_cursor loop update table2 set address2 = tst.address1 where ssn2 = tst.ssn1;end loop;end |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-10-13 : 21:47:47
|
| UPDATE t2 SET address2=t1.address1FROM table1 t1 INNER JOIN table2 t2 ON t1.ssn1=t2.ssn2And if you're using Oracle 9 or higher, that statement may also work without modification, and should run faster than using a cursor. Cursors should always be your last choice, especially in SQL Server. Unfortunately Oracle likes to make them the first or only choice. |
 |
|
|
helpme
Posting Yak Master
141 Posts |
Posted - 2004-10-14 : 05:47:42
|
| Thank you for your reply, and I agree with you on cursors. However, we have a lot of code that we need to convert. My example was just a very simplistic example of what we have. We have cursors that select the data, then within the 'for loop' very extensive computations and manipulations are performed before the updates and inserts are done on table2 (sometimes pages of logic). I'm looking for the quickest way to convert the existing code we have, basically get everything converted to where it will run in sql server, and then maybe we can address the issue of rewriting it in a more efficient way. So, if I was to try and do the above example with a cursor (changing as little code as possible), how could I replace the 'for loop' logic in the sql server version?Thanks for your help on this, I appreciate it! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-10-14 : 08:36:43
|
| Well, that's my point: DO NOT convert Oracle cursors to SQL Server cursors. You're going to be doing a significant rewrite anyway, and copying cursor code to SQL Server will leave you with a poorly performing SQL Server. This has been not just my experience but pretty much EVERYONE's when trying to port older application code to a significantly different system (not just Oracle/SQL Server either)Don't take this personally, but "maybe we can address the issue of rewriting it in a more efficient way" will never happen, if you do a direct port from Oracle. If you don't address it now, the inertia of rewriting code will stop it later. You're already feeling the symptoms, by not wanting to get into a full rewrite yet. Hate to say it, but there is no "quick" way to do this that won't cause you a shitload of grief later on. You don't want to know how many times I've been there (I'm in a situation like that right now)You will be far better off in the long run if you write it effectively in SQL Server. You'll spend a little more time in the beginning, granted, but you'll save a lot more time later on. I cannot tell you how many months, maybe even years, I've wasted patching converted code that I ending up dumping anyway. |
|
|
|
helpme
Posting Yak Master
141 Posts |
Posted - 2004-10-15 : 05:52:20
|
| Can you point me to a place where I can find good sample code to look at for examples? Right now I haven't even found good sample code to use as a guideline. The books I have don't provide anything but the very basic of examples. |
|
|
|
B0g
Starting Member
19 Posts |
Posted - 2004-10-15 : 06:30:01
|
| If you really need a cursor... this might work:declare @ssn1 VARCHAR(20), @address1 VARCHAR(20)declare cursor fix_data_cursor forselect ssn1, address1 from table1open fix_data_cursorFETCH fix_data_cursor INTO @ssn1, @address1WHILE @@FETCH_STATUS = 0beginupdate table2set address2 = @address1where ssn2 = @ssn1FETCH fix_data_cursor INTO @ssn1, @address1endCLOSE fix_data_cursorDEALLOCATE fix_data_cursor |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-10-15 : 08:07:42
|
Thanks for the undermine b0g.  Books Online should always be your first resource for SQL Server information. It is installed along with SQL Server and a link to it appears in the same Start Menu folder as Enterprise Manager, Query Analyzer, etc. Getting familiar with BOL is the best way to become an expert with SQL Server.You can check out the SQL Team Bookstore for other great books on SQL Server. Inside SQL Server is a great resource and a good introduction to T-SQL beyond Books Online. Ken Henderson'sThe Guru's Guide to Transact-SQLIs an extremely good book, full of lots of examples that show you exactly what T-SQL can do...without cursors even. Along the same lines is Joe Celko's SQL For Smarties, although not specific to T-SQL, Joe is an established cursor-hater and will show you things you never thought were possible with SQL.Lastly, SQL TEAM!!!! Any time you have a question or get stuck, post it here, someone will help you out.  |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-15 : 08:41:08
|
| I promise you, if you are starting out with 5,000 lines of Oracles cursors to convert, and you convert line-by-line as "verbatim as possible" into T-SQL, you will end up with 10,000 lines of completely convoluted and unreadable, buggy, slow code.But if you take a step back, and really analyze and think about what each part of the Oracle code accomplishes at a higher level and re-write it using set-based T-SQL (no cursors), you will end up with less than 1,000 lines of clean, efficient and very easy to read/understand code.Now, it might take the same amount of time to reach either end point -- i.e., both techniques may take x days to accomplish. Maybe learning the in's and out's of set-based T-SQL might even end up taking 15% more time. But which end result would be more desirable?(oh, and by the way, depending on the complexitly of the existing processes, the set-based code will execute between 100 and 1000 times faster than the cursors)- Jeff |
|
|
|
helpme
Posting Yak Master
141 Posts |
Posted - 2004-10-16 : 08:27:49
|
| Thanks for all the replies, I appreciate it! |
|
|
|
|
|
|
|
|
doing oracle 'for loop' in sql server