asked
Insert issue
declare @id int; select @id = @@IDENTITY from cimO.Organisation; INSERT INTO [CIMO].[CIMO].[Organisation] ([ParentOrganisation], [aliasName], [description], [localName], [mRID]], [gs_website]) values (@ID,1,ABC.. ETC) INSERT INTO [CIMO].[CIMO].[Customer]([organisationID], [pucNumber], [specialNeed], [vip], [PlannedOutage], [customerKind]) VALUES ( @ID, 1,1,1.. ETC) INSERT INTO [CIMO].[CIMO].[OTHERTABLE]([organisationID], [pucNumber], [specialNeed], [vip], [VALUES ( @ID, 1,1,1.. ETC) How can i have same @id populated to all the 3 different tables , which have an identity column set on all the tables. select @id = @@IDENTITY from cimO.Organisation; INSERT INTO [CIMO].[CIMO].[Organisation]([ParentOrganisation], [aliasName], [description], [localName], [mRID]], [gs_website]) values (@ID,1,ABC.. ETC) select @id = @@IDENTITY from cimO.customer; INSERT INTO [CIMO].[CIMO].[Customer]([organisationID], [pucNumber], [specialNeed], [vip], [PlannedOutage], [customerKind])VALUES ( @ID, 1,1,1.. ETC) Thanks
It sounds like SCOPE_IDENTITY() is what you're looking for. After you do an insert into the table with the identity column, do something like the following: declare @NewRecordId int; select @NewRecordId = SCOPE_IDENTITY(); Then, you can use @NewRecordId for the rest of your inserts. [Here is more information on the topic](
http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/).
Ah, that's not quite what I was thinking. The trick is that you don't need to define the identity key--that's why you'd have an identity key in the first place. Also, let's add another field to organisation1 (so I have something to insert into that table like you would a normal table): I'll just call it "somevalue." Instead, try something like: insert into organisation1(somevalue) values('The rest of your fields'); declare @NewRecordId int; select @NewRecordId = SCOPE_IDENTITY(); insert into customer1(custid) values(@NewRecordId); After doing this, customer1 will have the organization's identity column's value. Please give that a try and see if it's more to your liking.
Kevin, this i what i did, create table organisation1 ( orgid int identity (1,1) primary key ) create table customer1 ( custid int primary key ) ALTER TABLE customer1 ADD CONSTRAINT fk_org_cust FOREIGN KEY (custid) REFERENCES organisation1(orgID) GO set identity_insert organisation1 on declare @NewRecordId int; select @NewRecordId = SCOPE_IDENTITY(); insert organisation1(orgid) values (@NewRecordId) insert customer1(custid) values (@NewRecordId) the error emssage i get is Msg 515, Level 16, State 2, Line 5 Cannot insert the value NULL into column 'orgid', table 'WORK_SPACE.dbo.organisation1'; column does not allow nulls. INSERT fails. The statement has been terminated. Msg 515, Level 16, State 2, Line 6 Cannot insert the value NULL into column 'custid', table 'WORK_SPACE.dbo.customer1'; column does not allow nulls. INSERT fails. The statement has been terminated.
If you mean that you want to set the identity column on a particular insert to the same value for all 3 tables in this batch, then you have to first make sure that that value is available in all 3 tables, then turn identity insert on, then make the insert. There is a good reference at [
http://www.sqlteam.com/article/how-to-insert-values-into-an-identity-column-in-sql-server][1] Now, with that said, my advice is don't try to do that kind of thing. If you are trying to use this as a foreign key reference, then do not let the other two tables have identity columns. Instead, insert the value from the identity column (or better, a non-identity primaryy key) of the primary table into the other two. [1]:
http://www.sqlteam.com/article/how-to-insert-values-into-an-identity-column-in-sql-server
Timothy, apologize, i didnt compose the question right, only one table that is the organization table has the identity, the other two tables carry over the same id populated in the first table (organisation). they have a foriegn key relationship, but it is one to one. that is the design. each row is associated with one another with the id populated in the first table. in that case how do i use the identity populated in the organisation and pass it to the other tables?
Sorry, I completely misunderstood what you were asking. I think Kevin covers it nicely though.
I would go with Kevin's answer as well, and create foreign keys when referencing other table's identity columns. I would like to add one thing: if I would do this I would insert using the OUTPUT clause to return the actual Id that has been inserted. The reason for this is that @@scope_identity sometimes returns the wrong values, as per [This Microsoft Connect issue][1] and [this one][2]. I had read about this before but I have to credit this [sqlservercentral forum thread][3] for the details I copied into here. [1]:
http://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value [2]:
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=476577 [3]:
http://www.sqlservercentral.com/Forums/Topic1068641-392-1.aspx#bm1068649
Another advantage of the OUTPUT clause over scope_identity() is that if you have a set-based insert statement which affects many records and would like to have the list of ids of all newly inserted records then OUTPUT is the way to go, i.e.
use AdventureWorks;
go
create table dbo.Boloney
(
RecordID int identity(1, 1) primary key clustered,
Other varchar(128) null);
go
declare @id int;
declare @t table (id int);
insert into dbo.Boloney (Other)
select top 10 [name] from sys.objects;
-- you are limited to only getting the last id
select @id = scope_identity();
select @id id;
-- this will give you the id list of all affected records
insert into dbo.Boloney (Other)
output inserted.RecordID into @t
select top 10 'z' + [name] from sys.objects;
select stuff(list.id, 1, 2, '') list
from
(
select ', ' + cast(id as varchar(10))
from @t for xml path('')
) list(id);
go
-- results:
id
-----------
10
list
--------------------------------------
11, 12, 13, 14, 15, 16, 17, 18, 19, 20
