I am trying to insert a relationship into my relationship table UsuariosProyectos
with a INSERT
nested. I also researched how I can get a IDENTITY
and found this answer...
@bdukes: The statement
clause will allow you to access each row that was inserted via the same statement... avoids parallel processing errors ...OUTPUT
INSERT
so i implemented it in my stored procedure
.
Data structure:
CREATE TABLE [dbo].[Users](
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Nombre] [varchar](50) NOT NULL,
[Password] [varchar](50) NOT NULL)
CREATE TABLE [dbo].[Proyectos](
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Name] [varchar](50) NOT NULL)
CREATE TABLE [dbo].[UsersProyectos](
[IDUsers] [int] NOT NULL,
[IDProyectos] [int] NOT NULL
)
ALTER TABLE [dbo].[UsersProyectos] WITH CHECK ADD CONSTRAINT [FK_DefaultUsersProyectos_DefaultProyectos] FOREIGN KEY([IDProyectos])
REFERENCES [dbo].[Proyectos] ([ID])
ALTER TABLE [dbo].[UsersProyectos] CHECK CONSTRAINT [FK_DefaultUsersProyectos_DefaultProyectos]
ALTER TABLE [dbo].[UsersProyectos] WITH CHECK ADD CONSTRAINT [FK_DefaultUsersProyectos_Users] FOREIGN KEY([IDUsers])
REFERENCES [dbo].[Users] ([ID])
ALTER TABLE [dbo].[UsersProyectos] CHECK CONSTRAINT [FK_DefaultUsersProyectos_Users]
Stored procedure:
create procedure insertRelationUserProject
@IDUsuario int,
@Nombre varchar(50)
as
insert into UsersProyectos(IDUsers, IDProyectos)
select @IDUsuario, Proyectos.ID
from (
insert into Proyectos(Name) -- Insert anidado
output inserted.ID -- obtengo el ID insertado
values(@Nombre)
) Proyectos
But I am getting the following error.
The target table 'UsersProjects' of the INSERT statement cannot be on either side of a relationship (primary key, foreign key) when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement. A reference constraint 'FK_DefaultUsersProyectos_Users' was encountered.
I hope someone can help me, Greetings.
The exception you get is based on a restriction on the use of the OUTPUT clause:
OUTPUT Clause (Transact-SQL)
Therefore, one output is to send the results of the rows affected by the operation to a temporary table or a variable of type table, for example: