Thread: insert thousands of records

Hi
Sorry for the confusing title

I have this table structure

tbluser
-user_id.....

tblmessages
-message_id
-receiver_id
-message....

Now i have to send mass messages to these users
So first question is if i have say 30K users, can i insert so many records in 1 go or will face a problem??

Second question is query related.
Can i get this done in 1 query, i mean something like this

Code:

insert into tblmessages values (1, (select User_Id From idate_tblusermaster order by User_Id), 'test message')

Although this query returns error

Subquery returns more than 1 row

OR will i have to first get the user id, then insert using it??

VB.NET 2005
MySql 5.0

Thanx

Regarding the second part of your post, the following query should work:

Code:

insert into tblmessages 
 Select 1, User_ID, 'test message'
   From idate_tblusermaster

The ORDER BY is not necessary order is established by the index on the table or when the data is retrieved.

IMO, you should do this in batches. I believe MySQL's LIMIT could help you with this.

Originally Posted by Wolffy

Regarding the second part of your post, the following query should work:

Code:

insert into tblmessages 
 Select 1, User_ID, 'test message'
   From idate_tblusermaster

The ORDER BY is not necessary order is established by the index on the table or when the data is retrieved.

Thanx Wolffy, it works

Originally Posted by jmurrayhead

IMO, you should do this in batches. I believe MySQL's LIMIT could help you with this.

ya, i was also thinking that doing all at one go might create problem.

I'll see how i get it done in batches.......... thanx J

Originally Posted by jmurrayhead

IMO, you should do this in batches. I believe MySQL's LIMIT could help you with this.

Well.......... i cant think of a good way to do it J

Can you suggest how to do it??

Originally Posted by micky

Well.......... i cant think of a good way to do it J

Can you suggest how to do it??

Ok, i managed to do it....... inserting 1000 records at a time.

Share the solution, M

Originally Posted by jmurrayhead

Share the solution, M

yes J, i was out of time yesterday..... so didnt post my code.

I added 2 things in .aspx part, first a text box to save my limit count for records to be shown

Code:

<asp:TextBox Visible="false" runat="server" ID="hdPageCount">

Then i had 2 submit buttons, one for 1st 1000 records, and second for next 1000 records.

Code:

<asp:button id="btnSubmit" runat="server" Width="125px" Text="First 1000 >>" TabIndex="2"></asp:button>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<asp:button id="btnNext" runat="server" Width="125px" Text="Next 1000 >>" TabIndex="3" Enabled="false"></asp:button>

Now i have a textbox to enter message to be saved in db. I needed this hidden textbox as i wasnt redirecting to this page after each insertion as then i would loose this text and i dont want the text in querystring.

Next is .vb part

Code:

Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
    If Page.IsValid Then
        SendMessage(Trim(txtMessage.Text), 0)
    End If
End Sub

Protected Sub btnNext_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnNext.Click
    If Page.IsValid Then
        SendMessage(Trim(txtMessage.Text), hdPageCount.Text)
    End If
End Sub

    Function SendMessage(ByVal message As String, ByVal limit As Integer)
        Dim nPage As Integer = 1000
        Dim strSQL As String = "Insert Into tblmessagemaster (Receiver_Id, Message, Msg_DateTime) " & _
            "Select User_Id, @Message, now() From tblusermaster Limit @Limit, @Page"

        Using Con As New MySqlConnection(sConn)
            'GET TOTAL USERS
            Dim cmdTotal As New MySqlCommand("Select Count(User_Id) From tblusermaster", Con)

            Con.Open()
            Dim nTotal As Integer = cmdTotal.ExecuteScalar

            If nTotal > limit Then
                Dim cmd As New MySqlCommand(strSQL, Con)
                cmd.Parameters.AddWithValue("@Message", message)
                cmd.Parameters.AddWithValue("@Limit", limit)
                cmd.Parameters.AddWithValue("@Page", nPage)

                cmd.ExecuteNonQuery()
            End If
            Con.Close()

            If limit = 0 Then
                'FIRST 1000
                If nTotal - nPage > 0 Then
                    'MORE RECORDS
                    hdPageCount.Text = nPage
                    btnNext.Enabled = True
                    btnSubmit.Enabled = False
                    lblError.Text = " Inserted " & nPage & " records out of " & nTotal & " records."

                Else
                    'NO MORE RECORDS
                    Response.Redirect("Message.aspx")
                End If

            Else
                'NEXT 1000
                Dim nNext As Integer = nPage + limit

                If nTotal - nNext > 0 Then
                    'MORE RECORDS
                    hdPageCount.Text = nNext
                    btnNext.Enabled = True
                    btnSubmit.Enabled = False
                    lblError.Text = " Inserted " & nNext & " records out of " & nTotal & " records."

                Else
                    'NO MORE RECORDS
                    Response.Redirect("Message.aspx")
                End If
            End If
        End Using
    End Function