The following code works. However, I am trying to preform this same function multiple times throughout an SQL. Therefore, I would like to declare a variable so the user only has to update that with the logon names at the beginning of the SQL.
INSERT user_role (logon, role_name)
SELECT logon, 'QUERY' AS 'role_name'
FROM user_table
WHERE logon NOT IN ( 'bill', 'tim', 'dave' )
When I add the variable @Names, the IN operator doesn't return the correct results. It is basically ignores the variable and inserts all users into the table. If I change "( @Names )" to just "@Names" in the WHERE statement I get an incorrect syntax error near keyword @Names.
DECLARE @Names VARCHAR(100)
SELECT @Names = " 'bill', 'tim', 'dave' "
INSERT user_role (logon, role_name)
SELECT logon, 'QUERY' AS 'role_name'
FROM user_table
WHERE logon NOT IN ( @Names )
I did come across the EXEC function, but it doesn't appear to work in conjunction with the INSERT function. I get an incorrect syntax error near keyword EXEC.
DECLARE @Names VARCHAR(100)
SELECT @Names = " 'bill', 'tim', 'dave' "
INSERT user_role (logon, role_name)
EXEC("SELECT logon, 'QUERY' AS 'role_name'
FROM user_table
WHERE logon NOT IN ("+@Names+")")
I even tried changing up the sequence of where the INSERT statement was and this didn't work either. I get an incorrect syntax error near keyword FROM.
DECLARE @Names VARCHAR(100)
SELECT @Names = " 'bill', 'tim', 'dave' "
EXEC("SELECT logon, 'QUERY' AS 'role_name'
INSERT user_role (logon, role_name)
FROM user_table
WHERE logon NOT IN ("+@Names+")")
I am trying to replicate this SQL to use at multiple sites and minimize the amount of user interaction to make it work. Hopefully there is a simple solution to this problem.
In the last one code you you have to change the INSERT and SELECT order:
DECLARE @Names VARCHAR(100)
SELECT @Names = " 'bill', 'tim', 'dave' "
EXEC("INSERT user_role (logon, role_name)
SELECT logon, 'QUERY' AS 'role_name'
FROM user_table
WHERE logon NOT IN ("+@Names+")")