Is bulk insert possible between 2 db's without using files on filesystem?

Question

Is bulk insert possible between 2 db's without using files on filesystem?

sporoy

Hall of Fame

Points: 3114
More actions
August 23, 2010 at 2:19 am

#222980

We are uo to make a high speed data transfer between two databases residing on the same instance, is it possible to transfer the data with bulk load speed without using intermediate files?

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

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

sturner SSC-Insane Points: 22411 More actions · Answer 1

The fastest way without using bulk insert is to drop any indexes/foreign keys in the destination tables then use insert select from source tables directly. Re create the indexes anf RI (if you need to) after the data has been inserted.

The probability of survival is inversely proportional to the angle of arrival.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

SSIS will move this data without intermediate tables.

I'd follow Mr. Turner's suggestions as well

sporoy Hall of Fame Points: 3114 More actions · Answer 3

I see but SSIS does not seem optimal for our case, let me clarify a bit more, I have two source tables and 15 destination databases (2 table each), I have a [type] column according to which the rows will be moved to corresponding destination db, first I used for each loop container at SSIS but this way there is no paralellism, what I try to achive is to let each destination databases write at the same time, so we switched to creating agent jobs for each type.. So he best way seems to be :

INSERT INTO DBDest1.dbo.DestinationTable(,,,)

SELECT (,,,) FROM DBSource.dbo.SourceTable WHERE Type='XX

'

Another question, dropping index and FK relationships on the destination for performans, does this mean dropping the clustered index and PK constraint too?

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

You can do that in SSIS without using any looping mechanism. Just set your data source using a query, something like SELECT col1, col2, col3 FROM MyTable WHERE type = 'XX'. You can run types XX, YY and ZZ in parallel or one after the other, depending on how you get the best performance.

Yes, dropping indexes does mean dropping primary key constraints. For that reason alone, it is best to do this sort of operation when there is no activity on the database. You don't need to drop foreign key constraints, but you have to be careful in which order you populate the tables where there are dependencies.

John

Luzi62 Ten Centuries Points: 1218 More actions · Answer 5

If the Source in allways in the same Instance why not just making a View with the select pointing in the source DB and dont move the data at all? Would be fast and always up to date.

regards

sporoy Hall of Fame Points: 3114 More actions · Answer 6

Luzi62 (8/24/2010)
If the Source in allways in the same Instance why not just making a View with the select pointing in the source DB and dont move the data at all? Would be fast and always up to date.
regards

Architecture has been set as multi database, I think developer guys won't accept that..

Regards