November 16, 2009 at 2:02 pm
I am trying to split data from one table into two tables creating a relationship between the 2 new tables.
For 43k rows, this procedure took 41 mins. Is there a more efficient way to do this?
declare @count int
declare @fk int
declare @i int
declare @myError int
set @i = 1
select @count = count(*) from ffout
while (@i <= @count)
begin
begin tran
insert into dbo.table1 (code,name,shortcode)
select req_code,req_nametext,short_code from ffout where rowid = @i
select @myError = @@error
IF @myERROR != 0 GOTO HANDLE_ERROR
insert into dbo.table2(fk,program)
select @i,program from ffout where rowid = @i
select @myError = @@error
set @i = @i + 1
IF @myERROR != 0 GOTO HANDLE_ERROR
COMMIT TRAN -- No Errors, so go ahead
HANDLE_ERROR:
ROLLBACK TRAN
end
November 16, 2009 at 2:18 pm
Use this.
CREATE TABLE #Temp ( ID INT IDENTITY(1,1) NOT NULL, code INT, [name] VARCHAR(50), shortcode VARCHAR(50),
program VARCHAR(50) )
INSERT INTO #Temp
SELECT code,name,shortcode,program FROM ffout
INSERT INTO dbo.table1
SELECT code,name,shortcode FROM #Temp
INSERT INTO dbo.table2
SELECT ID,program FROM #Temp
DROP TABLE #Temp
I am not sure of the Datatypes for code, name, shortcode, program so change if they are not matching your table definition.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 18, 2009 at 8:20 am
Thanks so much!
November 18, 2009 at 8:30 am
stack_900 (11/18/2009)
Thanks so much!
You are welcome !
Did it solve your problem, if so how long did it take?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 18, 2009 at 9:22 am
A guess? With 43K rows, say about a second or two. Obviously a big improvement from the RBAR (while loop) method originally used. 😉
February 12, 2010 at 9:56 am
Bru/Lynn
Thank you for posting the help on this query - I have found it very useful as I have just performed the same operation with 10m rows and 35 columns. It took about 30 mins to split the orginal table into 4 tables. Does this sound reasonable? Does the same theory apply for splitting data from 1 to 4 tables?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply