Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

rownumber() over with insert statement Expand / Collapse
Author
Message
Posted Thursday, October 24, 2013 2:15 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 5, 2014 9:10 PM
Points: 163, Visits: 569
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.
A clever person solves a problem. A wise person avoids it.



A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
Post #1508265
Posted Thursday, October 24, 2013 2:30 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 5:13 PM
Points: 592, Visits: 924
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
Post #1508270
Posted Thursday, October 24, 2013 2:54 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 5, 2014 9:10 PM
Points: 163, Visits: 569
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.
A clever person solves a problem. A wise person avoids it.



A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
Post #1508280
Posted Thursday, October 24, 2013 3:10 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 5:13 PM
Points: 592, Visits: 924
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
Post #1508283
Posted Thursday, October 24, 2013 3:35 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 5, 2014 9:10 PM
Points: 163, Visits: 569
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.
A clever person solves a problem. A wise person avoids it.



A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
Post #1508289
Posted Thursday, October 24, 2013 3:37 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 5, 2014 9:10 PM
Points: 163, Visits: 569
...






A clever person solves a problem. A wise person avoids it.
A clever person solves a problem. A wise person avoids it.



A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
Post #1508290
Posted Thursday, October 24, 2013 4:11 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 3,614, Visits: 8,108
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1508293
Posted Thursday, October 24, 2013 5:34 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 5, 2014 9:10 PM
Points: 163, Visits: 569
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.
A clever person solves a problem. A wise person avoids it.



A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
Post #1508300
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse