Solved

Get indentity field

Posted on 2009-04-06
14
143 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
  • 6
  • 5
  • 3
14 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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
Comment Utility
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
Comment Utility
"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
Comment Utility
>> 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Ok, looks good. Is there anyway I can script out the formula?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
I posted the forumla above in http:#a24077380

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

Author Comment

by:Camillia
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks, i can move on with my life now :)
0

Featured Post

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)

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Server DatePart HOUR 6 31
Getting the 3 middle digits 4 31
2 comma seperated list - SQL Server 12 32
Truncate vs Delete 63 87
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video discusses moving either the default database or any database to a new volume.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now