|
|
You're in the
sql server new users
group:Inserting large amount of rows?
sql server new users:
Environment: SQL 2000 Standard SP4
Database recovery mode: Simple I have a stored proc that once a day transfers a large amount of data (2+ million rows usually) from one table to another as follows: INSERT INTO table1 SELECT * FROM table2 DROP table2 This doesn't scale well obviously, and quite often the above operation results in "could not obtain lock" error. Could someone suggest, please, some less brutal way of copying rows, like in batches, so example? None of the tables have primary keys or identity columns, by the way.
[quoted text, click to view]
On Apr 9, 1:01 pm, Usenet User < @ . > wrote: > Environment: SQL 2000 Standard SP4 > Database recovery mode: Simple > > I have a stored proc that once a day transfers a large amount of data > (2+ million rows usually) from one table to another as follows: > > INSERT INTO table1 > SELECT * FROM table2 > > DROP table2 > > This doesn't scale well obviously, and quite often the above operation > results in "could not obtain lock" error. > > Could someone suggest, please, some less brutal way of copying rows, > like in batches, so example? None of the tables have primary keys or > identity columns, by the way. > > TIA! What about BCP OUT and then BCP IN or BULK INSERT (this is what is called a minimally logged operation) [quoted text, click to view] >>None of the tables have primary keys or Can't wait to hear the Celko answer on this one ;-)> identity columns, by the way. How are you going to do a batch if you don't have a unique identifier? Denis the SQL Menace http://sqlservercode.blogspot.com/
On Apr 9, 1:42 pm, "Aaron Bertrand [SQL Server MVP]"
[quoted text, click to view] <ten....@dnartreb.noraa> wrote: > >>>None of the tables have primary keys or > >> identity columns, by the way. > > Can't wait to hear the Celko answer on this one ;-) > > He would say, "then you don't have any tables!" I think he throws in "by definition" somewhere in that sentence ;-) Denis the SQL Menace http://sqlservercode.blogspot.com/
[quoted text, click to view]
"SQL Menace" <denis.gobo@gmail.com> wrote in message news:1176139966.289857.258950@p77g2000hsh.googlegroups.com... > Can't wait to hear the Celko answer on this one ;-) You might be better off waiting for Godow. :)
[quoted text, click to view]
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:uGUKd8teHHA.1960@TK2MSFTNGP04.phx.gbl... >> You might be better off waiting for Godow. > > Did you mean Godot? Good catch, you caught me thinking of shoes:( :) [quoted text, click to view] >He would say, "then you don't have any tables!" Does that make you a 'bag' boy?
try to select from both tables into a third one.
select * into Table2 from (select * from table1 UNION select * from Table2) Drop table1 Drop table2 sp_rename 'table3', 'table1', object see if it makes difference. [quoted text, click to view] "Usenet User" < @ . > wrote in message news:kask13pagumd4ts3f5ic93f4rp48tvu102@4ax.com... > Environment: SQL 2000 Standard SP4 > Database recovery mode: Simple > > I have a stored proc that once a day transfers a large amount of data > (2+ million rows usually) from one table to another as follows: > > INSERT INTO table1 > SELECT * FROM table2 > > DROP table2 > > This doesn't scale well obviously, and quite often the above operation > results in "could not obtain lock" error. > > Could someone suggest, please, some less brutal way of copying rows, > like in batches, so example? None of the tables have primary keys or > identity columns, by the way. > > TIA!
[quoted text, click to view]
>>>None of the tables have primary keys or >> identity columns, by the way. > Can't wait to hear the Celko answer on this one ;-) He would say, "then you don't have any tables!"
Actually, he would still say the same thing, even if all the tables *did*
have identity columns as the primary key. :-) [quoted text, click to view] "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:e%23G2g5seHHA.4848@TK2MSFTNGP06.phx.gbl... >>>>None of the tables have primary keys or >>> identity columns, by the way. >> Can't wait to hear the Celko answer on this one ;-) > > He would say, "then you don't have any tables!" > >
On 9 Apr 2007 10:32:46 -0700, "SQL Menace" <denis.gobo@gmail.com>
[quoted text, click to view] wrote: >On Apr 9, 1:01 pm, Usenet User < @ . > wrote: >> Environment: SQL 2000 Standard SP4 >> Database recovery mode: Simple >> >> I have a stored proc that once a day transfers a large amount of data >> (2+ million rows usually) from one table to another as follows: >> >> INSERT INTO table1 >> SELECT * FROM table2 >> >> DROP table2 >> >> This doesn't scale well obviously, and quite often the above operation >> results in "could not obtain lock" error. >> >> Could someone suggest, please, some less brutal way of copying rows, >> like in batches, so example? None of the tables have primary keys or >> identity columns, by the way. >> >> TIA! > >What about BCP OUT and then BCP IN or BULK INSERT (this is what is >called a minimally logged operation) > >>>None of the tables have primary keys or >> identity columns, by the way. >Can't wait to hear the Celko answer on this one ;-) >How are you going to do a batch if you don't have a unique identifier? Ok, I agree. Now, suppose I have an identity column included in table2. How would I go about doing the above task in batches?
That by itself does not create a minimally logged operation. There are 5
conditions that must be met to get a minimally logged bulk copy as shown in BooksOnLine under "minimally logged bulk copy". -- Andrew J. Kelly SQL MVP [quoted text, click to view] "SQL Menace" <denis.gobo@gmail.com> wrote in message news:1176139966.289857.258950@p77g2000hsh.googlegroups.com... > On Apr 9, 1:01 pm, Usenet User < @ . > wrote: >> Environment: SQL 2000 Standard SP4 >> Database recovery mode: Simple >> >> I have a stored proc that once a day transfers a large amount of data >> (2+ million rows usually) from one table to another as follows: >> >> INSERT INTO table1 >> SELECT * FROM table2 >> >> DROP table2 >> >> This doesn't scale well obviously, and quite often the above operation >> results in "could not obtain lock" error. >> >> Could someone suggest, please, some less brutal way of copying rows, >> like in batches, so example? None of the tables have primary keys or >> identity columns, by the way. >> >> TIA! > > What about BCP OUT and then BCP IN or BULK INSERT (this is what is > called a minimally logged operation) > >>>None of the tables have primary keys or >> identity columns, by the way. > Can't wait to hear the Celko answer on this one ;-) > How are you going to do a batch if you don't have a unique identifier? > > > Denis the SQL Menace > http://sqlservercode.blogspot.com/ > >
I seriously recommend you look at upgrading to SQL 2005 and use partitioning
to handle this. With partitioning you would not have to copy a single row under the right conditions. But currently if you have an identity column you could create a clustered index on it and do the inserts in smaller bacthes of say 10K at a time. SET NOCOUNT ON DECLARE @Total INT, @Counter INT, @Loops INT DECLARE @Begin INT, @End INT SET @Begin = 1 SET @End = 10000 SET @Counter = 1 SELECT @Total = MAX(R_ID) FROM Test3 SET @Total = 150000 -- For testing purposes -- See how many loops we need to make by dividing the total rows by the -- amount we will work on in each pass SET @Loops = (@Total / 10000) WHILE @Counter <= @Loops BEGIN SET @StartTime = GETDATE() INSERT INTO [dbo].[TestInserts] ([Sub_ID],[LName],[MDate]) SELECT [Sub_ID],[LName],[MDate] FROM TEST3 WITH (NOLOCK) -- or TABLOCK ** WHERE R_ID BETWEEN @Begin AND @End SET @Begin = @Begin + 10000 SET @End = @End + 10000 SET @Counter = @Counter + 1 -- WAITFOR DELAY '00:00:01' END -- Andrew J. Kelly SQL MVP [quoted text, click to view] "Usenet User" < @ . > wrote in message news:46ll13hmveh4b3q3anhifk54il5qccvrd1@4ax.com... > On 9 Apr 2007 10:32:46 -0700, "SQL Menace" <denis.gobo@gmail.com> > wrote: > >>On Apr 9, 1:01 pm, Usenet User < @ . > wrote: >>> Environment: SQL 2000 Standard SP4 >>> Database recovery mode: Simple >>> >>> I have a stored proc that once a day transfers a large amount of data >>> (2+ million rows usually) from one table to another as follows: >>> >>> INSERT INTO table1 >>> SELECT * FROM table2 >>> >>> DROP table2 >>> >>> This doesn't scale well obviously, and quite often the above operation >>> results in "could not obtain lock" error. >>> >>> Could someone suggest, please, some less brutal way of copying rows, >>> like in batches, so example? None of the tables have primary keys or >>> identity columns, by the way. >>> >>> TIA! >> >>What about BCP OUT and then BCP IN or BULK INSERT (this is what is >>called a minimally logged operation) >> >>>>None of the tables have primary keys or >>> identity columns, by the way. >>Can't wait to hear the Celko answer on this one ;-) >>How are you going to do a batch if you don't have a unique identifier? > > Ok, I agree. Now, suppose I have an identity column included in > table2. How would I go about doing the above task in batches? > > TIA!
Don't see what you're looking for? Try a search.
|
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |