>>>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')
>

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.

Check this blog post
http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/best-practice-don-t-not-cluster-on-uniqu