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:
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
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 #tempsamples;
create table #tempsamples
-- There are many other fields to be copied – this is just a short list for the purposes of the example
DECLARE @i int = 1;
DECLARE @nrows int = (select count(*) from #temptable);
DECLARE @barcode_id varchar(80);
WHILE (@i <= @nrows)
SET @barcode_id = (SELECT barcodeid FROM #temptable WHERE rn = @i);
DECLARE @j-2 int = 1;
DECLARE @k int = (SELECT naliquots FROM #temptable WHERE rn = @i);
WHILE (@j <= @k)
INSERT INTO #tempsamples (
WHERE Internal_Bar_Code_ID = @barcode_id;
SET @j-2 = @j-2 + 1;
SET @i = @i + 1;
-- Copy new samples from #tempsamples table back into main table Data_Table
INSERT INTO Data_Table
SET @i = @i + 1;
delete from #tempsamples; -- Clear out and start again