Home Forums SQL Server 2005 T-SQL (SS2K5) Querying a Table Using Dynamic SQL RE: Querying a Table Using Dynamic SQL

  • March 3, 2010 at 10:19 pm

    #1128255

    Jeff Kring (3/3/2010)

    I did look into ROW_NUMBER as an option. I could use it to return the correct rows, but the application needs the actual value as well.

    Not sure if this is exactly what you mean, but it's quite easy to make a new table with a new identity column whilst copying an old identity column. Read the comments in the code, please...

    --===== Original table with an IDENTITY column

    SELECT TOP (1000)

    IDENTITY(INT,1,1) AS SomeIdentity,

    NEWID() AS SomeValue

    INTO #ContainsIdentity

    FROM Master.sys.All_Columns

    --===== New table with new IDENTITY column and old

    -- IDENTITY column stripped of IDENTITY property by ISNULL

    SELECT IDENTITY(INT,1,1) AS SomeNewIdentity,

    ISNULL(SomeIdentity,0) AS StrippedOfIdentity

    INTO #NewTable

    FROM #ContainsIdentity

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)