October 24, 2013 at 2:15 pm
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))
October 24, 2013 at 2:30 pm
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.
October 24, 2013 at 2:54 pm
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))
October 24, 2013 at 3:10 pm
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))
October 24, 2013 at 3:35 pm
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))
October 24, 2013 at 3:37 pm
...
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
October 24, 2013 at 4:11 pm
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.
October 24, 2013 at 5:34 pm
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