Insert data problem
-
Hi all,
I inserted 35,000 records into a table and created a locking in that table. Is there anyway to overcome this.
Thanks,
Minh Vu
-
My goal is to add an identity field so that I instruct my query to insert 1000 record at a time, but I don't know how. Please rescue me.
Thanks,
Minh Vu
-
No offense but inserting only 35k records shouldn't take long. How large is the table?
How many indexes are there on the table?
What's the Primary/key, clustered index?
How long does it take to do the select?
-
Remi not only do you type answers to questions faster than I do but you type questions to questons faster than I do.
Mike
-
Yup
.And I just thaught of a few more :
How many relations do you have on that tables?
Do you have insert triggers?
Do you have a lot of check/default constraints?
What are the specs of the server?
Is tempdb big enough?
Is the target db big enough (autogrow problems)?
-
I don't have any relations on the tables, no insert triggers eithere. My server is good enough. The think is I insert data from a link server.
insert into [122.23.23.2].ed.dbo.product_inventory
select * from tmp_product_inventory.
I tried to add id field in my tmp_table and write a loop to insert 500 records at a time. When I insert 500 records at a time, it did not create a lock. Please show me how to write a loop.
select *, identity(int,1,1) into mytemp from tmp_product_inventory
declare @id
set@id =1
While @id<>0
Begin
set @id =@id+500
insert into [122.23.23.2].ed.dbo.product_inventory
end
But it did not work.
Thanks,
Minh
-
Just go to the linked server and do the insert from there... It's gonna be faster that way... and won't take a loop.
-
I really like to know to do the looping. Can you give me an example?
-
Sorry for the delay :
DECLARE @demo table (RowID int not null identity (1,1) primary key clustered, name varchar(10) not null)
Insert into @demo (Name)
Select 'cats'
UNION ALL
Select 'dogs'
UNION ALL
Select 'birds'
UNION ALL
Select 'chimps'
UNION ALL
Select 'zebras'
Select * from @demo
DECLARE @LastID as int
DECLARE @CurID as int
DECLARE @Name as varchar(10)
SET @LastID = 0
SELECT TOP 1 @CurID = RowID, @Name = Name from @demo order by RowID
WHILE @CurID IS NOT NULL
BEGIN
SET @LastID = @CurID
SET @CurID = NULL
PRINT @Name
SELECT TOP 1 @CurID = RowID, @Name = Name from @demo where RowID > @LastID order by RowID
END
-
Several things wrong with your SQL:
select *, identity(int,1,1) AS ID into mytemp from tmp_product_inventory
declare @id INT
set@id =0
While @id<= (SELECT MAX(ID) FROM mytemp)
Begin
set @id =@id+500
insert into [122.23.23.2].ed.dbo.product_inventory
select col1, col2, ... from mytemp
where id > @id - 500 and id <= @id
end
Really, if you do it from the destination server it should be faster. Or, I'd use DTS which would do a bulk insert.
Dylan Peters
SQL Server DBA -
Thanx for the x rows at the time version... didn't want to write a 2nd versions
.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply