Re: Copy complete tables - What is the best pattern?

From: Adam Machanic (amachanic_at_hotmail._removetoemail_.com)
Date: 01/07/05 

Date: Fri, 7 Jan 2005 09:10:56 -0500

20 tables? -- how about something totally different? Create a new database
just for those tables, use sp_detach_db, detach it, copy it to a new
location, re-attach it using sp_attach_db, and then use sp_renamedb to swap
in the new database...

That will be virtually instant and use very few resources. And it will
maintain your old data in the same place (once you re-attach the
database)... And it's very uncomplicated. 

-- 
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Xian" <xcits@newsgroup.nospam> wrote in message
news:A7453404-CB93-4488-96E5-9FE1876D2329@microsoft.com...
> Hi!
>
> Thanks for the answers. Here a little bit more information about the
system:
>
> I don't have different databases. TableA and TableB are in the same
physical
> database.
>
> The data in the "master" Table can be changed on a timeframe between 1 day
> and 6 months (!) (it depends on the bussines case). In this timeframe the
> data will be changed daily from aprox. 5 useres.
>
> Also the real world application has not only TableA and TableB. There are
> aprox. 20 Tables which have relationships between them and for all the
Tables
> I need the "copy" solution.
>
> One Table holds aprox. 10000 rows, each row has 20 - 100 kB (there is much
> of NTEXT in each table.)
>
> So the drop and rebuild solution is not suiteable for me, because after
> "copyying" from TableA to TableB the superusers go on to work on TableA.
>
> Delta copy looks a little bit to copylicated in this case for me and I
have
> concerns about unsynchronised data. I need really a 100% exact copy from
> TableA in TableB. (Also the unknown timeframe from 1 day to 6 monts looks
a
> little bit to long delta copy.)
>
> I'm looking for a solution which does need the least ressources on the
> server (the duration for the copy-operation is not so important (in the
worst
> case this can be up to 1 day! ;-) )
>
> So I hope, this input was helpfull.
>
> Looking forward to hear from you,
>
> regards,
>
> Chris
>

Relevant Pages

  • Re: Query with mathematics criteria!!?? Help!
    ... I might not even add the new record to the database if it doesn't ... > Create a Cartesian join query by adding TableA and TableB to the query ...
    (microsoft.public.access.queries)
  • How to change data displayed in one GridView depending on selection made in the second one? (ASP.NET
    ... I have two tables in my database: TableA and TableB. ... GridViewA - in this one records from TableA should be displayed. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Subscribing to a Transactional Publication
    ... I have a database table [TableA] that continuously has information ... I need to create another database containing [TableB] on another server ...
    (microsoft.public.vb.general.discussion)
  • Re: easy way of creating update commands?
    ... I wish to move all the rows from TableA on Server 1 to TableA ... My intention was to run this same code on tables B-Z as well but I failed to take into consideration the insert command that was created for tableA wouldn't work on tables B-Z. ... *DataAdapter class is just a set of data commands and a database connection that are used to fill the DataSet and update a SQL Server database. ...
    (microsoft.public.dotnet.languages.vb)
  • Basic Question about DELETE
    ... Just a basic question regarding a relational database like MySQL. ... I am using a MySQL database wherein tables are, naturally, linked to other ... Say I have tableA and tableB. ...
    (comp.lang.java.databases)