Adding current_value in a table column in sql server

Question

I want to insert the current value of a sequence (which is a Primary Key in table1) into a column of table2. I am using : MS SQL SERVER 2016

QUERY I tried:

INSERT INTO [dbo].[Employee] ([EMP_ID], [DEPT_ID], [DESCRIPTION]) 
VALUES (NEXT VALUE FOR EMP_ID_SEQ, 
        SELECT current_value FROM sys.sequences WHERE [name]= 'DEPT_ID_SEQ', 
        NULL)

ERROR:

Incorrect syntax near the keyword 'SELECT'.

If I run SELECT current_value FROM sys.sequences WHERE [name]= 'DEPT_ID_SEQ' separately, it works well.

The value of DEPT_ID is a FK to DEPT table and is also a sequence. Let me know any alternative way to it. Preferably in a single statement since I've to mould it in liquibase tags and run the entire query via liquibase update.

Answer 1

Use an INSERT INTO ... SELECT:

INSERT INTO [dbo].[Employee] ([EMP_ID], [DEPT_ID], [DESCRIPTION])
CONVERT(int, SELECT NEXT VALUE FOR EMP_ID_SEQ), current_value, NULL
FROM sys.sequences WHERE [name] = 'DEPT_ID_SEQ';