Append records with 1 incrementing field

Mitch · May 21st, 2005, 03:40 PM

I am trying to append records to Table A from Table B, And as the records get appended I need a field in Table A to be a number that gets incremented by 1 and starts at a value that is the MAX from another table. I get that value from an SQL statement.

I can't seem to putll it all together. I have it so that it adds the number but it only makes it the max, it does not increment it.

Does anyeone have a way to do this?

Below is what I have so far:

Code:

PROCEDURE spSchedule_AppendAllToBatchItems_test
/*

*/

AS
INSERT INTO T_Schedule_BatchItems ( OrigID,  ItemType,  ItemDescription,  ItemID )
SELECT vSchedule_SchedulableItems.ItemID,
      vSchedule_SchedulableItems.ItemType,
      vSchedule_SchedulableItems.EquipDesc,
      dbo.FN_QCntr(1) AS CounterNumber
FROM vSchedule_SchedulableItems
ORDER BY vSchedule_SchedulableItems.ItemType,
      vSchedule_SchedulableItems.EquipDesc

And the function is this:

Code:

FUNCTION dbo.FN_QCntr
/*
'Used to count the records in a queary, Y will either be 1 or 2
*/

(@Y int)
RETURNS int /* datatype */
AS
BEGIN
    DECLARE @Cntr as int, @QCntr as int, @MaxItem as int
    set @Cntr = 0
    --set @MaxItem =  G0 Select MAX(ItemID) AS Expr1 FROM T_Schedule_ProductionItems 
    Select @MaxItem =  (Select MAX(ItemID) AS Expr1 FROM T_Schedule_ProductionItems)  --Should return 601

    IF @Y=1
        --Begin

            If @MaxItem IS NOT NULL        --found items (NOT NULL) start numbering at DMax + 1
                    --begin
                    IF @Cntr > @MaxItem
                            Begin
                            SET @Cntr=@Cntr+1
                            SET @QCntr=@Cntr
                            end
                        Else
                            Begin
                            SET @Cntr=@MaxItem
                            SET @Cntr=@Cntr+1
                            SET @QCntr=@Cntr
                            END
                    --End

        --END

    Else IF @Y=2    --  put 2 in because we wanted this to run independently for 1 particular query
        If @MaxItem IS NOT NULL
                IF  @Cntr > @MaxItem
                    begin
                    SET @Cntr=@Cntr+1
                    SET @QCntr=@Cntr
                    end
                Else
                    begin
                    SET @Cntr=@MaxItem
                    SET @Cntr=@Cntr+1
                    SET @QCntr=@Cntr
                    end
                --End
            --END
        Else        --No items found (NULL)  start numbering with 1
            begin
            SET @Cntr = @Cntr + 1
                    SET @QCntr = @Cntr
            end

    Else
        begin
        SET @Cntr = @Cntr + 1
        SET @QCntr = @Cntr
        end

RETURN @QCntr /* value */

End

Mitch · May 23rd, 2005, 04:01 PM

I have gotten close with this, it runs without error, but I don't see wehre it created the table, if at all.

Code:

SELECT ItemID,  ItemType,  EquipDesc, identity(int, 1, 1) as id into #a FROM vSchedule_SchedulableItems ORDER BY ItemType, EquipDesc
declare @id int
select @id = max(ItemID) from T_Schedule_ProductionItems
INSERT INTO zzT_Schedule_BatchItems ( OrigID,  ItemType,  ItemDescription,  ItemID )
select ItemID,  ItemType,  EquipDesc, @id + id
from #a

happygv · May 25th, 2005, 12:08 AM

Which table do you mean? #a or zzT_Schedule_BatchItems?
If #a, it is a temporary table, that is dropped once the batch execution is completed. So on completion of executing this procedure, if you wanted to look at #a table's rows, it is simple, you can't see that as the table is dropped by the server then.

I am not sure what you mean by @id + id? Is the id after '+' a variable or a value?

As you say, you want to increment the max ID by one, if I understood that right, you could instead do it this way.

Code:

select @id = max(ItemID) + 1 from T_Schedule_ProductionItems
INSERT INTO zzT_Schedule_BatchItems ( OrigID,  ItemType,  ItemDescription,  ItemID )
select ItemID,  ItemType,  EquipDesc, @id
from #a

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Mitch · May 26th, 2005, 08:40 AM

In the end I had to create the table zzT_Schedule_BatchItems by hand first. Then it appended records to it. I would have thought that it would have generated an error if it did not find the destination table, but guess not.

Now, on to the second of two similar procedures.