Solved

SQL Query Syntax with Cursors Insert/Update

Posted on 2012-03-31
7
236 Views
Last Modified: 2012-06-27
I am trying to get away from using cursors and here is something I do all the time with cursors. I read one table, get the values and then I check for existence in another table. If the record does not exist I do an Insert. If it does exist I do an update. Is there a way to do this without cursors.

As an example, here is what I need to do. I have mulitple databases all with the same tables. Let's say one of the tables is called YTDSUMMARY. In this table are fields called YEAR1,PERIODID,ACCTNMBR and PERDBLNC. Now what I want to do is parse all the databases and then create a temporary table that will have aggregate totals by year1,periodID and acctnmbr. So as I read the first database, all the updates to the temporary table will be inserts. However, as I proceed through the other companies some may be inserts and some may be updates.

YTDSUMMARY - fields YEAR1,PERIODID,ACCTNMBR and PERDBLNC
PK - YEAR1,PERIODID,ACCTNMBR

Temporary Table
GLTBWORK -  fields YEAR1,PERIODID,ACCTNMBR and PERDBLNC
PK - YEAR1,PERIODID,ACCTNMBR

Without using a cursor, what would the syntax be to populate the temporary table with either inserts or updates and I proceed through each of the other databases?

My plan is to use DYANAMICS SQL as the database names will keep changing as I move from one database to another.
0
Comment
Question by:rwheeler23
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 9

Accepted Solution

by:
OCDan earned 400 total points
ID: 37791619
I know cursors are generally seen as a bad thing, and in most cases they are.
However, if you are getting a list of the databases and then doing a cursor that does inserts/updates for each one then personally I don't see any issue with that, but I am open to being corrected (as I likely will be).

If there is more than one cursor in your current procedure then I would change that. Do you have an example of the code you are running at the moment, it would be easier to see if there is a better way to do it if you provided us with that first.
0
 

Author Comment

by:rwheeler23
ID: 37791779
Here is the code as of right now. It is pretty basic. I do not what it is about cursors. I know they are resource intensive but sometimes they are the only way to get things done. People either hate them or love them. I try not to use them whenever possible.

DECLARE @SQL      nvarchar(max)
DECLARE @ACCTNMBR nvarchar(max)
DECLARE @ACCTDESC nvarchar(max)
DECLARE @PERDBLNC numeric(19,5)
DECLARE @return_status int;

-- Build SQL statement to proceed through the GL balances for this company
set @sql = 'Declare  gltbworkcurs CURSOR for
      SELECT glm.ACTNUMBR_2,glm.ACTDESCR,gls.PERDBLNC,      from ' + rtrim(@CurrentGPCompany)+
      '..gl10110 gls INNER JOIN ' + rtrim(@CurrentGPCompany)+ '..GL00100 glm on gls.ACTINDX=glm.ACTINDX ' +
      'where YEAR1 =' + rtrim(convert(char(4),@YEAR1)) + ' and PERIODID = ' + rtrim(convert(char(2),@PERIODID))

-- Execute the cursor        
exec sp_executesql @sql
 
-- Open the cursor
OPEN gltbworkcurs

FETCH NEXT FROM gltbworkcurs INTO @ACTNUMBR,@ACCTDESC,@PERDBLNC
WHILE (@@FETCH_STATUS = 0 )
BEGIN
      
      /* Do totals exist for this account in the temporary work table,if so update them,if not insert it */
      EXEC @return_status = pdgCheckAccountsGLTBWORK @YEAR1,@PERIODID,@ACCTNMBR
      IF (@return_status = 0)
    BEGIN
      
        INSERT INTO #GLTBWORK
           (YEAR1,PERIODID,ACCTNMBR,ACCTDESC,PERDBLNC)
            VALUES
           (@YEAR1,@PERIODID,@ACCTNMBR,@ACCTDESC,@PERDBLNC)
       END
       ELSE
       BEGIN
             UPDATE #GLTBWORK
             UPDATE PERDBLNC = PERDBLNC + @PERBLNC
             WHERE YEAR1=@Year1 AND @PeriodID=@PeriodID AND ACCTNMBR = @ACCTNMBR
       END
          
      FETCH NEXT FROM gltbworkcurs INTO @ACTNUMBR,@PERDBLNC
END

CLOSE gltbworkcurs
DEALLOCATE gltbworkcurs
0
 
LVL 9

Expert Comment

by:OCDan
ID: 37791819
I can't see any other way to do this. Everything I can find on If Exists Update Else Insert involves using cursors if multiple records are involved.
 Sorry I couldn't be more help, one thing I have found that can speed up cursors a bit is to use a few keywords after on the cursor declaration line. They should work fine for your use.

e.g. DECLARE mycursor CURSOR LOCAL FAST_FORWARD STATIC
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:rwheeler23
ID: 37792054
Do you see anything wrong with my code when it comes to the cursor?

I am actually calling this sp through a VS C# program and when this sp gets called it tell me the cursor  gltbworkcurs does not exist.
0
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 50 total points
ID: 37792994
Merge is doing what you want: Insert new data and update old data.

http://msdn.microsoft.com/en-US/library/bb510625(v=sql.105).aspx

Bye, Olaf.
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 50 total points
ID: 37793827
Cursor mains problem is overhead locking since procedural mindset is necessarily under optimized for Set IO operations.  Line by line procedural implementations such as cursors are simply low level abstractions but a relational database is not best used that way.  

Using cursors in relationally oriented database is simply the poor man's way to use SQL.  On SQL There is pretty much nothing you can do with a cursor that you can't do without, better and faster.  

In your case, I would look first at creating a view that consolidates all tables from multiple databases using the UNION ALL operator.  Once the global view is created, it should be easier for you to process any further logic.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 37793829
Example of global view

CREATE VIEW V_ALL_TABLE1
AS
SELECT COL1 from DATABASE1.DBO.TABLE1
UNION ALL
SELECT COL1 from DATABASE2.DBO.TABLE1
.....
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Help - 12 42
MSSQL Frequency of Years From Days Field 2 16
login and database user 3 21
Syntax using Declare 3 11
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

747 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

12 Experts available now in Live!

Get 1:1 Help Now