Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 How to do quick insert in SQL Server databases
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

d
Starting Member

26 Posts

Posted - 12/26/2001 :  14:03:31  Show Profile  Reply with Quote
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

USA
3246 Posts

Posted - 12/26/2001 :  14:20:30  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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...
Go to Top of Page

d
Starting Member

26 Posts

Posted - 12/26/2001 :  14:48:31  Show Profile  Reply with Quote
Are you using INSERT ... SELECT ... syntax? -> YES
Is the destination table empty to begin with? -> YES
It'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? -> YES
If 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.



Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 12/26/2001 :  15:15:23  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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...
Go to Top of Page

d
Starting Member

26 Posts

Posted - 12/26/2001 :  15:18:53  Show Profile  Reply with Quote
"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.

Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 12/26/2001 :  15:29:45  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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...
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000