January 8, 2014 at 12:50 pm
hi all
I am trying to insert from tableA to tableB
there are 1000 records in tableA and I want to insert 100 records at a time into tableB
idea is to insert 100 records at a time and run the query 10 times and insert 1000 records into tableB
thanks in advance
January 8, 2014 at 1:21 pm
Why would you do that? How can you identify which rows you have inserted and which rows you haven't?
January 8, 2014 at 1:23 pm
I know that a weird requirement and I want to use primary key for that
January 8, 2014 at 1:35 pm
harita (1/8/2014)
hi allI am trying to insert from tableA to tableB
there are 1000 records in tableA and I want to insert 100 records at a time into tableB
idea is to insert 100 records at a time and run the query 10 times and insert 1000 records into tableB
thanks in advance
Use NTILE(10) OVER (ORDER BY ID) to get 10 groups of 100, and aggregate the result picking up the min and max id of each tile. Spool the result into a temp table and scan through it with a WHILE loop, or use the result as the table source for a firehose cursor.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 8, 2014 at 2:38 pm
Here's an example:
select *
INTO #TableB
from sys.all_objects
WHERE 1=2
select top 1000 *
INTO #TableA
from sys.all_objects
WHILE @@ROWCOUNT > 0
BEGIN
INSERT INTO #TableB
SELECT TOP 100 *
FROM #TableA a
WHERE NOT EXISTS(SELECT * FROM #TableB b WHERE b.object_id = a.object_id)
END
SELECT * FROM #TableA
SELECT * FROM #TableB
DROP TABLE #TableA
DROP TABLE #TableB
GO
I would never do something like this, but if you really need it. A single insert will perform much better than this cycle which needs validation or the NTILE that Chris suggested.
Thinking outside the box, you could use a data flow task in SSIS and limit number of rows per batch to 100.
January 8, 2014 at 5:43 pm
Luis - This is nice! Also out of the box.
select [object_id]
INTO #TableB
from sys.all_objects
WHERE 1=2
Another way that I would certainly never try at home myself.
select [object_id]
INTO #TableB
from sys.all_objects
WHERE 1=2
select top 1000 [object_id]
INTO #TableA
from sys.all_objects
DECLARE @ROWCOUNT INT = 1;
WHILE @ROWCOUNT < 1000
BEGIN
WITH TableA AS
(
SELECT *, rn=ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM #TableA
)
INSERT INTO #TableB
SELECT [object_id]
FROM TableA a
WHERE rn BETWEEN @ROWCOUNT AND @ROWCOUNT + 99;
SELECT @ROWCOUNT = @ROWCOUNT + @@ROWCOUNT;
END
SELECT * FROM #TableA
SELECT * FROM #TableB
GO
DROP TABLE #TableA
DROP TABLE #TableB
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 9, 2014 at 8:23 am
dwain.c (1/8/2014)
Luis - This is nice! Also out of the box.
select [object_id]
INTO #TableB
from sys.all_objects
WHERE 1=2
That was very common at my first job to avoid writing the definition of temp tables. 🙂
Laziness creates nice solutions.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply