SQL Server Performance Forum – Threads Archive

Problem in insert

Hello I wnat to know how to insert into a table with only one column which is set to autoincrement? Description The table is Entity(ID)
Id is a primary key and also set identity(1,1)i.e autoincrement now how to insert into this table. If u have any suggestions pls tell me This table is used to keep track of the records and its related value in other tables. this EntityID is used along with ValueTypeID to find the repective value From Value Table. Thanks in advance
Pallavi

SET IDENTITY_INSERT table_name ON should do the trick!
‘I reject your reality and substitute my own’ – Adam Savage

and SET IDENTITY_INSERT table_name OFF after you finish [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

Add another column, entityName or EntityDescription or something like that.

Thanks for all those comments, I removed the constraint identity,
and now whenever I am inserting records into this table I am retriving max, add one to max and insert record. Pallavi

>>I wnat to know how to insert into a table with only one column which is set to autoincrement? What is the purpose of having only one column in the table with identity property? It doesnt make any sense to me [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

hi, pallavi what actually u want to do with this identity column??????? do u want to continue with ur autoincrement column or just want to off once created and inserted value with respect to existing record?????? i want to know this b`coz i have implemented exactly the same thing bye [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] ekta jaiswal

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Madhivanan >>I wnat to know how to insert into a table with only one column which is set to autoincrement? What is the purpose of having only one column in the table with identity property? It doesnt make any sense to me [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail <hr height="1" noshade id="quote"></blockquote id="quote"> [B)][:0][?] not clear to me too….table with only column..[|)][xx(]

Hello
we are gobally storing the values for entities for different tables like company client, product so we need to keep track of for which this enntity belong to,
if we use id from respective tables there is possiblity of duplication
so we need this table with only one field.
there is no need to add another field in this table. ekata
i have removed the identity from this table
whenever i want to add a value in this table
first get max(id) , then insert the record in the table by max+1
but u need to keep this column as primary key
otherwise there can be dulpication of records. Pallavi

You may have serious performance and/or consistency problems with that approach.

pallavi/ekata – I am not sure how you are using it but I know in my case we removed that concept. I was handling an healthcare application that was already using this concept in which Entity ( exactly the same name as yours ) table stored records for all the other healthcare entities in the application. However we had other columns other than identity. Over a period of time, this lead to perf problems because much of the transactions have to go thru this table and secondly this was done for ALL the other entities irrespective of whether it serves the purpose or not. So after careful evaluation I presented the plan for this removal and successfully implemented it. So careful with this design if you want to use it.

]]>