September 3, 2014 at 8:23 am
Hi all -- hopefully, this is a simple question.
I'm trying to do a simple insert into a table, something like this:
insert into sometable (ID, somecolumn)
select 'Task-ID', somevalue from SomeOtherTable
where something = 'someothervalue'
(or something to that effect)
So, the SELECT would generate something that looks like this:
ID somecolumn
-- ----------
Task-ID somevalue1
Task-ID somevalue2
Task-ID somevalue3
(etc.)
Here's where my problem comes in: ID is a PK, and needs to be unique. What I need it to do is this:
ID somecolumn
-- ----------
Task-ID.1 somevalue1
Task-ID.2 somevalue2
Task-ID.3 somevalue3
(etc.)
What I don't know is, how do I programatically generate the number sequence? Note: I do not have admin rights to the table, i.e. I cannot just change a column to IDENTITY.
Also the 'Task-ID' must remain part of the ID; in other words, I can't just generate a GUID, and it needs to be easily identifiable.
What I'm hoping to do is rewrite my SQL like this:
insert into sometable (ID, somecolumn)
select 'Task-ID.' + (generated seq #), somevalue from SomeOtherTable
where something = 'someothervalue'
I've been looking at BOL (maybe I just haven't found it yet) and (gasp!) StackOverflow, and the answers I've read have not been very helpful.
Is there an easy way to do this?
Thanks in advance!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
September 3, 2014 at 8:28 am
select 'Task-ID.' + cast(ROW_NUMBER() over(order by something) as varchar(12)) from SomeOtherTable
where something = 'someothervalue'
September 3, 2014 at 8:35 am
The solution posted will work but I am concerned about the WHY you are doing this. This seems like you are ending up with multiple values in your column now. You have the original text PLUS a number. This whole thing screams of shoving a square peg in a round hole that will cause you pain in the future because it wasn't done correctly the first time.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 3, 2014 at 8:55 am
Sean Lange (9/3/2014)
The solution posted will work but I am concerned about the WHY you are doing this. This seems like you are ending up with multiple values in your column now. You have the original text PLUS a number. This whole thing screams of shoving a square peg in a round hole that will cause you pain in the future because it wasn't done correctly the first time.
I hear you. The table in question is used for task tracking purposes, and the ID ties it to my task. Again, this is not my table, so I have little say in its design or function.
(FWIW, if it was my table, I would've used something else as the PK -- maybe an identity column or something like that.)
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
September 3, 2014 at 10:14 am
I share Sean's concern here, at least there should be a check for the key-number existence and adjustment if there is a key combination collision.
😎
September 3, 2014 at 11:17 am
Eirikur Eiriksson (9/3/2014)
I share Sean's concern here, at least there should be a check for the key-number existence and adjustment if there is a key combination collision.😎
Folks, you're preaching to the choir. Like I said, it's not my table.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
September 3, 2014 at 11:38 am
I may have missed something (I often do) but one thing to keep in mind, is sorting. Task-ID1 will not be followed by Task-ID2 if there is a Task-ID10. This may not matter but it is something to keep in mind.
September 3, 2014 at 12:51 pm
Ray K (9/3/2014)
Eirikur Eiriksson (9/3/2014)
I share Sean's concern here, at least there should be a check for the key-number existence and adjustment if there is a key combination collision.😎
Folks, you're preaching to the choir. Like I said, it's not my table.
No mate, I'm talking directly to you;-), this has nothing to do with the table but all to do with what you intend to do with the table! In simple terms, before an insert, assert that the value you are about to insert isn't already there.
😎
September 3, 2014 at 1:29 pm
Eirikur Eiriksson (9/3/2014)
Ray K (9/3/2014)
Eirikur Eiriksson (9/3/2014)
I share Sean's concern here, at least there should be a check for the key-number existence and adjustment if there is a key combination collision.😎
Folks, you're preaching to the choir. Like I said, it's not my table.
No mate, I'm talking directly to you;-), this has nothing to do with the table but all to do with what you intend to do with the table! In simple terms, before an insert, assert that the value you are about to insert isn't already there.
😎
Ah, okay, I see what you mean. :blush: Yeah, I understand the concern; in this case, it's not an issue, since "Task-ID" is unique to my task (it's not really named "Task-ID"; names are changed to protect the innocent :-)). Also, I should mention that this isn't mission-critical, so any impact (if any) would be minimal.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
September 3, 2014 at 1:35 pm
Ray K (9/3/2014)
Eirikur Eiriksson (9/3/2014)
Ray K (9/3/2014)
Eirikur Eiriksson (9/3/2014)
I share Sean's concern here, at least there should be a check for the key-number existence and adjustment if there is a key combination collision.😎
Folks, you're preaching to the choir. Like I said, it's not my table.
No mate, I'm talking directly to you;-), this has nothing to do with the table but all to do with what you intend to do with the table! In simple terms, before an insert, assert that the value you are about to insert isn't already there.
😎
Ah, okay, I see what you mean. :blush: Yeah, I understand the concern; in this case, it's not an issue, since "Task-ID" is unique to my task (it's not really named "Task-ID"; names are changed to protect the innocent :-)). Also, I should mention that this isn't mission-critical, so any impact (if any) would be minimal.
....until it breaks....\o/......./\..... (my impression of the great white 😉
😎
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply