performance for copying data from one table to another

Question

Post reply

performance for copying data from one table to another

Vicki Aversa

SSC Enthusiast

Points: 106
More actions
May 28, 2008 at 1:30 pm

#191607

I am trying to speed up the copying of a large dataset from a table in one database into an empty table of same structure on another database on a different server (both are sql 2005). Both server instances are linked correctly, and can communicate with each other.
Using code
Insert into table1
select *
from table2
I'm pretty new in the DBA arena.
I read that you can only do bulk insert from flat files.
I would like to perform the same type if insert (bulk copy) from one table to another. Logging isn't an issue for the destination database.
I also read in help that you could use table hints to mimic bulk insert, but have found no examples.
ANY help would be appreciated. biggest table has 214 million+ records.
Thank you in advance,
Vicki

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

Use SSIS, there's a data import wizard in SSMS that will help you.

Vicki Aversa SSC Enthusiast Points: 106 More actions · Answer 2

I'm trying to create a single sql statement to do this.

I have a job that parses a metadata table for source Server, database, and tables and destination Server, database, and tables.

It builds the query and runs for each table to extract.

I tried till I was blue in the face to get the source data developers to send me the data in text files. It would have been a no brainer to bulk copy, not to mention the "theoretically correct way" to grab the data. But, this is what I have to work with.

I, also, thought that I could only grab any record added or changed in the source db. But, I have no timestamp for the records to use.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

you'll probably want to set something to walk the clustered index keys. something along the lines of a look that pulls in 100K to 1M rows per shot, walking up the clustered index. Trying to do this in a single statement is probably a bad idea since it's oging to make one or both of the servers plow for a LONG time.... Either way - this is going to be a substantial drain on your servers.

Why are the data developers opposed to a timestamp field? especially one that they wouldn't have to to anything with? Is their "convenience" worth sending these servers into overdrive for hours on end every day?

How big is that "big table"?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Vicki Aversa SSC Enthusiast Points: 106 More actions · Answer 4

While trying to stay politically correct, All I'll say is

this dog rolled over a long time ago.

I stopped trying to make sense about 2 years ago. Now, I just do what I'm told. But, I do put up a token fight, with some rather good suggestions before giving in. But, that discussion is for a whole 'nuther topic.;)

the largest table is at 214 million records now.

I'll look into your suggestion about the indexes.

Thanks

Vicki

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Vicki Aversa (5/28/2008)
While trying to stay politically correct, All I'll say is
this dog rolled over a long time ago.
I stopped trying to make sense about 2 years ago. Now, I just do what I'm told. But, I do put up a token fight, with some rather good suggestions before giving in. But, that discussion is for a whole 'nuther topic.;)
the largest table is at 214 million records now.
I'll look into your suggestion about the indexes.
Thanks
Vicki

I hear you on the the stubbornness of others....

As to walking the clustered index:

- Keep in mind that unless you plan on doing this against a DB with simple recovery, you're going to have some "big mamma" log files on a daily basis.

- this would rely on making both tables (source and destination) have the same clustered key. Except if the source has an identity field - make the destination an int/bigint instead.

With those ideas in mind, it looks something like this (assuming ID is the UCI key):

declare @startid bigint

set @startid = 0;

declare @chunksize bigint;

set chunksize=50000; --<-- scale up or down as performance dictates - you may need to find the sweet spot

declare @endid bigint;

set @endid=(select MaxID from Openquery(MyRemoteSvr,'Select max(id) from MyRemoteTable') M)

declare @sql varchar(max)

While (@startID<@endid)

begin

set @sql='Insert MyLocaltable(MyFieldList,...)

select MyFieldList,....

from Openquery(MyRemoteSvr,''Select MyFieldlist, ...

from MyRemoteTable

where ID between '

+ cast(startID as bigint) +

' AND '

+ cast(startID+chunksize as bigint) +''')'

Exec (@SQL)

set @StartID=@startID+@chunksize+1;

end

End

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Vicki Aversa SSC Enthusiast Points: 106 More actions · Answer 6

thanks a bunch! 🙂

I'll try it out tomorrow.

I'm going HOME!