July 29, 2009 at 8:58 am
How much time Appx. should it take for 4.7 Million records to be inserted in a table from Linked Server?? Both servers are at same location. There are 3 joins in the query, one to the temp table on local server and other 2 connecting to two different tables on Linked Server with same column...
Thanks,
apat
July 29, 2009 at 10:19 am
It Depends on the servers, the network, and activity. I'd be more inclined to use bcp/bulk insert or SSIS to do something like this.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 29, 2009 at 12:50 pm
Jack Corbett (7/29/2009)
It Depends on the servers, the network, and activity. I'd be more inclined to use bcp/bulk insert or SSIS to do something like this.
BCP utility would be helpful. Below is the MSDN article where you can read up on it:
http://msdn.microsoft.com/en-us/library/ms162802.aspx
The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. Except when used with the queryout option, the utility requires no knowledge of Transact-SQL. To import data into a table, you must either use a format file created for that table or understand the structure of the table and the types of data that are valid for its columns.
bcp {[[database_name.][schema].]{table_name | view_name} | "query"}
{in | out | queryout | format} data_file
[-m max_errors] [-f format_file] [-x] [-e err_file]
[-F first_row] [-L last_row] [-b batch_size]
[-n] [-c] [-N] [-w] [-V (70 | 80 | 90 )]
[-q] [-C { ACP | OEM | RAW | code_page } ] [-t field_term]
[-r row_term] [-i input_file] [-o output_file] [-a packet_size]
[-S server_name[\instance_name]] [-U login_id] [-P password]
[-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]
C. Copying data from a file to a table
The following example illustrates the in option by using the file created in the preceding example (Currency.dat). First, however, this example creates an empty copy of the AdventureWorks Sales.Currency table, Sales.Currency2, into which the data is copied. The example assumes that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command.
--To create the empty table, in Query Editor, enter the following command:
USE AdventureWorks;
GO
SELECT * INTO AdventureWorks.Sales.Currency2
FROM AdventureWorks.Sales.Currency WHERE 1=2
--To bulk copy the character data into the new table, that is to import the data, enter the following command at a command prompt:
bcp AdventureWorks.Sales.Currency2 in Currency.dat -T -c
--To verify that the command succeeded, display the contents of the table in Query Editor, and enter:
USE AdventureWorks;
GO
SELECT * FROM Sales.Currency2
Above explanation and sample bcp syntax from MSDN.
Thanks,
S
--
:hehe:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply