• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

SQL Query Syntax with Cursors Insert/Update

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
rwheeler23
Asked:
rwheeler23
  • 2
  • 2
  • 2
  • +1
3 Solutions
 
OCDanCommented:
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
 
rwheeler23Author Commented:
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
 
OCDanCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
rwheeler23Author Commented:
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
 
Olaf DoschkeSoftware DeveloperCommented:
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
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now