Get indentity field

i have table like this

  Table1:
   ID      // GUID, PK
   Number //****this is an identity field
   NumberDisplay


 I have a stored proc that does this:

   declare @code as varchar(5)
   Select @code = code from AnotherTable where ...

   insert into Table1 (Id,Number,NumberDisplay
    select  ID, Number, @code + cast([Number] as varchar(50))
     from Table1

This gives me an error :
Violation of PRIMARY KEY constraint 'PK_ORdNumberr'. Cannot insert duplicate key in object 'dbo.Table1'.

How can I concat that Number with the Code and insert at once??
If I cant insert at once, how can I get that Number and then maybe do an update right after the insert...

Any other ways of doing this? Issue is that [Number] has to be Identity..generated automatically... dont want
 to manually generate that number..
LVL 8
CamilliaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you should have this:
ID      // GUID, PK
Number //****this is an identity field
Code   //the value for @code (alone)
NumberDisplay // a computed field, doing the Code + cast(number) as identity for you.

Open in new window

0
BrandonGalderisiCommented:
You can't get the value if the identity, until it's inserted.  What is your PK?  Number or ID?
   declare @code as varchar(5)
,@number int
   Select @code = code from AnotherTable where ...
 
   insert into Table1 (Id,NumberDisplay)
    select  ID, @code 
     from Table1
select @number = scope_identity()
 
update table1
set numberdisplay= numberdisplay + ltrim(str(@number))
where number=@number

Open in new window

0
CamilliaAuthor Commented:
I can NOT do a computed field. That "Code" has to be read from another table.

angelll - this is again that question that i had opened. I just cant do a computed field when i have to read one field FROM ANOTHER table. computed field can only be used for columns IN THE SAME table.

Brandon - i did exactly what you have. Question..not bad for performance? i know my manager will be at my throat with this... anyway i can prove that it's not bad for performance?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I can NOT do a computed field. That "Code" has to be read from another table.

still, why not store the value in your table? you want to have the value in there anyway?!
0
BrandonGalderisiCommented:
Here's what angel is suggesting.  It CAN be a computed field because he is proposing adding [code] to the table and displaying it as a computed field.
alter table table1 add [Code] varchar(255)
alter table table1 add NumberDisplay = [code] + ltrim(str(number))
 
 
declare @code as varchar(5)
Select @code = code from AnotherTable where ...
 
insert into Table1 (Id,@code)
select  ID, @code 
FRom SomeOtherTable

Open in new window

0
BrandonGalderisiCommented:
"Brandon - i did exactly what you have. Question..not bad for performance? i know my manager will be at my throat with this... anyway i can prove that it's not bad for performance?"

It's a balancing act.  If you will be selecting more than inserting/updating, then my method will be faster.  If that display column is seldomly referenced then it will be faster to compute it when necessary.

Also, you never answered the question about indexes and keys.
0
CamilliaAuthor Commented:
>> why not store the value in your table? you want to have the value in there anyway?!

ahhh, I dont have the code stored on the table. I have the ID that references that "code" stored on the table.  So, Table2 has ID, CODE,DateTime, etc and I have 'ID' stored on Table1. Big change if I remove the ID and change it to "code". Now, if I add "Code" to it as well, then data will be redundant??

>> If you will be selecting more than inserting/updating
   I will insert a row, then it's selected and updated. I think it's equal when it comes to selecting/inserting/updating this table.

>> indexes and keys
    The key of this table is a GUID. The Identity field also has an index on it. The second field that I'm
 concating (code+number)..is varchar(50) and I put an index on that as well...




0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
let me put this straight:

if you want to have NUMBERDISPLAY field to have the code value in there, then it is THERE in that table.
as a copy of the other table.

so either:
* you leave the field numberdisplay out of this table, and solve this with a view, for example, or if you want to search by the combined value, with the searched value being split up and to search in both tables with a join

* you add the code field to this table, being a copy of the data of the other, and then being able to search on the combined (computed) field (which can also take a index)


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CamilliaAuthor Commented:
I think I will copy the CODE to TABLE1. Then have a computed field.

I cant use view or table join. My manager says other systems will use this column(numberdisplay) to look up the code+number field. Field has to be there.

Let me create the column Code on this table and try the compted field. thanks
0
CamilliaAuthor Commented:
Ok, looks good. Is there anyway I can script out the formula?
0
BrandonGalderisiCommented:
I posted the forumla above in http:#a24077380

alter table table1 add NumberDisplay = [code] + ltrim(str(number))
0
CamilliaAuthor Commented:
tried this and get "syntax error '=' "

alter table order add NumberDisplay = rtrim([code]) + cast([Number] as varchar(50))
0
BrandonGalderisiCommented:
Sorry.  I think it's "as" not "=".

alter table table1 add NumberDisplay as [code] + ltrim(str(number))

0
CamilliaAuthor Commented:
Thanks, i can move on with my life now :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.