Search code examples
databasesql-server-2014

How do I insert values from another table and inserting a new value?

This is my query but it creates a new row and the only value is from the next insert query. How do I combine them? Any type of response will be appreciated. Thanks in advance!

INSERT INTO empAttend (empID, empLName, empFName) SELECT empID, empLName, empFName FROM emp WHERE empUser = 'name';
INSERT INTO empAttend (empTimeIn) VALUES (SYSDATETIMEOFFSET());
Solution

  • Each INSERT statement creates new row(s). The best solution would be to specify all values for all columns in the single statement:

    INSERT INTO empAttend (empID, empLName, empFName, empTimeIn) 
SELECT empID, empLName, empFName, SYSDATETIMEOFFSET()
FROM emp 
WHERE empUser = 'name';

    When you cannot do the above for some reason you need to UPDATE the existing row(s) instead. To do this you would need to have identifying information about the row(s) you need to update, be it a (primary) key, or some other combination of column data to identify the rows.

    Alternatively, create a default constraint on your table that populates the column when no value is provided. For example you could set the default value of empTimeIn to SYSDATETIMEOFFSET(), then simply omit the second statement to have this default applied. I do not know your business requirements but this may be a good option.