Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / DB Engine / SQL Server / September 2007

Tip: Looking for answers? Try searching our database.

Bulk Move

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Ali - 30 Sep 2007 14:26 GMT
hi,
I am using Microsoft SQL server 2005. I want to move a large set of
data with a specific condition to other database in the same machine.

for example after a period of time I want to archive data by the
condtion of time ( in a specific year ) to other database. that
archive database is going to store the previous data which are
archived.

Am I going to use queries to move the data for each table? or there is
a better way?
what is the best approach to do so?

Thanks in advance,

Ali
Uri Dimant - 30 Sep 2007 14:42 GMT
Ali
What is large set of data means?
You can have a job which does an INSERT statement  every day ( that's
probably  is not too large set fo data)  or

INSERT INTO......dbname.dbo.tabl and having properly created indexes on the
source tables

> hi,
> I am using Microsoft SQL server 2005. I want to move a large set of
[quoted text clipped - 12 lines]
>
> Ali
Jeje - 30 Sep 2007 14:58 GMT
do you want to COPY or MOVE the data?

if it's a copy you can use the synchronization feature of SQL Server. this
process take a copy  of the source database at a regular basis (up to a
realtime synchonization)

if you want to control everything (like which rows you want to copy by
applying filters)  and/or want to delete the source data (so moving the
data):
you can do it by running SQL scripts or using SSIS
so you have to write the SQL statements required to copy the expected rows
and delete them in the source if you want to remove them.

> hi,
> I am using Microsoft SQL server 2005. I want to move a large set of
[quoted text clipped - 12 lines]
>
> Ali
Dan Guzman - 30 Sep 2007 15:02 GMT
Why are you archiving data?  If the purpose is manageability, you might
consider partitioning instead of moving data to another database (assuming
SQL 2005 Enterprise Edition).  You can also use a partition switch to
efficiently delete or insert mass amounts of data by partition as part of
your move process.

You'll need to use queries to extract and delete data if partitioning is not
an option.  Bulk insert (especially minimally logged) is faster than INSERT
when large amounts of data are involved.  A common technique to load large
amounts of data is to remove indexes on the destination table, perform a
minimally logged bulk insert (perhaps in a SSIS package) and then recreate
indexes and constraints.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP

> hi,
> I am using Microsoft SQL server 2005. I want to move a large set of
[quoted text clipped - 12 lines]
>
> Ali
Ali - 30 Sep 2007 16:37 GMT
On Sep 30, 5:02 pm, "Dan Guzman" <guzma...@nospam-
online.sbcglobal.net> wrote:
> Why are you archiving data?  If the purpose is manageability, you might
> consider partitioning instead of moving data to another database (assuming
[quoted text clipped - 31 lines]
>
> > Ali

Thanks for the answers. so I think I'd better use partitioning than
copying or moving them to other database.
My reason was the speed of retrieving data. I think partitioning can
do the Job.

Thanks,
Ali
Dan Guzman - 30 Sep 2007 19:21 GMT
> My reason was the speed of retrieving data. I think partitioning can
> do the Job.

Partitioning will help manageability but not necessarily performance.  The
key to data retrieval performance is indexing and query tuning.  Appropriate
indexes reduce the amount of data that needs to be touched without
physically moving data data via archiving or partitioning.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP

> On Sep 30, 5:02 pm, "Dan Guzman" <guzma...@nospam-
> online.sbcglobal.net> wrote:
[quoted text clipped - 46 lines]
> Thanks,
> Ali
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.