looping through records of a view

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

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

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.

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

The view may have 100 - 150 records

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

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.

Your recordset is small enough that using a cursor would probably not have much effect on the sql server.

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.

>>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.

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! ;-)

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?

>>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.

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.

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.

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.

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:
https://www.experts-exchange.com/Databases/MS_Access/ or
https://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.

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

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

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.

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. :)

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

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

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.

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

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.