Solved

Get indentity field

Posted on 2009-04-06
14
153 Views
Last Modified: 2012-05-06
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..
0
Comment
Question by:Camillia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
14 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24077221
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24077242
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
 
LVL 7

Author Comment

by:Camillia
ID: 24077341
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24077366
>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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24077380
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24077393
"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
 
LVL 7

Author Comment

by:Camillia
ID: 24077694
>> 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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 24077757
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
 
LVL 7

Author Comment

by:Camillia
ID: 24078028
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
 
LVL 7

Author Comment

by:Camillia
ID: 24078397
Ok, looks good. Is there anyway I can script out the formula?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24078597
I posted the forumla above in http:#a24077380

alter table table1 add NumberDisplay = [code] + ltrim(str(number))
0
 
LVL 7

Author Comment

by:Camillia
ID: 24078680
tried this and get "syntax error '=' "

alter table order add NumberDisplay = rtrim([code]) + cast([Number] as varchar(50))
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 250 total points
ID: 24078864
Sorry.  I think it's "as" not "=".

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

0
 
LVL 7

Author Closing Comment

by:Camillia
ID: 31567008
Thanks, i can move on with my life now :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question