SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


rownumber() over with insert statement


rownumber() over with insert statement

Author
Message
TeraByteMe
TeraByteMe
SSC Veteran
SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)

Group: General Forum Members
Points: 293 Visits: 589
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))
Keith Tate
Keith Tate
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1042 Visits: 979
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
TeraByteMe
TeraByteMe
SSC Veteran
SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)

Group: General Forum Members
Points: 293 Visits: 589
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))
Keith Tate
Keith Tate
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1042 Visits: 979
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
TeraByteMe
TeraByteMe
SSC Veteran
SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)

Group: General Forum Members
Points: 293 Visits: 589
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))
TeraByteMe
TeraByteMe
SSC Veteran
SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)

Group: General Forum Members
Points: 293 Visits: 589
...

A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
Luis Cazares
Luis Cazares
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17154 Visits: 19122
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
TeraByteMe
TeraByteMe
SSC Veteran
SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)

Group: General Forum Members
Points: 293 Visits: 589
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))
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search