Hello, first post here. Thanks in advance for the help.

I'm trying to insert data into a table from two tables into a single table along with a hard coded value.

insert into TABLE1
(THING,PERSONORGROUP,ACCESSRIGHTS)
VALUES
((select SYSTEM_ID from TABLE2 where
AUTHOR IN (select SYSTEM_ID from TABLE2 where USER_ID
=('USER1'))),(select SYSTEM_ID from TABLE2 where USER_ID
=('USER2')),255)

I get the following-
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Do I need to use a cursor?

No.

The errror message says that one of your subqueries is returning more than one records-worth of results when it is expecting the results of a single record.

You've got three subqueries there. Look carefully at each and determine which one is returning multiple records.

Aside from that, I see a number of issues here.

You are comparing AUTHORs to SYSTEM_IDs. Does the ID number used in AUTHOR field originate as a SYSTEM_ID?

Drop the parenthesis around the literals USER1 and USER2.

What if either one of those two subqueries that get assigned to THING and PERSONORGROUP return a null. Do you want to write a record of two nulls and the number 255? You will be able to do that once, but thereafter you are going to run into trouble.

That's quite a remarkable query you got there. I can't tell if it is correct or not. At least it made me scratch my head. You should try to create a SELECT statement that gives you the exact result set that you want to INSERT into TABLE1.

I tried to rewrite your query, but most likely I'm miles off of what you intended.
First execute only the part after and including SELECT. As long as it doesn't give you the result you expect, tweak the select statement until it does. Only when that part gives you the exact result set that you want, execute the INSERT part too.
If you need extra help, post your CREATE script of TABLE2 and give some sample data and the records that you want to insert in TABLE 1, based on your sample data of TABLE2.

Thanks to all. Wim's post got had me go at it from a different angle.

Here's the layout.
SECURITY tbl contains document security
THING-System ID of document
PERSONORGROUP-System ID of USER from USERS table
ACCESSRIGHTS-level of access

DOCUMENTS table, 1 row per document
SYSTEM_ID - System ID of document
AUTHOR-System ID from USERS tbl

USERS
SYSTEM_ID
USER_ID


insert into SECURITY
(THING, PERSONORGROUP, ACCESSRIGHTS)
select D.SYSTEM_ID, U.SYSTEM_ID, 255
from USERS U, DOCUMENTS D
WHERE U.USER_ID = '<user to add to the ACL>'
and U.AUTHOR IN (
SELECT SYSTEM_ID from USERS where USER_ID = '<author of documents to be secured>')

Cheers!

TC

There is no JOIN criteria between USERS and DOCUMENTS, resulting in a Cartesian product (when there are 100 records in USERS and 200 records in DOCUMENTS, The result set will have 100 * 200 = 20 000 records). Most likely that is not what you want.

If you want fewer records, you must add some criteria to link the USERS to the DOCUMENTS, like DOCUMENTS.AUTHOR = USERS.USER_ID.

The "and U.AUTHOR IN (SELECT SYSTEM_ID from USERS where USER_ID = '<author of documents to be secured>')" feels wrong.

Thanks for following up Wim, what you caught was a typo in my paste. I attempted to make the script somewhat generic for my post. What I actually ran, which worked was:
insert into SECURITY
(THING, PERSONORGROUP, ACCESSRIGHTS)
select D.SYSTEM_ID, U.SYSTEM_ID, 255
from USERS U, DOCUMENTS D
WHERE U.USER_ID = '<user to add to the ACL>'
and D.AUTHOR IN (
SELECT SYSTEM_ID from USERS where USER_ID = '<author of documents to be secured>')