x

how quickly append data of tables from one database to other database using sql import /export wizard?

alt text

hi, i want to know how quickly append the one db to other db using sql import /export wizard?

source db: database1

destination db: database2

table : same table name exists in both databases

i have same two tables present in database1 and database2. So i want to append the table data using queries one db to another db. But My problem is 400000(4lakh) data causes nearly 80 mint (1 hour 20 mint). Can we reduce this time..using import/export wizard...or any other method available in sql server to append the data? here i gave sample query i used to append the data... as below...

 select COL1,COL2,COL3,COL4,COL5,COL6,COL7
 from 
 (  
 select row_number()over(order by Trackingid)
 [sno],COL1,COL2,COL3,COL4,COL5,COL6,COL7
 from databasename1.dbo.tablename
 WHERE COL3<'2012-01-01'
 )  
 as a  
 where [sno] between 1 and 400000
 order by sno

plz any one help........me...............to reduce this time consuming.........

more ▼

asked Jun 28, 2012 at 06:58 AM in Default

avatar image

askmlx121
2.5k 72 76 83

(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

The time it takes to transfer this data isn't under the control of the import wizard. There may be some settings you could tune to make this run more quickly but I would take a guess that the biggest issue is how optimal your query is and the hardware that you are running on.

Have you tried using the TOP operator to control the rows retrieved rather than the nested select and ROW_NUMBER?

something like

 select TOP 40000 
  COL1,
  COL2,
  COL3,
  COL4,
  COL5,
  COL6,
  COL7
 from publicsitestracking.dbo.tracking
 WHERE COL3 < '2012-01-01'
 ORDER BY TrackingID

Depending on the data in the tracking table you may need to review the indexing so that these records can be located as efficiently as possible.

more ▼

answered Jun 28, 2012 at 07:08 AM

avatar image

Fatherjack ♦♦
43.8k 79 99 118

+1. Rightly said... The problem is with the query....Assigning Sr. No to all the table values and then filteration on Sr. No and then ORDERing them.... Just making sure that server do all kind of processing. TOP with order by TrackingId should suffice assuming it is the clustered key.

Jun 28, 2012 at 07:27 AM Sacred Jewel

hello, sacred, when avoid assigning.....sno....import and export runs very long time to execute..........it did not transfer the data........

Jun 28, 2012 at 08:38 AM askmlx121
(comments are locked)
10|1200 characters needed characters left

Since the same table exits in both DB. why not just rename the current table in the destination source,using import export wizard to export the necessary table from the source database to the destination db.Check the imported table before deleting the rename one.NB Rename is just for security reason.

more ▼

answered Jun 28, 2012 at 10:37 AM

avatar image

ecomma
470 18 22 26

The rename table can later be deleted.

Jun 28, 2012 at 10:40 AM ecomma

it would depend on the data in the table, if the table has 100M rows then thats a lot of data to move (possibly to a destination that doesnt have the capacity) and then drop.

we need more info from OP

Jun 28, 2012 at 10:47 AM Fatherjack ♦♦

that correct Fatherjack

Jun 28, 2012 at 10:51 AM ecomma
(comments are locked)
10|1200 characters needed characters left

Add an index on col3. it may help.

more ▼

answered Jun 28, 2012 at 08:59 AM

avatar image

Pamz
30 1 1 4

I'm not sure that you can make a statement like that without knowing more about the table, the data in it and the other processes accessing it. you could make things very much worse.

Jun 28, 2012 at 09:10 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

Sub queries will always be time consuming... It will be better to append all the data in the table, and then use programming logic for getting the desired result you want, or to delete the unwanted records later. People do De-normalization, sometimes, for saving time.

If you are using SQL 2008 R2, you can try the option as follows.

1.Create a new package,

2.Select your source and destinations

3.And then opt "copy Data from one or more tables" rather than using "Write a Query to specify the data to transfer".

4.Click Next and select your table.

5.Click "Edit mappings", and select the option "Append rows to the destination Table".

It will copy only those are not present in destination Table.

more ▼

answered Jun 18, 2013 at 11:59 AM

avatar image

unsrockr08
0

(comments are locked)
10|1200 characters needed characters left

The speed to Import/Export affected by The processing power of your PC OR server.The design of your database could be the cause as well.Consider the size of the data and the network band with as well

more ▼

answered Jun 18, 2013 at 12:13 PM

avatar image

ecomma
470 18 22 26

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x71
x21

asked: Jun 28, 2012 at 06:58 AM

Seen: 4107 times

Last Updated: Jun 18, 2013 at 12:16 PM

Copyright 2017 Redgate Software. Privacy Policy