locked
Insert multiple records at once RRS feed

  • Question

  •  

    Hi,

     

    I'm creating a web application and part of the application prompts a user to select multiple categories.

     

    e.g

     

    Category1

    Category2

    Category3

    Category4 etc

     

    A User picks categories 1 and 2 (FOR EXAMPLE) using the checkbox control in Visual Studio. When user presses Submit I'd like a stored procedure to be called passing in the categories selected. these categories will then be inserted into a composite table of Category and UserID.

     

    UserID      CategoryID

     

    User1           Cat1

    User1          Cat2

    User3          Cat1

    User2          Cat3

    User2          Cat2

    User2           Cat1

     

    Etc

     

    As the amount of categories selected will vary I'm not sure how I could do this, as the procedure will be expecting a set amount of paramaters. Any ideas?

     

    The checkboxes used are a Visual Studio Checkbox List control

     

    many thanks

    Tuesday, November 18, 2008 2:32 PM

Answers

  • Here's some pseudo code for the event

     

    Code Snippet

    for each checkbox in container

     

        if checkbox is selected then

            call insert stored procedure passing params:(user_id, checkbox.value)

            'where checkbox.value = category_id

        end if

     

    next checkbox

     

     

    insert sproc:

    Code Snippet

    CREATE PROC dbo.single_insert (

       @user_id int

     , @category_id int

    )

    AS

      BEGIN

        INSERT INTO dbo.my_table (user_id, category_id) VALUES (@user_id, @category_id)

      END

    GO

     

     

    Friday, November 21, 2008 11:14 AM
    Answerer

All replies

  • Any reason why you can't simply perform N many single inserts? One for each category.

     

    Tuesday, November 18, 2008 2:59 PM
    Answerer
  • Hi,

     

    In SQL Server 2008 you can pass a TABLE VARIABLE as a parameter, but if you're working on SQL 2000 or 2005, you need to use XML, or, as the post above are saying, why not insert one category at a time? Use a loop in your VS application to call the insert multiple times.

    Cheers,

    Raul Santos Neto

     

    Tuesday, November 18, 2008 3:25 PM
  • Hi,

    how about something like this:

     

    Assign:

    Cat1 ---> 2

    Cat2 ---> 4

    Cat3 ---> 8

    Cat4 ---> 16

    Cat5 ---> 32

    ...

     

    If the user selects Cat1, Cat3 and Cat4, you pass a single integer 2+8+16=26 to SQL Server Stored Procedure. There you can easily figure out what 26 means and translate that into INSERT statements, for example.

     

    The method is related to Bitwise operation in SQL Server. As a start, check this link:

    http://www.mssqltips.com/tip.asp?tip=1218

     

    Let me know if you still need help.

     

     

    Thanks,

    Varsham

     

    Wednesday, November 19, 2008 5:38 AM
  • If your using SQL Server 2000, you can do

    1. Prepare some xml that contains all categories and users

    <Cat id="catid1">

    <user>1</user>

    <user>2</user>

    </Cat>

    <Cat id="catid2">

    <user>10</user>

    <user>20</user>

    </Cat>

    2. Extract the prepared xml in the form of string and pass it to stored procedure

    3. In the sp, use OPENXML and insert all these fetched rows into the proper table at a time.

     

    If your using SQL Server 2005 or above, you can avail XML datatype. For information on this please visit http://msdn.microsoft.com/en-us/library/ms345117.aspx.

    Wednesday, November 19, 2008 6:20 AM
  • Thanks for your replies.

    I'm using SQL Server 2000 and at the moment I don't want to go down the XML route.

     

    GVEE can you go into more detail regarding the technique you suggest?

     

    many thanks

     

    Friday, November 21, 2008 10:17 AM
  • Here's some pseudo code for the event

     

    Code Snippet

    for each checkbox in container

     

        if checkbox is selected then

            call insert stored procedure passing params:(user_id, checkbox.value)

            'where checkbox.value = category_id

        end if

     

    next checkbox

     

     

    insert sproc:

    Code Snippet

    CREATE PROC dbo.single_insert (

       @user_id int

     , @category_id int

    )

    AS

      BEGIN

        INSERT INTO dbo.my_table (user_id, category_id) VALUES (@user_id, @category_id)

      END

    GO

     

     

    Friday, November 21, 2008 11:14 AM
    Answerer
  •  gvee wrote:

    Here's some pseudo code for the event

     

    Code Snippet

    for each checkbox in container

     

        if checkbox is selected then

            call insert stored procedure passing params:(user_id, checkbox.value)

            'where checkbox.value = category_id

        end if

     

    next checkbox

     

     

    insert sproc:

    Code Snippet

    CREATE PROC dbo.single_insert (

       @user_id int

     , @category_id int

    )

    AS

      BEGIN

        INSERT INTO dbo.my_table (user_id, category_id) VALUES (@user_id, @category_id)

      END

    GO

     

     

     

    I understand now, thanks for your help!

    Friday, November 21, 2008 3:31 PM