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

Question

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

Answer 1

Hi @Selene Platt ，

Thanks for your update.

You could put SET NOCOUNT ON at the top of the script or at the start of one procedure/trigger.

GO is not a SQL keyword. It is a client-side batch separator used by SQL Server Management Studio and other client tools.

GO has no effect on transaction scope. BEGIN TRAN will start a transaction on the current connection. COMMIT and ROLLBACK will end the transaction. You can execute as many statements as you want in-between. GO will execute the statements separately.

You could include multiple GO sections within it and they will all roll back as a unit.

BEGIN TRANSACTION TransactionName;  
GO  
  
-- do stuff  
GO  
  
COMMIT TRANSACTION TransactionName;  
GO  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I tried adding 'GO' as you describe above, but keep getting a syntax error wherever I add 'GO' - within the transaction or outside of it. I have something like this:

BEGIN TRANSACTION
INSERT INTO table
(list of columns)
SELECT
(list of columns)
FROM #temptable;
GO
COMMIT TRANSACTION

I've also put GO after the COMMIT statement. Either way, I get the error "Incorrect syntax near 'GO'"

Answer 3

Try this:

-- Create temporary table to hold list of records that need to be replicated. Loop over this table to replicate all records.
DROP TABLE IF EXISTS #temptable;

CREATE TABLE #temptable (
    rn int,
    barcodeid varchar(80),
    naliquots int
);

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;

DROP TABLE IF EXISTS #tempsamples;

CREATE TABLE #tempsamples (
    Sample_ID nvarchar(255),
    Internal_Bar_Code_ID float,
    Replication_Count float,
);

DECLARE @i int = 1;
DECLARE @nrows int = (SELECT COUNT(*) FROM #temptable);
DECLARE @barcode_id varchar(80);

WHILE (@i <= @nrows)
BEGIN
    DECLARE @k int;
    DECLARE @j int = 1;
    SELECT @barcode_id = barcodeid, @k = 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 = @j + 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

DROP TABLE IF EXISTS #tempsamples;
DROP TABLE IF EXISTS #temptable;
GO

Answer 4

Hi @Selene Platt ，

Thanks for your update.

It could be better for you to post the complete lastest code together with DDL and some sample data. Then we could look further and try to find out the cause.

I tried with below code and I did not face the "Incorrect syntax near 'GO'" error.

BEGIN TRANSACTION  
GO  
INSERT INTO table1  
SELECT * from table1  
GO  
COMMIT TRANSACTION  
GO  

It would happen if your batch separator has been changed in your settings. Please go to SSMS click on Tools --> Options and go to Query Execution/SQL Server/General to check that batch separator.

Please check whether 'Go' is there.

In addition, you could not have the semicolon and the GO on the same line.

Please avoid the query like below:

SELECT 'This Doesn''t Work'  
;GO  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Update: I had the latest version of SSMS installed and ran my script...it got MUCH farther than the old version! I finish the data set with only 2 executions rather than 4. Not clean, but it will get the job done, and I only have to do it once in the production system - it won't have to be re-executed once the data set is replicated.

I do have the batch separator = 'GO' in the settings.