rownumber() over with insert statement

  • I am trying to create data in a table that is a lookup table to another table and hoping to use rownumber over to accomplish it. The script below works as far as generating the values I want to insert other then I can't figure out how to use the rownumber function in conjunction with the insert statement. Any help is a apppreciated.

    DECLARE @Counter INT

    DECLARE @BillingGroupCode VARCHAR(8)

    DECLARE @CompanyName VARCHAR(50)

    DECLARE @CompanynameCounter INT

    DECLARE @CompanynamePlusCounter VARCHAR(55)

    DECLARE @RowNum INT

    SET @CompanynameCounter = 0

    SET @Counter = (select count(*) from Sap.DetailsImport)

    select M.RowNum, M.BillingGroupNumber

    from

    (

    select BillingGroupNumber, (row_number() over (order by BillingGroupNumber) - 1) % @Counter + 1 as RowNum

    from Sap.DetailsImport

    ) as M

    WHILE @Counter != 0

    BEGIN

    SET @CompanynameCounter = @CompanynameCounter + 1

    SET @CompanynamePlusCounter = 'Company' + CAST(@CompanynameCounter AS VARCHAR(5))

    --INSERT INTO [ReceiptRepository].[Lkup].[BillingGroups]

    --VALUES(@BillingGroupNumber, etc... )

    PRINT @CompanynamePlusCounter

    PRINT @Counter

    PRINT @CompanynameCounter

    PRINT @BillingGroupCode

    SET @Counter = @Counter - 1

    END

    A clever person solves a problem. A wise person avoids it. ~ Einstein
    select cast (0x5365616E204465596F756E67 as varchar(128))

  • I'm not sure exactly what this query should be doing, but if you question is how to use insert with row_number then just do this:

    INSERT INTO [ReceiptRepository].[Lkup].[BillingGroups]

    select M.RowNum, M.BillingGroupNumber

    from

    (

    select BillingGroupNumber, (row_number() over (order by BillingGroupNumber) - 1) % @Counter + 1 as RowNum

    from Sap.DetailsImport

    ) as M

    I'm not sure what the other variables are used for or why this is in a while loop.



    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Thank for the reply Keith. The WHILE LOOP is needed because for each loop through it needs to create a unique company name to be used in the insert statement. Each one of those names need to be tied to each of the company group numbers that are in the row_number table. Together they need to be inserted into the table.

    For example when run as-is what prints to the screen is:

    Company1 -- Company name created dynamically and assigned to variable

    50

    1

    Company2

    49

    2

    Company3

    48

    3

    Company4

    47

    4

    etc... until counter reaches 0 and there are no more records to process

    Company50

    1

    50

    A clever person solves a problem. A wise person avoids it. ~ Einstein
    select cast (0x5365616E204465596F756E67 as varchar(128))

  • I would think you could use a windowing function to accomplish this, but without table structures and sample data I'm not sure. Is there a reason the following wouldn't work?

    select 'Company' + cast(row_number() over (order by getdate()) as varchar(5))



    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • I got it to work with creating a temp table and populating that with the results of my RowNumber table. May not be the most savy of code but it works.

    DECLARE @Counter INT

    DECLARE @BillingGroupCode VARCHAR(8)

    DECLARE @CompanyName VARCHAR(50)

    DECLARE @CompanynameCounter INT

    DECLARE @CompanynamePlusCounter VARCHAR(55)

    DECLARE @RowNum INT

    SET @CompanynameCounter = 0

    SET @Counter = (select count(*) from Sap.DetailsImport)

    select * into #RowNumbTable

    from

    (

    select BillingGroupNumber, (row_number() over (order by BillingGroupNumber) - 1) % @Counter + 1 as RowNum

    from Sap.DetailsImport

    ) as RowNumTable

    WHILE @Counter != 0

    BEGIN

    SET @CompanynameCounter = @CompanynameCounter + 1

    SET @CompanynamePlusCounter = 'Company' + CAST(@CompanynameCounter AS VARCHAR(5))

    SET @BillingGroupCode = (SELECT TOP 1 BillingGroupNumber FROM #RowNumbTable)

    INSERT INTO [ReceiptRepository].[Lkup].[BillingGroups]

    VALUES(@BillingGroupCode, @CompanynamePlusCounter, 'Y', 'TeraByteMe', GETDATE(), TeraByteMe, GETDATE(), '2006-01-01 00:00:00.000', '2020-12-31 00:00:00.000')

    DELETE FROM #RowNumbTable

    WHERE BillingGroupNumber = @BillingGroupCode

    PRINT @CompanynamePlusCounter

    PRINT @Counter

    PRINT @CompanynameCounter

    PRINT @BillingGroupCode

    SET @Counter = @Counter - 1

    END

    A clever person solves a problem. A wise person avoids it. ~ Einstein
    select cast (0x5365616E204465596F756E67 as varchar(128))

  • ...

    A clever person solves a problem. A wise person avoids it. ~ Einstein
    select cast (0x5365616E204465596F756E67 as varchar(128))

  • This might help you.

    WITH BillingGroups AS(

    SELECT BillingGroupNumber,

    row_number() over (order by BillingGroupNumber) as RowNum

    FROM Sap.DetailsImport

    )

    INSERT INTO [ReceiptRepository].[Lkup].[BillingGroups]

    SELECT BillingGroupNumber,

    'Company' + CAST(RowNum AS varchar(10)),

    'Y',

    'TeraByteMe',

    GETDATE(),

    TeraByteMe,

    GETDATE(),

    '2006-01-01 00:00:00.000',

    '2020-12-31 00:00:00.000'

    FROM BillingGroups

    Now, this might give you repeated billing group numbers, I hope that you're aware of that.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Yes, I think that does it. That is much simpler and more elegant than what I did. Thanks to you both for your help.

    A clever person solves a problem. A wise person avoids it. ~ Einstein
    select cast (0x5365616E204465596F756E67 as varchar(128))

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply