May 18, 2021 at 8:12 pm
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;
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
May 18, 2021 at 8:20 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
May 18, 2021 at 8:28 pm
Sorry - code has been added!
May 19, 2021 at 3:21 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
May 19, 2021 at 3:25 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply