Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2012
    Posts
    3

    Unanswered: Want to insert from two tables into a single with a hard coded value

    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?

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    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.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    Code:
    insert into TABLE1(THING, PERSONORGROUP, ACCESSRIGHTS)
    select T2.SYSTEM_ID, T23.SYSTEM_ID, 255
    from TABLE2 AS T2 
    	INNER JOIN TABLE2 AS T22 ON
    		T2.AUTHOR = T22.SYSTEM_ID 
    	CROSS JOIN TABLE2 AS T23 
    WHERE T22.USER_ID = 'USER1' AND
    	T23.USER_ID = 'USER2'
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Feb 2012
    Posts
    3

    Got it...

    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

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Feb 2012
    Posts
    3

    Typo in my paste...

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •