Home Forums SQL Server 2005 T-SQL (SS2K5) Manually increment smallint column during an INSERT command. RE: Manually increment smallint column during an INSERT command.

  • roy.tollison (11/5/2013)


    ROW_NUMBER sounds promising.

    the values they want in spdd_f5 will be 1 thru 100 (will never go over 100) no duplicates.

    it is just a pad number but it would need to be reset when any of the _f1, _f2, _f3, _f4 columns values change.

    this might not be possible in T-SQL, if it isn't then i will just write a program to do it.

    I wouldn't be so hasty as to suggest this is not possible in T-SQL. I think this is something like what you're looking for:

    INSERT INTO SPEDDTL (spdd_f1, spdd_f2, spdd_f3, spdd_f4, spdd_f5, spdd_f6, spdd_f7)

    SELECT spd_f1, spd_f2, spd_f3, spd_f4

    ,ROW_NUMBER() OVER (PARTITION BY spd_f1, spd_f2, spd_f3, spd_f4 ORDER BY (SELECT NULL))

    ,spd_f6, spd_f7

    FROM SPED

    WHERE NOT EXISTS

    (

    SELECT *

    FROM SPEDDTL

    WHERE spdd_f1 = spd_f1 and spdd_f2 = spd_f2 and spdd_f3 = spd_f3 and spdd_f4 = spd_f4

    );

    However I do have some concerns, for example what if additional matching rows are added to SPED after you've run this query to populate SPEDDTL? Those will never get added to SPEDDTL, and it may be a bit bothersome to add them later. To do that, you'd need to have something better than:

    ORDER BY (SELECT NULL)

    In the window function. If you could describe what causes the generation of the new SPED records to be added, it might help to identify what you could change the ORDER BY to, and allow you to INSERT the additional records.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St