Help with Function

Help with Function

Post by Keit » Sun, 09 Nov 2003 17:06:31



I am using a table that stores the NextID values.  I have
a sp that is inserting multiple records in a table using
the following insert command:

Insert Into GroupAccess (CompID, CatalogID, GroupID,
Access)

GroupID, 1 as Access
        from Catalog

        and CatalogID not in (Select CatalogID from
#Temp1)

The Primarky key for the GroupAccess table is
GroupAccessID.  How can I get this value from the NextID
table and incorporate it into the above insert statement?

Thanks,

Keith

 
 
 

Help with Function

Post by David Porta » Sun, 09 Nov 2003 18:07:43


You don't need a "NextID" table.

Assuming this is just a single row insert:

INSERT INTO GroupAccess (groupaccessid, compid, catalogid, groupid,  access)
 SELECT (SELECT COALESCE(MAX(groupaccessid),0)+1
   FROM GroupAccess),

  FROM Catalog

   NOT IN
   (SELECT catalogid
    FROM #Temp1)

If you are inserting multiple-rows, and assuming Catalogid is the primary
key in Catalog:

INSERT INTO GroupAccess (groupaccessid, compid, catalogid, groupid,  access)
 SELECT (SELECT COALESCE(MAX(groupaccessid),0)
   FROM GroupAccess)+COUNT(*),

  FROM Catalog AS C1
  JOIN Catalog AS C2
   ON C1.catalogid >= C2.catalogid


   AND C1.catalogid
   NOT IN
   (SELECT catalogid
    FROM Temp1)
   AND C2.catalogid
   NOT IN
   (SELECT catalogid
    FROM Temp1)
  GROUP BY C1.catalogid

--
David Portas
------------
Please reply only to the newsgroup
--