Data manipulation nightmare

• Lee Wesley Clark

  Newbie

  Points: 5

  More actions

  November 15, 2004 at 3:16 pm

  #163276

  Good Day,

  I have a table in a database that contains information from an old mainframe db.  I need to insert the old data into an existing table in the same db.  The existing table we will call table_B and the old mainframe table we will call table_A  Table_B contains 4 keys to insure that the company, the customer, the glperiod and the transaction are uniqe per customer per glperiod.  The problem that I have is that I need to have a field called recno increment for each transaction each month.  In theory I could just auto incriment the recno field in Table_B before inserting the data into Table_A.  However Table_A recno field is  type smallint and(cannot be changed) will not contain the 56,000 plus unique values.  The recno field needs to restart incremnt at 1 for each new glperiod.

  I have come close to solving this using curor updates but I don't know how to increment an exitsing field per cursor.

  Thanks so much,

  Lee

• Johan Bijnens

  SSC Guru

  Points: 135551

  More actions

  November 16, 2004 at 12:07 am

  #530230

  It might even be from a _new_ mainframe db

  create function dbo.fnGetNextRecNo(@company xx, @customer xx, @glperiod xx, @transaction)

  returns smallint

  as

      begin

          declare @NewRecNo smallint, @recno int

          select @recno =max(recno) + 1

          from _B

          where company = @company

             and customer = @customer

             and glperiod = @glperiod

             and transaction = @transaction

           

          set @NewRecNo = isnull(@RecNo , 1) -- ?? where do you want to start today

         

          if @NewRecNo < (-32768)  or @NewRecNo > 32767

          begin

                -- Now what ??? 

                set @NewRecNo = (-32768)

          end

          return @NewRecNo

      end

  go

  -- grant when needed

  insert into _B (col_list)

  select company,customer,glperiod,transaction, dbo.fnGetNextRecNo(company,customer,glperiod,transaction),...

  from _A

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me