January 23, 2024 at 12:07 pm
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.
January 23, 2024 at 2:22 pm
It sounds like you're trying to reinvent sequence or identity. Why not just use a sequence?
January 23, 2024 at 2:28 pm
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.
January 23, 2024 at 2:28 pm
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.
January 23, 2024 at 3:19 pm
Many thanks Brain. I totally forgot about creating a variable to store the value 🤦🏻♂️.
January 23, 2024 at 3:30 pm
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 23, 2024 at 3:59 pm
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
Change is inevitable... Change for the better is not.
January 23, 2024 at 5:56 pm
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.
January 24, 2024 at 12:15 pm
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.
January 24, 2024 at 1:05 pm
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 24, 2024 at 3:12 pm
I'm sorry, friend, I didn't notice his comment.
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