split data from one table to 2 tables with a Fkey relationship
-
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
-
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 #TempSELECT code,name,shortcode,program FROM ffoutINSERT INTO dbo.table1SELECT code,name,shortcode FROM #TempINSERT INTO dbo.table2SELECT ID,program FROM #TempDROP TABLE #TempI am not sure of the Datatypes for code, name, shortcode, program so change if they are not matching your table definition.
Bru MedishettyBlog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
-
Thanks so much!
-
stack_900 (11/18/2009)
Thanks so much!You are welcome !
Did it solve your problem, if so how long did it take?
Bru MedishettyBlog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
-
A guess? With 43K rows, say about a second or two. Obviously a big improvement from the RBAR (while loop) method originally used.
-
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