Solved

looping through records of a view

Posted on 2006-12-01
27
302 Views
Last Modified: 2008-02-01
I'm familiar with how to loop through records in .net using a recordset but how do I do it in SQL.

I have a view with a bunch of records.  each record must be inserted into another DB (mdb), but each record needs to be tested before it is added to the mdb.  

here's my seudo VB code example:

view.move first
With View
    while view not EOF
     if view!name = null then
          Add_Customer view!name   (a function that adds the new customer to the mdb with the name field as a param)
     else
          insert into tblInvoices (name, amopunt, taxes, so on) .......
     view.move next
     loop          


How would I replicate this logic in a SQL proc
0
Comment
Question by:Steve7423
  • 8
  • 5
  • 5
  • +2
27 Comments
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 18057062


Don't loop... use SQL. SQL is deesigned for set based transactions. When you loop you're wasting the SQL benefits.

insert into tblInvoices (name, amopunt, taxes, so on) .......
SELECT columnname1, columnname2, name
FROM View
WHERE name IS NULL
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 18057063
you can use cursor if you explicitly want to step through each record in this view, if you really want to loop then create a temp table with all the columns of the view plus one extra identity colum, insert data from this view to a temp table
and then loop through this temp table using identity cols value

0
 
LVL 17

Accepted Solution

by:
xDJR1875 earned 250 total points
ID: 18057098
You will want to use a cursor. (Caution if this is for a large table, it could use up a lot of server resources)

DECLARE @myvariable_id int, @myvariable_name nvarchar(50),

DECLARE my_cursor CURSOR
FOR SELECT * FROM Purchasing.Vendor
OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO @myvariable_id, @myvariable_name

WHILE @@FETCH_STATUS = 0
BEGIN
     --do your processing here.

END

0
 

Author Comment

by:Steve7423
ID: 18057114
I definately have to loop through each record because I have to check to see if the record exists in the mdb.  If it doesn't then call the Add_customer proc.  Once each record in the view is checked then insert all the records.  

I guess the best way to do this is by using a cursor.  could you post a sample of how to step through records using a cursor?


thanks.
0
 
LVL 17

Expert Comment

by:xDJR1875
ID: 18057115
also, if you use the cursor solution don't forget to add the Fetch Next statement Before the END statement.

WHILE @@FETCH_STATUS = 0
BEGIN
     --do your processing here.

     FETCH NEXT FROM my_cursor
     INTO @myvariable_id, @myvariable_name

END

0
 

Author Comment

by:Steve7423
ID: 18057123
The view may have 100 - 150 records
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 18057153
now I dont think you need a cursor of loop, you just need a insert statement by joining these two
can you post structure of the table you are stepping through and the structure of table which you want to update

0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 18057235
Looping is bad... very bad.

There is usually a way to do most things without looping... and it's almost always better to loop without using a cursor.

For instance...you can join on it... something like (without really knowing your schema)



insert into tblInvoices (name, amopunt, taxes, so on) .......
SELECT columnname1, columnname2, name
FROM View v
LEFT JOIN mdb m on v.id = m.id
WHERE name IS NULL

Please post more specifics and we can lead you in the right direction.
0
 
LVL 17

Expert Comment

by:xDJR1875
ID: 18057250
Your recordset is small enough that using a cursor would probably not have much effect on the sql server.
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 18057332
It is a bad practice to design on the assumption that a recordset isn't going to grow. Always design for the worst case. The problem with most databases I have to tune as a result of poor performance is as a result of poor planning. But, hey, that's where I make my money and as long as there are developers out there coding poorly... us database developers will have a job...

I take it back... use the cursors. I love job security.

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18057344
>>Your recordset is small enough that using a cursor would probably not have much effect on the sql server.<<
You are missing the point.  This is not a valid reason to use a CURSOR.  CURSOR's should only be used as a last resort. Period.
0
 
LVL 17

Expert Comment

by:xDJR1875
ID: 18057353
IF this is a one time data load it is a valid reason to use a cursor.

but alas... we all have our own opinions.. give me the points! ;-)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18057358
All you need is something like this:

INSERT tblInvoices (name, amopunt, taxes,  .......
Select   v.Name, ....
From    YourViewGoesHere v
            Left Join tblInvoices i on v.Name = i.Name
Where  i.Name is Null

Why on earth would you want to make it more complicated?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18057368
>>we all have our own opinions.. <<
Unfortunately, no.  It has nothing to do with opinion.  As Atlanta_Mike has allueded to, it is called experience.
0
 
LVL 17

Expert Comment

by:xDJR1875
ID: 18057495
That is why I only use cursors for data loads (1 time shots) I avoid them all other times. Don't be too quick too judge who has experience grasshopper.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18057843
Than pray tell can you explain how a CURSOR would be simpler to write than any of the INSERT queries Atlanta_Mike and I posted?

>>Don't be too quick too judge who has experience grasshopper.<<
When you post a valid solution using the questioners requirements I will start judging, in the meantime I am afraid you are not even on the radar screen.
0
 

Author Comment

by:Steve7423
ID: 18057927
Ok I guess I'll post the best seudo I can to represent my scenario.  first I have to say....the point about cursors is well received.  if only I can think of an efficient way of representing what needs to be done.  My appologies for using the isnull(name) example, it obviously redirected the point of my problem. I hope the following is a clearer example.

There are two DB's, an Access DB and a SQL DB.  the records in both differ.  for example the customers in the SQL may not be in the mdb and the issue is to synchronize and maintain the two DB's.  The client is creating invoices in the SQL DB which then have to be transfered to the Access DB.  The mdb is the actual accounting system, though they have been using SQL to maintain invoices, now they want to duplicate the invoice information in the new accounting system so they don't have to manually enter duplicate info.   I have to check to see if the customer for an invoice in SQL, is also in the mdb.  you see I don't know which customers are in the accounting system and which aren't so I would like to check it to avoid missing key failures.

If the customer does not exist in the mdb then I have to create the customer in the mdb, based on the record in the view in SQL.  the client is doing a monthly batch of invoices once a month.  the mdb requires relational records in order to add invoices. if the customer isn't in the customer table then the transaction can not be written and therefore fails, rolling back all transactions.  If I don't check the mdb then if there's a failure no one will know why.

The view may have 100-150 records.  it is possible for the view to increase in records but very little, however, as mentioned, poor performance is a result of poor planning, I get it.

The process:
A view of all the monthly invoices is created
there may be invoices for customers that do not exist in the mdb, so check to see if the customer exists for each record
if so then write the record and move next Else run a proc that adds this new customer to the mdb, then write  the invoice records

I'm using the customer name field as the criteria to check existance in the mdb.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18058174
If I understand you correctly you are trying to add rows to the MS Access database to keep it in sync with a SQL Server database.  If that is the case, than in a sense you really have a MS Access rather than a MS SQL Server problem and your best bet is posting in a more appropriate Topic Area such as:
http://www.experts-exchange.com/Databases/MS_Access/ or
http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/

The only way you could achieve this with a pure T-SQL solution is using OPENROWSET() or a linked server.  For a non T-SQL solution, but still keeping it within MS SQL Server, you can use DTS to do an append query to your MS Access database.
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 18058194
Hey AC,  I guess I went home at the wrong time... thanks for helping out.

Here's a silly question, Steve. Why don't you just port the Access DB to SQL Server, and create a link to it from Access? Then the records will be in SQL Server.

Mike
0
 

Author Comment

by:Steve7423
ID: 18058422
Mike, are you saying import the tables in question to SQL and link them to the Access DB?  How do you import into SQL and how do you link the tables in SQL?  even still I have to check the records in the customer table during the insert of the view records to prevent key violations and process failures.

Thought.... if the AccDB is moved this will pooch the link to the tables and code must be written in SQL to relink the tables after the file is found.

AC, this definately needs to be done in SQL because the proceedures are called from a front end app, and all the processing must be done on the server.  the reason is because the AccDB is a commercial app that uses a Access DB.  I only have rights to see the tables.

I'm in the process of trying to design a script using openrowset that will show a bit of what I need to do.  I need your patience, stay tuned.  

meanwhile here's a pictoral representation of what needs to be done.

SQL View:
name          amt          date
abc co.        50           dec 1
xyz co         100          dec 1

AccDB tblCustomer:
sname          
abc co

if isnull select from tblcustomer where sname = view!custname then
     add customer xyz
end if

hope this makes sense
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 18058496
Ahhh...bummer. The commercial app has your table locked down?

In order to link the Application database tables to the SQL Server tables you'd have to be able to have access to the MSAccess database. Microsoft has a utility (or did have) that will move the tables for you. Then from the Access database you can link to the SQL Server table instead. My guess is the application vendor has restricted your access. Who is the vendor? What is the product?

I'd be contacting the vendor to port the database for the app to SQL Server instead of Access.

0
 

Author Comment

by:Steve7423
ID: 18060476
Unfortunately, not an option.  further investigation tells me that linking tables isn't such a great idea in this particular case.

I'm working on some logic that I hope may spark an idea for someone.  

stay tuned bat fans. :)
0
 

Author Comment

by:Steve7423
ID: 18064523
xDJR1875, could you explain the purpose of : DECLARE @myvariable_id int, @myvariable_name nvarchar(50)
why is this used?

also, how do I refer to a specific field value of a record which the cursor is on.  eg in VB:

dim Rst as recordset
set rst = dbo.openrecordset(select * from customers)
rst .movefirst
Var = rst ! sname
rst.movenext
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 18069020
Steve,
first a questions,
somewhere in your comments I read that you dont want to link access within MSSQL cause access might move, if it does how do you programmatically plan to keep track of this move?.

and since invoices are created once a month, why not bring whole tblCustomer table over to MSSQL, create a unique list of customers from SQL view and this imported access table, put this list in a temp table (or better a physical table that can be updated every month). now export the customer list from this temp table in tblCustomer in mdb, and update SQL table likewise.
This can be part of a monthly job and thats it.
I wonder if I even understood your problem completely cause this solution is very simple

rw3admin

0
 

Author Comment

by:Steve7423
ID: 18069559
rw3admin, tracking the move of the AccessDB has two possibilities.  I'm not responsible for the creation of the code but I am going to design the foundation of how it's done.  in the main proceedure is an if statement that checks the openrowset connection to the mdb, if it fails it's because the db isn't there.  it uses a path string that is stored in a table in the SQL db.  on failure a screen opens that allows the user to navigate the network to find the mdb, using the OpenFile dialog.  the users finds the db and the new path is stored in the sql DB.  the other way is very similar but the string is stored in the registry.

The whole point of my problem is comparing customers.  It's possible to enter a customer in the sql DB and forget to add them to the mdb, because they are separate applications.  if an invoice is created for a customer that was entered in the sql DB, but that customer does not exist in the mdb then I can't write transactions because that customer must exist in the mdb to meet data integrety and relationships.  therefore I have to check each and every record in the view to make sure that each customer in the SQL db is also in the mdb.  

In essence , we are doing the same thing.  You see there may be only 1 instance a month of missing customers but I don't know which one.  some months there are no missing customers.

In the end the customer doesn't care to much about performance.  if the whole process takes 2-3 minutes to complete that's fine.  I'm suspecting that even with a cursor the process should complete in seconds.

Your idea has merrit and is a unique approach.  however we don't know when new customers are added and we have to assess the performance cost of iether solution, not to mention development time.  your idea would have to run prior to the write process to ensure synchronisity.

Not to complicate matters but I have to duplicate this logic for Products too.
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 18070405
Steve Thanks for explaining this more,
please dont think I am trying to enforce my idea, cause other experts also had excellent ideas, I am just saying syncing DBs once a month this way will save you guessing time, your SQL will decided (using joins) if records in MDB or SQL have new customers and if yes then go ahead and sync them. same logic for products.
But after reading your last comment I am thinking once a month sync is not an answer cause from SQL side you can add a customer create order (or invoice) for this customer while this customer is not in MDB databases,
In that case your application comes in which can still create linked server to Access MDB using all the means you described, something as simple as
Declare @LinkedServer varchar(150)
Set @LinkedServer = --(return a value of linked server from your app here)
if Not exists(Select * from master.dbo.SysServers where srvname = @LinkedServer)
Begin
exec sp_addlinkedserver  @LinkedServer
End

then query Access table through SQL, and check for existance of this record, in my opinion you dont need cursor, as this can be a simple check off MDB table you can write something as simple as an exists statement in to check if this customer exists in access or not, something as simple as using dynamic SQL



0
 

Author Comment

by:Steve7423
ID: 18095375
Hey guys and or gals, I've gone over my problem nine ways to sunday.  I've reviewed all suggestions taking into account the ramifications and implications of each, and the best way I can see accomplishing my goal is by using a cursor.  I recongnize the impact a curso has on performance.  I realize cursors are not favored however in this particular case a curso is the only solution which is viable and has the fewest implications considering all the facts.  the sequence of events that need to be executed rely on each individual record in the invoice view.  most importantly I have to account for the possibility that new customers could be created just prior to my proceedure running and I have to catch tose stray customers in the invoice view.  there are other factors as well that limit my solution, including my roll on this project; I'm not in charge of the DB structure and must work with the structure I have.

everyone has contributed awsome ideas and I wish I could reward every one but at the end of the day it's the cursor that solves my dilema.





0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql query 7 36
sql server query? 6 28
Left Join with Tuple returning more rows 10 56
SQL Server 2012 Row Selection 2 30
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now