>INSERT INTO EmpID (emKey) OUTPUT Inserted.emID, NewID(), LTRIM(STR(Inserted.emID)), > 'Einstein', 'Albert', CAST('03/15/1979' AS Date), 2, '1234567', 1979031512, 1, > 'DEPARTMENTKEY', 'VCC', GetDate(), GetDate() >INTO Employees (emID, emKey, emNumber, > emName, emFirst, emBirthDat, emSex, emPersNr, emIDNr, emCivil, > emDeKey, CreateU, CreateD, CreateT) >VALUES (NEWID()) >>Previously I had a subselect, so I did not use "VALUES" but SELECT.
>INSERT INTO Employees (emKey, emName, emDeKey) >SELECT NewID(), 'Einstein', (SELECT deKey FROM Departments WHERE deCode = 'NEW') >I see, you just need to declare a new variable, e.g.
declare @DepKey varchar(10) -- use correct type here select @DepKey = deKey from dbo.Departments where deCode = 'NEW' INSERT INTO EmpID (emKey) OUTPUT Inserted.emID, NewID(), LTRIM(STR(Inserted.emID)), 'Einstein', 'Albert', '19790315', 2, '1234567', 1979031512, 1, 'DEPARTMENTKEY', 'VCC', CURENT_TIMESTAMP, CURRENT_TIMESTAMP, @DepKey INTO Employees (emID, emKey, emNumber, emName, emFirst, emBirthDat, emSex, emPersNr, emIDNr, emCivil, emDeKey, CreateU, CreateD, CreateT, DeKey) VALUES (NEWID())BTW, you may want to use default values instead for the CreateD and CREATE_T (why do you need 2 columns?) and for the empKey you can use NewSequentialID() instead of NewId() for the default value.