question

Katie 1 avatar image
Katie 1 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
sql-server-2008insert
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kevin Feasel avatar image
Kevin Feasel answered
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/).
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
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.
0 Likes 0 ·
TimothyAWiseman avatar image
TimothyAWiseman answered
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
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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?
0 Likes 0 ·
Sorry, I completely misunderstood what you were asking. I think Kevin covers it nicely though.
0 Likes 0 ·
Jos Menhart avatar image
Jos Menhart answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.