SQL to insert row with RefNum to be the next numeric number

Question

Post reply

SQL to insert row with RefNum to be the next numeric number

Coung

SSC Veteran

Points: 269
More actions
January 23, 2024 at 12:07 pm

#4366343
Hi all,
I need some help with SQL. I want to create test data by inserting new rows into an existing table using TSQL. Each row has a RefNum column which is in integer and isn't the primary key. RefNum of the new row should be max. value of RefNum in the table + 1
ie. If the max. Value of RefNum is 100 from the records, then my new row will contain 101 for RefNum
It's this possible in SQL?
I tried Max(RefNum) + 1 but get errors that a different column is invalid because it is not contained in either an aggregate function or the GROUP BY clause.
- This topic was modified 11 months, 1 week ago by Coung.
- This topic was modified 11 months, 1 week ago by Coung.
January 26, 2024 at 10:45 am

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

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

ratbak Ten Centuries Points: 1382 More actions · Answer 1

It sounds like you're trying to reinvent sequence or identity. Why not just use a sequence?

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

I can think of a few ways to do this and I think the easiest is to grab the MAX(RefNum) into a variable and then use ROW_NUMBER in your INSERT SELECT statement. So something like:

DECLARE @MaxRef INT
SELECT @MaxRef = MAX(RefNum)
FROM <table>

INSERT INTO <table>
SELECT col1, col2, col3.... @MaxRef + ROW_NUMBER() OVER(ORDER BY col#) as RefNum
FROM <source>

Making a few assumptions with the above, but I think it gives you a rough idea of what I am thinking of for solving the problem. Probably not the most efficient way to do it, but I expect it to be "fast enough" and is easy to read and understand.

Now, that being said, if the RefNum is just an ever increasing int, creating a sequence for it OR making it an IDENTITY column is probably the easier approach.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Coung SSC Veteran Points: 269 More actions · Answer 3

Unfortunately, I don't have a say about the structure of the table. It's populated via an ETL process. I just want to create dummy data for testing so want to make sure RefNum is unique.

Coung SSC Veteran Points: 269 More actions · Answer 4

Many thanks Brain. I totally forgot about creating a variable to store the value 🤦🏻‍♂️.

Phil Parkin SSC Guru Points: 246990 More actions · Answer 5

Coung wrote:

Many thanks Brain. I totally forgot about creating a variable to store the value 🤦🏻‍♂️.

Despite his clever responses, I don't think he's called Brain 🙂

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 6

Coung wrote:

Unfortunately, I don't have a say about the structure of the table. It's populated via an ETL process. I just want to create dummy data for testing so want to make sure RefNum is unique.

Just to say it out loud... using any form of MAX+1 to get the proverbial "next id" is basically doomed to failure in at least one of many ways. The people that are designing this "ETL process" need to consider the tools that they're using. A SEQUENCE or even an IDENTITY column would likely be perfect rather than any home grown form of a "GenNextID" process.

--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)

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 7

Heh, I don't mind being called "Brain", but I do agree with Phil here. I may sometimes have some good answers, but I have also been way out to lunch on things before. I learn new things all the time and have been wrong plenty of times too.

It is easy to forget the simple things if you don't use them often. Like if your primary database goal is reporting and analytics, you probably don't use variables that much in the TSQL like this. BUT if you build a lot of stored procedures, variables like this are pretty quick to come to mind.

The ONLY risk that I can see coming in with my query is you would want the system to be unchanging while it is running. The reason being if you grab the largest number and it is 100 and then before your INSERT runs, someone sneaks in and does their own insert, your "100" is no longer the correct value. You will probably want to have the whole query inside a transaction to prevent others from modifying data while you are modifying it unless you know the system will be fairly static while you run your stuff. Downside of the transaction is that you are going to be a blocker and someone may kill your query if it blocks for too long.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

arnoldnashwel SSC Rookie Points: 30 More actions · Answer 8

So, you're looking to insert a row with a RefNum as the next numeric number? One straightforward method, as suggested in the forum, is to grab the MAX(RefNum) into a variable and then use ROW_NUMBER in your INSERT SELECT statement. It's like trying to find the tallest person in a room and then lining up everyone else by increasing height. 📏

Here's a little script that plays a game of 'follow the leader' with your data:

DECLARE @MaxRef INT

SELECT @MaxRef = MAX(RefNum) FROM <your_table>

INSERT INTO <your_table>

SELECT col1, col2, col3, ..., @MaxRef + ROW_NUMBER() OVER(ORDER BY col#) as RefNum

FROM <your_source>

Now, this method might not be the Usain Bolt of efficiency, but it's like a trusty old bicycle – gets you where you need to go and it's easy to understand.

Phil Parkin SSC Guru Points: 246990 More actions · Answer 9

arnoldnashwel wrote:

So, you're looking to insert a row with a RefNum as the next numeric number? One straightforward method, as suggested in the forum, is to grab the MAX(RefNum) into a variable and then use ROW_NUMBER in your INSERT SELECT statement. It's like trying to find the tallest person in a room and then lining up everyone else by increasing height. 📏
Here's a little script that plays a game of 'follow the leader' with your data:
DECLARE @MaxRef INT SELECT @MaxRef = MAX(RefNum) FROM <your_table> INSERT INTO <your_table> SELECT col1, col2, col3, ..., @MaxRef + ROW_NUMBER() OVER(ORDER BY col#) as RefNum FROM <your_source>
Now, this method might not be the Usain Bolt of efficiency, but it's like a trusty old bicycle – gets you where you need to go and it's easy to understand.

Amusing commentary, but I can't see how your code adds anything to what Mr. Brian Gale has already posted.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

arnoldnashwel SSC Rookie Points: 30 More actions · Answer 10

arnoldnashwel

SSC Rookie

Points: 30

January 24, 2024 at 3:12 pm

#4367076

I'm sorry, friend, I didn't notice his comment.