July 7, 2010 at 9:41 am
I am curious, is there a t-sql statement, function, keyword etc, which mirrors the data pump transformation in DTS? Specifically, the loading records in batches, such as every 1000 or whatever number makes sense for the particular load.
I had been considering a loop of an insert statement which picks a range of primary keys. However, many of the larger inserts I need to work with have tables without a numeric key, or with many large gaps in the keys.
It is my understanding in SQL 2005 and up, there is a row number function which would work for this. However, I do not see such a function in the BOL for SQL 2000.
Thanks!
July 7, 2010 at 12:50 pm
Take a look at SET ROWCOUNT:
http://msdn.microsoft.com/en-us/library/aa259189(SQL.80).aspx
It's a deprecated feature, but a very useful one.
- Jeff
July 9, 2010 at 5:58 am
Given that the ROWCOUNT is deprecated I would suggest this solution
-- update 100 rows at a time
-- insure that you are counting rows
SET NOCOUNT OFF
while (1 = 1)
begin
UPDATE X set X.myValue = 'FOO'
FROM myTable X inner join
( SELECT TOP 100 myOrderField FROM myTable WHERE myValue <> 'FOO'
ORDER BY myOrderField
) Y ON X.myOrderField = Y.myOrderField
-- Test that 100 records were updated
-- If less than 100 were updated you've reached the end
-- of your update
if(SELECT @@rowcount) < 100
begin
BREAK
end
end
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply