Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
d
Starting Member
26 Posts |
 Posted - 2001-12-26 : 14:03:31
|
| I have SQL Server 7.0 databases on 2 different machines called A and B.I have to insert records from a table in B into a table in A.The table in B has over 6,00,000 records.I am using insert statement to insert records in a table in A from a table in B. This insert is taking forever to run.Is there a quicker way of doing this insert???Thanks for the help. |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-26 : 14:20:30
|
| Are you using INSERT ... SELECT ... syntax?Is the destination table empty to begin with? It's probably constantly resizing your database.Do indexes exist on the destination table? You might gain performance by removing them first, especially if they are clustered.Is the destination table being REPLACED by the source table? If so, have you looked into DTS - Transfer SQL Server objects?--------------------------------------------------------------1000 Posts, Here I come! I wonder what my new title will be... |
 |
|
|
d
Starting Member
26 Posts |
Posted - 2001-12-26 : 14:48:31
|
| Are you using INSERT ... SELECT ... syntax? -> YESIs the destination table empty to begin with? -> YESIt's probably constantly resizing your database.Do indexes exist on the destination table? ->YES. There are quite a few indexes. Only one of the indexes is clustered.You might gain performance by removing them first, especially if they are clustered.Is the destination table being REPLACED by the source table? -> YESIf so, have you looked into DTS - Transfer SQL Server objects? -> NO, I am not aware of this concept. Can you please provide me some info regarding this.I will really appreciate your help regarding this.Thanks. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-26 : 15:15:23
|
| Well, despite my general distaste for wizards, this one seems to work okay. So, go into SQL Enterprise Manager (EM). Open the server/database of your source, open the list of tables, right-click on the source table, choose All Tasks... Export.This should start the DTS wizard asking you for the SOURCE and DESTINATION SQL Servers. On screen 3, choose Transfer SQL Server Objects, and on screen 4 UNcheck Transfer All Objects and go into the Choose button to select just the one table. Walk through the rest to run immediately, and it should start grinding the gears to move your data.In general, the DTS (Data transformation Services) system is designed to move data from any ODBC-compliant source to any ODBC-compliant destination. This is handy for pulling data out of Access into SQL Server, etc., etc. It also will move data between SQL Servers. It is good for "one-time-only" updates, but not recommended for using as your primary backup utility or data replication scheme.Hope that Helps...--------------------------------------------------------------1000 Posts, Here I come! I wonder what my new title will be... |
|
|
|
d
Starting Member
26 Posts |
Posted - 2001-12-26 : 15:18:53
|
| "It also will move data between SQL Servers."It means it will copy the data from one SQL Server to another one. It will not move but copy.In case I am incorrect, please let me know.Thanks. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-26 : 15:29:45
|
You are correct. I should have said COPY. Sorry for being sloppy there.  --------------------------------------------------------------1000 Posts, Here I come! I wonder what my new title will be... |
|
|
|
|
|
|
|
|
How to do quick insert in SQL Server databases