Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2003
    Posts
    11

    Unhappy Unanswered: Any SQL Gods out there????

    I have a SP that I am trying to finalize however; my inexperience is showing itself on this one.

    History:
    3 Tables: Tooldb - Employee - ToolUserdb

    Scenario:
    I have a webform in c# that gathers data concerning internal tools(applications) that are written in-house. One of the fields is a listbox of names pulled from the Employee table called Creator. When the form is submitted I need to have the list of selected employees published to the ToolUserdb.

    My SP:
    ALTER PROCEDURE dbo.InsertTool
    (
    @ToolName nvarchar(250),
    @Platform nvarchar(250),
    @Vendor nvarchar(250),
    @Subplatform nvarchar(250),
    @Submitter nvarchar(250),
    @Finders nvarchar(250),
    @LTD nvarchar(50),
    @JobArea nvarchar(250),
    @Func nvarchar(250),
    @Owners nvarchar(250),
    @Active nvarchar(250),
    @Version numeric,
    @Build numeric,
    @CWSTD nvarchar(50),
    @Status nvarchar(250),
    @Cost numeric,
    @Notes nvarchar(250),
    @Keywords nvarchar(250),
    @Links nvarchar(250),
    @Paths nvarchar(250),
    @eid int,
    @tdbid int,
    @Creator nvarchar(250)
    )
    AS

    INSERT INTO [ToolDB] (ToolName, Platform, Vendor, Subplatform, Submitter, Finders, LTD, JobArea, Func, Owners, Active, Version, Build, CWSTD, Status, Cost, Notes, Keywords, Links, Paths)
    VALUES
    (@ToolName, @Platform, @Vendor, @Subplatform, @Submitter, @Finders, @LTD, @JobArea, @Func, @Owners, @Active, @Version, @Build, @CWSTD, @Status, @Cost, @Notes, @Keywords, @Links, @Paths)
    INSERT INTO ToolUsersdb (@Creator) SELECT + @eid + ',[ID] FROM Employee + @tdbid + ',[ID] FROM ToolDB IN (@Creator)

    Error:
    Incorrect syntax near keywork IN (referring to last insert statement).

    Can someone tell me how I can get this multi-insert stmt. to work?

    Thank you!

    Tim

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    What's the DDL for ToolUsersDB table?

    INSERT INTO ToolUsersdb (EmpID, ToolID, Creator) values (@eid, @tdbid, @Creator) ???
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Dec 2003
    Posts
    11
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ToolUsersdb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[ToolUsersdb]
    GO

    CREATE TABLE [dbo].[ToolUsersdb] (
    [id] [int] NOT NULL ,
    [eid] [int] NULL ,
    [tdbid] [int] NULL
    ) ON [PRIMARY]
    GO

  4. #4
    Join Date
    Dec 2003
    Posts
    11
    After examining my SP closer I have made the following changes. I think this is closer to the end result. The problem is that I am getting an error:

    The select list for the insert statement contains fewer items than the insert list. Not sure why this is.

    Code:
    ALTER PROCEDURE dbo.InsertTool
    (
    @ToolName		nvarchar(250),
    @Platform		nvarchar(250),
    @Vendor			nvarchar(250),
    @Subplatform		nvarchar(250),
    @Submitter		nvarchar(250),
    @Finders		nvarchar(250),
    @LTD			nvarchar(50),
    @JobArea		nvarchar(250),
    @Func			nvarchar(250),
    @Owners			nvarchar(250),
    @Active			nvarchar(250),
    @Version		numeric,
    @Build			numeric,
    @CWSTD			nvarchar(50),
    @Status			nvarchar(250),
    @Cost			numeric,
    @Notes			nvarchar(250),
    @Keywords		nvarchar(250),
    @Links			nvarchar(250),
    @Paths			nvarchar(250),
    @eid			int,
    @tdbid			int,
    @Creator		nvarchar(250)
    )
    AS
    
    INSERT INTO [ToolDB] (ToolName, Platform, Vendor, Subplatform, Submitter, Finders, LTD, JobArea, Func, Owners, Active, Version, Build, CWSTD, Status, Cost, Notes, Keywords, Links, Paths)
    VALUES
    (@ToolName, @Platform, @Vendor, @Subplatform, @Submitter, @Finders, @LTD, @JobArea, @Func, @Owners, @Active, @Version, @Build, @CWSTD, @Status, @Cost, @Notes, @Keywords, @Links, @Paths)
    INSERT INTO [ToolUsersdb] (eid, tdbid) select e.id from employee e where e.id in (@Creator) select t.id from Tooldb t
    Last edited by tmf; 10-04-04 at 16:41.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Huh?

    Code:
    INSERT INTO [ToolUsersdb] (eid, tdbid) 
    select e.id from employee e where e.id in (@Creator) select t.id from Tooldb t
    What this?

    You can't do that
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Dec 2003
    Posts
    11
    Brett,

    Could you expand on your thoughts?

    Do you have any ideas on how this can be done. I am sure that I am not the first person to be presented with a one to many realtionship between tables.

    Even a link would be helpful if you know of one.

    Thanks,

    Tim

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It looks like, and correct me if I'm wrong, and INSERT statement with 2 SELECTs....

    I guess you could do it this way....

    Code:
     INSERT INTO [ToolUsersdb] (eid, tdbid) 
          SELECT e.id, t.id 
    	FROM employee e JOIN Tooldb t 
    	  ON e.id = t.id 
           WHERE e.id = @Creator
    The IN error message is erroneous...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Dec 2003
    Posts
    11
    Brett,

    Thanks for helping out. You thoughts are appreciated.

    Here is the modification I have made (with your thoughts in mind):
    Code:
    INSERT INTO [ToolUsersdb] (eid, tdbid)
    SELECT e.id FROM employee e WHERE e.id = (@Creator)
    UNION 
    SELECT t.id FROM Tooldb t
    I put the UNION in place because my understanding is that without it, the second SLELECT statement would be ignored.

    With the above script I am getting the following error:
    The Select list for the insert statment contains fewer items than the Insert list.

    There are only two columns in the ToolUsersdb outside of the ID column.

    Any additional ideas?

    Have I explained what I am trying to do clearly enough in the beginning? Let me know if you need any further clarification.

    Thanks again.

    Tim

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Concepts!

    You're mixing rows with columns.

    For example, your INSERT expects 2 columns per row.

    Your result set is producing 1 column per row.

    If you want e.id and t.id on the same row, you have to relate them somehow.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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