>>Thanks, I could make it work, finally! You and Kevin already gave me that idea before, but I did not understand to add all the additional values in the OUTPUT part to be inserted. It is a little bit counter intuitive, and I also had the problem that I inserted from a select. Unfortunately using OUTPUT does not allow sub-selects, therefore I got an error at first. But I could work around it.
>
>If you post what exactly you're trying to do (the whole code), I can show how to make it work. It should not matter if you're using SELECT statement to insert into another table or VALUES clause.
This is the code that is now working, except that the part where it says 'DEPARTMENTKEY' I used to have a sub-select.
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')
Christian Isberner
Software Consultant