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

Thanks, I thought about default values, but for the unique keys I create the key in VFP, because I need the key before inserting the record in SQL server (because the creating of new records in parent and child tables are handled in the data classes). I thought this gives me more flexibility. For CreateD and CreateT: back when I used VFP tables I found it easier to have the "Date" field separate to make queries simpler based on date, otherwise you are forced to use the BETWEEN Starttime and Endtime for otherwise simple queries, I used to like the flexibility to handle these updates in the data logic rather than the database backend. I guess that's mostly because of my VFP background, to be obsessed with the business code controlling the database and not vice versa. But I do see your point.