CODEWITHSUNDEEP
sql-serversql-server-2012insertsubquery
Umut K
Umut K

Reputation: 1388

insert query with subqueries

I am trying to insert some values (CIID and AID) taken from another tables, but as not an expert, I couldn't manage to do it.

Insert query :

INSERT INTO INST_ACTIVE_ACTIONS act 
   (act.CIID, act.AID, act.STEPNUM, act.CREATEDATE) 
VALUES 
   (CIID , ,0,GETDATE())

The CIID Query is :

SELECT  C.CIID FROM INST_COURSE C 
LEFT JOIN INST_ACTIVE_ACTIONS AA ON (AA.CIID = C.CIID)
LEFT JOIN INST_TASKS T ON (T.CIID = C.CIID) 
LEFT JOIN SYS_SCH_ACTION SCH ON (SCH.CIID = C.CIID)
LEFT JOIN SYS_SUB_STACK SUB ON (SUB.RETURN_CIID=C.CIID)
WHERE C.COMPLETED IS NULL AND AA.AID IS NULL AND T.AID IS NULL AND SCH.AID IS NULL AND SUB.RETURN_AID IS NULL

AID Query is : 

SELECT TOP 1 ca.AID 
FROM INST_COMPLETE_ACTIONS CA 
  INNER JOIN TMPL_ACT_MASTER TAM ON CA.AID=TAM.AID  
WHERE ca.CIID =c.CIID  ORDER BY TSTAMP DESC

act.CIID = C.CIID = ca.CIID and CA.AID = act.AID

Edited :

the last query is 

 INSERT INTO INST_ACTIVE_ACTIONS  (CIID,AID,stepnum,CREATEDATE
            )

            VALUES

       (
                 (SELECT    c.CIID
                 FROM      INST_COURSE  C
                 LEFT JOIN INST_ACTIVE_ACTIONS AA
                 ON        (
                                     aa.CIID = c.CIID)
                 LEFT JOIN INST_TASKS T
                 ON        (
                                     t.CIID = c.CIID)
                 LEFT JOIN SYS_SCH_ACTION SCH
                 ON        (
                                     sch.CIID = c.CIID)
                 LEFT JOIN sys_sub_stack SUB
                 ON        (
                                     sub.RETURN_CIID = c.CIID)
                 WHERE     c.completed IS NULL
                 AND       aa.AID IS NULL
                 AND       t.AID IS NULL
                 AND       sch.AID IS NULL
                 AND       sub.return_AID IS NULL),
       (
                  SELECT TOP 1
                             ca.AID
                  FROM       INST_COMPLETE_ACTIONS CA
                  INNER JOIN tmpl_act_master TAM
                  ON         ca.AID=tam.AID

                  ORDER BY   tstamp DESC 
                  ),
       0,
       Getdate() )

but i get an error as

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.

Upvotes: 0

Views: 65

Answers (2)

Edward
Edward

Reputation: 896

Just condensed though its real busy if you ask me.

INSERT INTO INST_ACTIVE_ACTIONS  
(CIID, AID, STEPNUM, CREATEDATE) 
--OUTPUT INSERTED.* --if you want to see what was inserted uncomment this 
(SELECT  C.CIID,
    (SELECT TOP 1 CA.AID 
        FROM INST_COMPLETE_ACTIONS CA 
        INNER JOIN TMPL_ACT_MASTER TAM ON CA.AID=TAM.AID  
        WHERE CA.CIID =C.CIID  ORDER BY TSTAMP DESC)
        , 0,GETDATE())
FROM INST_COURSE C 
LEFT JOIN INST_ACTIVE_ACTIONS AA ON (AA.CIID = C.CIID)
LEFT JOIN INST_TASKS T ON (T.CIID = C.CIID) 
LEFT JOIN SYS_SCH_ACTION SCH ON (SCH.CIID = C.CIID)
LEFT JOIN SYS_SUB_STACK SUB ON (SUB.RETURN_CIID=C.CIID)
WHERE C.COMPLETED IS NULL AND AA.AID IS NULL AND T.AID IS NULL AND SCH.AID IS NULL AND SUB.RETURN_AID IS NULL)

Revised. tested, and working on my end. I still think if you have control on these tables you might look into the concept of Normalizing your database tables.

Upvotes: 1

InGeek
InGeek

Reputation: 2682

You can always perform something like this, and use as many sub queries as needed. Make sure that the types are matching on both sides.

Insert into table1 (value1, value2) Select val1,val2 from table2 ...

edit

INSERT INTO INST_ACTIVE_ACTIONS act (act.CIID, act.AID, act.STEPNUM, act.CREATEDATE) Select (SELECT C.CIID FROM INST_COURSE C LEFT JOIN INST_ACTIVE_ACTIONS AA ON (AA.CIID = C.CIID) LEFT JOIN INST_TASKS T ON (T.CIID = C.CIID) LEFT JOIN SYS_SCH_ACTION SCH ON (SCH.CIID = C.CIID) LEFT JOIN SYS_SUB_STACK SUB ON (SUB.RETURN_CIID=C.CIID) WHERE C.COMPLETED IS NULL AND AA.AID IS NULL AND T.AID IS NULL AND SCH.AID IS NULL AND SUB.RETURN_AID IS NULL), (SELECT TOP 1 ca.AID FROM INST_COMPLETE_ACTIONS CA INNER JOIN TMPL_ACT_MASTER TAM ON CA.AID=TAM.AID WHERE ca.CIID =c.CIID ORDER BY TSTAMP DESC act.CIID = C.CIID = ca.CIID and CA.AID ),0,GETDATE()

Upvotes: 0

Related Questions