July 21, 2010 at 8:23 am
Please help me to write a query. This is the task. I have Table a and B. Table B has millions of rows.
I want to insert into Table A by selecting from Table 10,000 records at a time until all the millions rows are inserted into Table A using a while loop.
July 21, 2010 at 8:26 am
Edit: Nvm wrong advice.
Cheers,
J-F
July 21, 2010 at 8:29 am
Anthonykush (7/21/2010)
Please help me to write a query. This is the task. I have Table a and B. Table B has millions of rows.I want to insert into Table A by selecting from Table 10,000 records at a time until all the millions rows are inserted into Table A using a while loop.
while exists (select PK from TableB EXCEPT select PK from TableA)
insert into TableA <column_list>
select top (10000) <column list>
from TableB b
LEFT JOIN TableA a
ON a.PK = b.PK
where a.PK IS NULL
Obviously, you need to fill in a lot of variables here, but this give you the general idea.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 21, 2010 at 8:54 am
Ok I assume that your source table has no "anchor" column which can be used to count your inserting progress...
So, here an example.
Lets crerate such a table, you will see I've concatinated 'K' to the number to make non identity kind of PK:
SELECT TOP 1000000 'K' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as VARCHAR(10)) PKCol,
c1.* INTO TableA
FROM sys.COLUMNS c1 CROSS JOIN sys.COLUMNS c2
Then prepare an empty copy of table:
SELECT * INTO TableB
FROM TableA WHERE 1 = 0
Now, in order to insert 10000 rows at the time we need to some how track the progress, see how I introduce "anchor" column on the fly by using ROW_NUMBER:
DECLARE @rn INT
DECLARE @ac INT
SET @rn = 0
WHILE @rn = 0 OR @ac > 0
BEGIN
INSERT INTO TableB
SELECT TOP 10000 ta.*
FROM TableA ta
JOIN (SELECT ROW_NUMBER() OVER (ORDER BY PKCol) AS rn, PKCol FROM TableA) ota
ON ota.PKCol = ta.PKCol
WHERE rn > @rn
SELECT @ac = @@ROWCOUNT, @rn = @rn + 10000
END
If your PK is a composite, remember to includwe all parts of it in OVER(ORDER BY) and JOIN.
The above query does not pretend to be very fast, however inserting 10000 records at time helps to avoid having "transaction log full" problem 😀
July 21, 2010 at 9:53 am
Thank you for this. I will try
July 21, 2010 at 1:26 pm
One more help please.
I am getting invalid column name PKCol after Table 1 and Table 2 with the actual table names.
What is PKCol? is this the primary key for my table?
July 21, 2010 at 6:04 pm
TonyK_Ga (7/21/2010)
One more help please.I am getting invalid column name PKCol after Table 1 and Table 2 with the actual table names.
What is PKCol? is this the primary key for my table?
You didn't provide any information so he made up names. It is meant to convey that you're joining against the primary key to guarantee that you're not duplicating your inserts.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply