How to execute multiple 'INSERT INTO' statements without crashing SSMS?

  • I need to execute multiple 'INSERT INTO' statements to replicate records in a table but SSMS keeps crashing, somewhere after the 800+ statement mark. I'm creating a temporary table in my SQL code, inserting the new records into that table, and ultimately inserting the records of that table into the original table.

    I've been playing with 'GO' and 'COMMIT' with no luck. I do wrap the INSERT statements with 'BEGIN/END [TRANSACTION]'.

    I am NOT a DB analyst nor an expert in SQL, and I have not worked with the INSERT statement much, so this is new territory for me. My only other option so far is to divide up my table data into manageable chunks based on some arbitrary criteria, but this would be tedious and inefficient. The original # records to be replicated is about 16K, and based on a field value in each record, I need to replicate that record [value] # of times in the table (from 1 to 10 times generally).

    Is there a way to batch the INSERTs so as not to crash SSMS? I will need explicit code examples or directions rather than a general reference to a topic. Not even sure if I'm going about it the right way - maybe there's an easier way to do this? Thanks in advance for any help!

    In terms of the DDL, there are no keys or indexes. There are a number of columns, but for the purposes of this post, there are three:

    Sample_ID nvarchar(255),

    Internal_Bar_Code_ID float,

    Replication_Count float

    Do you need anything else in this regard?

    Here's the code (pared down for the purposes of this example, and pasted as plain text so unfortunately the indentation is gone):

    --

    -- Create temporary table to hold list of records that need to be replicated. Loop over this table to replicate all records.

    --

    drop table #temptable;

    create table #temptable

    (

    rn int,

    barcodeid varchar(80),

    naliquots int

    );

    GO

    insert into #temptable (rn, barcodeid, naliquots)

    select distinct row_number() over(order by Internal_Bar_Code_ID) as rn, Internal_Bar_Code_ID, Replication_Count - 1

    from Data_Table where Replication_Count > 1;

    GO

    drop table #tempsamples;

    create table #tempsamples

    (

    -- There are many other fields to be copied – this is just a short list for the purposes of the example

    Sample_ID nvarchar(255),

    Internal_Bar_Code_ID float,

    Replication_Count float,

    GO

    DECLARE @i int = 1;

    DECLARE @nrows int = (select count(*) from #temptable);

    DECLARE @barcode_id varchar(80);

    WHILE (@i <= @nrows)

    BEGIN

    SET @barcode_id = (SELECT barcodeid FROM #temptable WHERE rn = @i);

    SELECT @barcode_id;

    DECLARE @j-2 int = 1;

    DECLARE @k int = (SELECT naliquots FROM #temptable WHERE rn = @i);

    WHILE (@j <= @k)

    BEGIN

    INSERT INTO #tempsamples (

    Sample_ID,

    Internal_Bar_Code_ID,

    Replication_Count)

    SELECT

    Sample_ID,

    Internal_Bar_Code_ID,

    Replication_Count

    FROM Data_Table

    WHERE Internal_Bar_Code_ID = @barcode_id;

    SET @j-2 = @j-2 + 1;

    END;

    SET @i = @i + 1;

    END

    --

    -- Copy new samples from #tempsamples table back into main table Data_Table

    --

    BEGIN TRANSACTION

    INSERT INTO Data_Table

    (Sample_ID,

    Internal_Bar_Code_ID,

    Replication_Count)

    SELECT

    Sample_ID,

    Internal_Bar_Code_ID,

    Replication_Count

    FROM #tempsamples;

    COMMIT TRANSACTION

    SET @i = @i + 1;

    delete from #tempsamples; -- Clear out and start again

    END

  • Hmm, you need explicit code -- great, that's what we're here for -- but you didn't give us:

    table DDL (this is what we really need, not just a table name a list of column names, but the table definition)

    -- which means we can't really help you yet: since we have no idea what names to use in the code, we can't write explicit code yet.  😀

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Sorry - code has been added!

    • This reply was modified 1 month ago by  seleneplatt.
  • There's a technique callled "Tally table" that is perfect for doing that.  A tally table is simply a table with a single column containing 1, 2, 3, ....  The beauty of it is that you can JOIN to it to replicate rows.  First I'll post the code for your original issue then quick demo code to show a tally table in action.

    ;WITH
    /* create a table of 10 rows, each containing 0 */
    cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
    ),
    /* CROSS join 10 rows to 10 rows to get 100 rows */
    cte_tally100 AS (
    SELECT 0 AS number FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2
    ),
    /* CROSS join 100 rows to 100 rows to get 10K rows, and make the value an actual row count value rather than just 0 */
    cte_tally10K AS (
    SELECT 0 AS number UNION ALL
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number FROM cte_tally100 c1 CROSS JOIN cte_tally100 c2
    )
    INSERT INTO dbo.Data_Table ( ...column_list... )
    SELECT ...column_list...
    FROM dbo.Data_Table DT
    /* Use the tally table to cause duplicates of the original table row to be created by SQL */
    INNER JOIN cte_tally10K ct ON ct.number BETWEEN 1 AND (DT.Replication_Count - 1)
    WHERE DT.Replication_Count > 1

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • CREATE TABLE #data (
    col1 varchar(10) NOT NULL,
    replication_count int NOT NULL
    )
    INSERT INTO #data VALUES('abc', 5),('defgh', 3),('xx', 1)

    ;WITH
    cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
    ),
    cte_tally100 AS (
    SELECT 0 AS number FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2
    ),
    cte_tally10K AS (
    SELECT 0 AS number UNION ALL
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number FROM cte_tally100 c1 CROSS JOIN cte_tally100 c2
    )
    SELECT *
    FROM #data d
    INNER JOIN cte_tally10K ct ON ct.number BETWEEN 1 AND d.replication_count - 1
    ORDER BY col1, ct.number

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply