locked
Super slow performance while using Import Export Wizard RRS feed

  • Question

  • Hi,

    I have to import a table from 1 server to another and I am using the Import/Export Wizard for SQL Server. I have about 23 million rows and it is running very very slow. What can I do to optimize its performance? Or is there any other way I can transfer the data from 1 table to another?

    Thanks!
    Monday, August 10, 2009 3:24 PM

Answers

  • Ok...I got it! INSERT INTO ...SELECT FROM... does the trick. I found that SELECT INTO does not work while using linked server.

    Thanks!
    • Marked as answer by kesar_k Monday, August 10, 2009 7:34 PM
    Monday, August 10, 2009 5:07 PM

All replies

  • Yes to optimize SSIS you can use
    http://msdn.microsoft.com/en-us/library/cc966529.aspx

    other ways , Yes use a Linked Servers object (opem SSMS -> Server objects-> Linked servers)  in your SSMS and make a SP to insert in to the destiantion server
    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post
    Monday, August 10, 2009 3:29 PM
  • The msdn link does not provide the information that I am looking for. I am not using integration services. I am just simply trying to import/export from SSMS.
    I will look into the linked object but any way to optimize the Import/Export Wizard itself?
    Monday, August 10, 2009 3:32 PM
  • I had to transfer 3.3mil and it took 12:29 min:sec , so you are dealling with a big number ,
    the Import/Export has a option for you so that you can save your impo/exp as a SSIS pacakge
    why am i suf=geesting to usea SP and a simple INSERT because you can chack or force the system to use the indexes that is faster , or can help you the most
    its just a suggestion
    + transfering data depends on lots of things

    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post
    Monday, August 10, 2009 3:37 PM
  • I created a linked server to the remote database that I need to insert the data into. Now when I am running the following query for example:

    SELECT *
    INTO MyLinkServer.DBName.dbo.TableName
    FROM LocalDB.dbo.TableName

    I am getting the following error:

    Msg 117, Level 15, State 1, Line 2
    The object name 'MyLinkServer.DBName.dbo.TableName' contains more than the maximum number of prefixes. The maximum is 2.

    Can I not insert into the remote serverusing linked server?

    Thanks!

    Monday, August 10, 2009 4:51 PM
  • Ok...I got it! INSERT INTO ...SELECT FROM... does the trick. I found that SELECT INTO does not work while using linked server.

    Thanks!
    • Marked as answer by kesar_k Monday, August 10, 2009 7:34 PM
    Monday, August 10, 2009 5:07 PM
  • SELECT *
    INTO tblNew
    FROM tbl ...


    this code will make a NEW TABLE you can not use an excisting table, you must use (assuming that you have the table on the destination server)

    INSERT INTO tblLocalTable (Col1, col2.......)
    SELECT col1, col2 ..... FROM LinkedServerName.DBNAme.SchemaName.TblOrViewName

    OR

    INSERT INTO LinkedServerName.DBNAme.SchemaName.TblName(Col1, col2.......)
    SELECT col1, col2 ..... FROM tblLocalTable
    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post
    • Proposed as answer by Nik - Shahriar Nikkhah Monday, August 10, 2009 5:24 PM
    • Marked as answer by kesar_k Monday, August 10, 2009 5:34 PM
    • Unmarked as answer by kesar_k Monday, August 10, 2009 7:34 PM
    • Unproposed as answer by kesar_k Monday, August 10, 2009 7:34 PM
    Monday, August 10, 2009 5:13 PM