I have 3 tables:
Employees with the fields:idEmployee and employeeName
Roles with the fields:idRole and roleName.
An employee can have one or many roles.
I created the table EmployeeRoles with the fields: id,idEmployee,idRole.
idEmployee and idRole are foreign keys.
I want to insert a new employee into Employees table, but I have to insert idEmployee field into EmployeeRoles table.
How can I do this?
Thanks
This should be handled by your application. If you are using identity fields you can use the SCOPE_IDENTITY function to return the last value for the identity column.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Hi,Can you please help me with the sintax?
Thanks|||To create a table, you can do something like:
CREATE TABLE Employee
(
EmployeeID INT NOT NULL
,EmployeeFirstName VARCHAR(15) NOT NULL
,EmployeeLastName VARCHAR(15) NOT NULL
)
CREATE TABLE Role
(
RoleID INT NOT NULL
,RoleName VARCHAR(15) NOT NULL
)
CREATE TABLE EmployeeRole
(
RoleID INT NOT NULL
,EmployeeID INT NOT NULL
)
INSERT Employee
(
EmployeeID
,EmployeeFirstName
,EmployeeLastName
)
VALUES
(
1
,'John'
,'Wayne'
)
INSERT Role
(
RoleID
,RoleName
)
VALUES
(
1
,'Rooster Cogburn'
)
INSERT EmployeeRole
(
RoleID
,EmployeeID
)
VALUES
(
1
,1
)
As WesleyB mentioned, you can take this a step further and make the ID columns INDENTITY columns and use the SCOPE_INDENTITY function to retrieve the value for the most recently inserted row. You can also add some declarative referential integrity if you wish. Check out Books Online for more information on this.
BTW - I'm writing this without access to a SQL Server to test the scripts so please forgive any typos.
HTH...
Joe
|||Ok,Thanks.
I used scope_identity and it worked.|||
I addition you could have used OUTPUT to redirect the output to a resultset with the assigned IDENTITIES per row. This feature is SQL Server 2005 and above only.
Jens K. Suessmeyer
http://www.sqlserver2005.de
sql
No comments:
Post a Comment