none
insert more row in sql server database RRS feed

  • Question

  • I want to add 20000 thousand rows at once .

    to monitor the efficiency of the database and the efficiency of the server

    I used the code below but did not add the data at once

    The AutoNumber key field

      Try
    
    
                Dim cmd As New SqlCommand("insert into Table_TB(NAME_TB,NOTE_TB) values(@NAME_TB,@NOTE_TB)", conns)
                For i = 0 To 20000
                    Cursor.Current = Cursors.WaitCursor
                    cmd.Parameters.AddWithValue("@NAME_TB", ("maher"))
                    cmd.Parameters.AddWithValue("@NOTE_TB", ("good"))
                    conns.Open()
                    cmd.ExecuteNonQuery()
                    conns.Close()
                Next
                ConnectSERVER()
                Me.Refresh()
                MsgBox("Success")
                Cursor.Current = Cursors.Default
            Catch ex As Exception
                MsgBox("fail")
            End Try


    • Edited by monemas Sunday, July 8, 2018 12:37 PM
    Sunday, July 8, 2018 12:37 PM

Answers

  • I would not be concerned with parameters for this, simply run code as follows.

    Public Sub ForumQuestionInserts()
        Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
            Using cmd As New SqlCommand With {.Connection = cn}
                cmd.CommandText =
                <SQL>
                    INSERT INTO dbo.Table_TB (NAME_TB,NOTE_TB) VALUES ('Karen','Payne');
                    SELECT CAST(scope_identity() AS int);
                </SQL>.Value
    
                cn.Open()
                For i = 0 To 20000
                    Console.WriteLine(CInt(cmd.ExecuteScalar))
                Next
            End Using
        End Using
    End Sub

    I ran this several times, at over 100,000 records the SELECT result.

    Table structure (with primary key)

    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[Table_TB](
    	[id] [INT] IDENTITY(1,1) NOT NULL,
    	[NAME_TB] [NCHAR](10) NULL,
    	[NOTE_TB] [NCHAR](10) NULL,
     CONSTRAINT [PK_Table_TB] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
     ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO

    EDIT

    I realize you are looking a performance yet there are other ways e.g. using performance tools in SSMS (SQL-Server Management Studio) for this.

    On a side note, to properly do bulk inserts is to use SqlBulkCopy and if monitoring is needed set it up with the following event.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Sunday, July 8, 2018 1:05 PM
    Moderator
  •  When Performance is the key  then use Table valued parameters to insert bulk data.

    /* Create a table type. */  
    CREATE TYPE LocationTableType AS TABLE   
    ( LocationName VARCHAR(50)  
    , CostRate INT );  
    GO  

    /* Create a procedure to receive data for the table-valued parameter. */  
    CREATE PROCEDURE dbo. usp_InsertProductionLocation  
        @TVP LocationTableType READONLY  
        AS   
        SET NOCOUNT ON  
        INSERT INTO AdventureWorks2012.Production.Location  
               (Name  
               ,CostRate  
               ,Availability  
               ,ModifiedDate)  
            SELECT *, 0, GETDATE()  
            FROM  @TVP;  
            GO  

    /* Declare a variable that references the type. */  
    DECLARE @LocationTVP AS LocationTableType;  

    /* Add data to the table variable. */  
    INSERT INTO @LocationTVP (LocationName, CostRate)  
        SELECT Name, 0.00  
        FROM AdventureWorks2012.Person.StateProvince;  

    /* Pass the table variable data to a stored procedure. */  
    EXEC usp_InsertProductionLocation @LocationTVP;  
    GO  


    /* Create a table type. */ CREATE TYPE LocationTableType AS TABLE ( LocationName VARCHAR(50) , CostRate INT ); GO /* Create a procedure to receive data for the table-valued parameter. */ CREATE PROCEDURE dbo. usp_InsertProductionLocation @TVP LocationTableType READONLY AS SET NOCOUNT ON INSERT INTO AdventureWorks2012.Production.Location (Name ,CostRate ,Availability ,ModifiedDate) SELECT *, 0, GETDATE() FROM @TVP; GO /* Declare a variable that references the type. */ DECLARE @LocationTVP AS LocationTableType; /* Add data to the table variable. */ INSERT INTO @LocationTVP (LocationName, CostRate) SELECT Name, 0.00 FROM AdventureWorks2012.Person.StateProvince; /* Pass the table variable data to a stored procedure. */ EXEC usp_InsertProductionLocation @LocationTVP; GO
    /* Create a table type. */ CREATE TYPE LocationTableType AS TABLE ( LocationName VARCHAR(50) , CostRate INT ); GO /* Create a procedure to receive data for the table-valued parameter. */ CREATE PROCEDURE dbo. usp_InsertProductionLocation @TVP LocationTableType READONLY AS SET NOCOUNT ON INSERT INTO AdventureWorks2012.Production.Location (Name ,CostRate ,Availability ,ModifiedDate) SELECT *, 0, GETDATE() FROM @TVP; GO /* Declare a variable that references the type. */ DECLARE @LocationTVP AS LocationTableType; /* Add data to the table variable. */ INSERT INTO @LocationTVP (LocationName, CostRate) SELECT Name, 0.00 FROM AdventureWorks2012.Person.StateProvince; /* Pass the table variable data to a stored procedure. */ EXEC usp_InsertProductionLocation @LocationTVP; GO
    /* Create a table type. */ CREATE TYPE LocationTableType AS TABLE ( LocationName VARCHAR(50) , CostRate INT ); GO /* Create a procedure to receive data for the table-valued parameter. */ CREATE PROCEDURE dbo. usp_InsertProductionLocation @TVP LocationTableType READONLY AS SET NOCOUNT ON INSERT INTO AdventureWorks2012.Production.Location (Name ,CostRate ,Availability ,ModifiedDate) SELECT *, 0, GETDATE() FROM @TVP; GO /* Declare a variable that references the type. */ DECLARE @LocationTVP AS LocationTableType; /* Add data to the table variable. */ INSERT INTO @LocationTVP (LocationName, CostRate) SELECT Name, 0.00 FROM AdventureWorks2012.Person.StateProvince; /* Pass the table variable data to a stored procedure. */ EXEC usp_InsertProductionLocation @LocationTVP; GO
    • Marked as answer by monemas Monday, July 9, 2018 10:23 PM
    Monday, July 9, 2018 8:52 AM

All replies

  • I would not be concerned with parameters for this, simply run code as follows.

    Public Sub ForumQuestionInserts()
        Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
            Using cmd As New SqlCommand With {.Connection = cn}
                cmd.CommandText =
                <SQL>
                    INSERT INTO dbo.Table_TB (NAME_TB,NOTE_TB) VALUES ('Karen','Payne');
                    SELECT CAST(scope_identity() AS int);
                </SQL>.Value
    
                cn.Open()
                For i = 0 To 20000
                    Console.WriteLine(CInt(cmd.ExecuteScalar))
                Next
            End Using
        End Using
    End Sub

    I ran this several times, at over 100,000 records the SELECT result.

    Table structure (with primary key)

    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[Table_TB](
    	[id] [INT] IDENTITY(1,1) NOT NULL,
    	[NAME_TB] [NCHAR](10) NULL,
    	[NOTE_TB] [NCHAR](10) NULL,
     CONSTRAINT [PK_Table_TB] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
     ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO

    EDIT

    I realize you are looking a performance yet there are other ways e.g. using performance tools in SSMS (SQL-Server Management Studio) for this.

    On a side note, to properly do bulk inserts is to use SqlBulkCopy and if monitoring is needed set it up with the following event.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Sunday, July 8, 2018 1:05 PM
    Moderator
  • Hi monemas

    We can use BULK INSERT to enter the massive data addition.

    'Set a database connection string
     Dim connectStr As String = "……"
        Dim conn As SqlConnection = New SqlConnection(connectStr)
        Dim command As SqlCommand = New SqlCommand()
        command.CommandTimeout = 0
        command.Connection = conn
       ' Description:
       '* "c:\\sql.txt" is a pre-generated file containing 100 data
       '* The information of each field is divided by ","
       '* Each piece of data is separated by the "|" symbol
       '* One transaction per 100,000 pieces of data.
        command.CommandText = "BULK INSERT TableB FROM 'c:\sql.txt' WITH (FIELDTERMINATOR =',',ROWTERMINATOR ='|',BATCHSIZE = 100000)"
        conn.Open()
        command.ExecuteNonQuery()
        conn.Close()

    Inserting millions of data is only a matter of seconds.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click &amp;amp;amp;quot;Mark as Answer&amp;amp;amp;quot; the responses that resolved your issue, and to click &amp;amp;amp;quot;Unmark as Answer&amp;amp;amp;quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 9, 2018 5:03 AM
  •  When Performance is the key  then use Table valued parameters to insert bulk data.

    /* Create a table type. */  
    CREATE TYPE LocationTableType AS TABLE   
    ( LocationName VARCHAR(50)  
    , CostRate INT );  
    GO  

    /* Create a procedure to receive data for the table-valued parameter. */  
    CREATE PROCEDURE dbo. usp_InsertProductionLocation  
        @TVP LocationTableType READONLY  
        AS   
        SET NOCOUNT ON  
        INSERT INTO AdventureWorks2012.Production.Location  
               (Name  
               ,CostRate  
               ,Availability  
               ,ModifiedDate)  
            SELECT *, 0, GETDATE()  
            FROM  @TVP;  
            GO  

    /* Declare a variable that references the type. */  
    DECLARE @LocationTVP AS LocationTableType;  

    /* Add data to the table variable. */  
    INSERT INTO @LocationTVP (LocationName, CostRate)  
        SELECT Name, 0.00  
        FROM AdventureWorks2012.Person.StateProvince;  

    /* Pass the table variable data to a stored procedure. */  
    EXEC usp_InsertProductionLocation @LocationTVP;  
    GO  


    /* Create a table type. */ CREATE TYPE LocationTableType AS TABLE ( LocationName VARCHAR(50) , CostRate INT ); GO /* Create a procedure to receive data for the table-valued parameter. */ CREATE PROCEDURE dbo. usp_InsertProductionLocation @TVP LocationTableType READONLY AS SET NOCOUNT ON INSERT INTO AdventureWorks2012.Production.Location (Name ,CostRate ,Availability ,ModifiedDate) SELECT *, 0, GETDATE() FROM @TVP; GO /* Declare a variable that references the type. */ DECLARE @LocationTVP AS LocationTableType; /* Add data to the table variable. */ INSERT INTO @LocationTVP (LocationName, CostRate) SELECT Name, 0.00 FROM AdventureWorks2012.Person.StateProvince; /* Pass the table variable data to a stored procedure. */ EXEC usp_InsertProductionLocation @LocationTVP; GO
    /* Create a table type. */ CREATE TYPE LocationTableType AS TABLE ( LocationName VARCHAR(50) , CostRate INT ); GO /* Create a procedure to receive data for the table-valued parameter. */ CREATE PROCEDURE dbo. usp_InsertProductionLocation @TVP LocationTableType READONLY AS SET NOCOUNT ON INSERT INTO AdventureWorks2012.Production.Location (Name ,CostRate ,Availability ,ModifiedDate) SELECT *, 0, GETDATE() FROM @TVP; GO /* Declare a variable that references the type. */ DECLARE @LocationTVP AS LocationTableType; /* Add data to the table variable. */ INSERT INTO @LocationTVP (LocationName, CostRate) SELECT Name, 0.00 FROM AdventureWorks2012.Person.StateProvince; /* Pass the table variable data to a stored procedure. */ EXEC usp_InsertProductionLocation @LocationTVP; GO
    /* Create a table type. */ CREATE TYPE LocationTableType AS TABLE ( LocationName VARCHAR(50) , CostRate INT ); GO /* Create a procedure to receive data for the table-valued parameter. */ CREATE PROCEDURE dbo. usp_InsertProductionLocation @TVP LocationTableType READONLY AS SET NOCOUNT ON INSERT INTO AdventureWorks2012.Production.Location (Name ,CostRate ,Availability ,ModifiedDate) SELECT *, 0, GETDATE() FROM @TVP; GO /* Declare a variable that references the type. */ DECLARE @LocationTVP AS LocationTableType; /* Add data to the table variable. */ INSERT INTO @LocationTVP (LocationName, CostRate) SELECT Name, 0.00 FROM AdventureWorks2012.Person.StateProvince; /* Pass the table variable data to a stored procedure. */ EXEC usp_InsertProductionLocation @LocationTVP; GO
    • Marked as answer by monemas Monday, July 9, 2018 10:23 PM
    Monday, July 9, 2018 8:52 AM