Re: copying data from 1 table to another

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Paul (Paul_at_discussions.microsoft.com)
Date: 10/26/04 

Date: Tue, 26 Oct 2004 12:33:04 -0700

Thanks for he additional information. Staging or temporary tables seems very
useful. Not familiar with DTS packages imagine there is a bit of a learning
curve, will look under Query Analyser help. The process will only have to be
repeated one time but still be nice to have it in a script or package.

"John Bell" wrote:

> Hi Paul
>
> Now you are talking about dbase copy! I was talking about copying SQL Server
> databases!!!
>
> In some cirumstances when alot of manipulation has to be done it is often
> easier to load everything into staging tables and then do whatever work is
> needed using them. A single DTS package may be created to do all of this is
> you wanted a reproducable process, this which is often the case as you may
> to several trial runs before you have the data in the form you want. It
> could also reduce the downtime when you do implement the final data
> migration.
>
>
> John
>
> "Paul" <Paul@discussions.microsoft.com> wrote in message
> news:3BB9FF3D-C82A-4D46-B2F5-DD3C7C3AE793@microsoft.com...
> > Hi thanks for the additional information. Since I have made some changes
> to
> > a few of the tables, added additional fields and added table constraints
> like
> > (no duplicate records allowed) probably should not do a complete dbase
> copy
> > but good information to know.
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > There are several ways to copy a compelete database from one location to
> > > another. The easiest if probably to backup/restore. If you
> backup/restore to
> > > another database on the same computer then you will need to rename data
> and
> > > log files (similarly if they are in a different location on the
> destination
> > > computer you will have to do the same).
> > >
> > > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#2
> > >
> > > If you are doing this from Query Analyser check out the WITH MOVE syntax
> of
> > > the RESTORE command in Books online.
> > >
> > > John
> > >
> > >
> > > "Paul" <Paul@discussions.microsoft.com> wrote in message
> > > news:8F21C830-8066-4875-B0DD-4955F46F3A3B@microsoft.com...
> > > > Thanks for the information. The largest table has around 16k records
> of
> > > > about 10 fields, one other table has around 8k records and the
> remaining 8
> > > > tables are very small. Thinking of just using the query analyser with
> > > insert
> > > > into <database1.table1>
> > > > select * from <database2.table1> since I am somewhat comfortable with
> the
> > > > tool.
> > > > I am assuming you can copy from 1 dbase to another.
> > > > "Sasan Saidi" wrote:
> > > >
> > > > > It depends with which tool you are the most confortable. Quick way
> could
> > > be
> > > > > to use:
> > > > > -insert into <destinationTable> select * from <SourceTable>. Make
> sure
> > > you
> > > > > try a on a test database first.
> > > > >
> > > > > You could also use the bulk copy utility. It depends how much data
> and
> > > how
> > > > > many tables you have. Note that the first solution will increase
> your
> > > > > transaction log size if you have a lot of data.
> > > > >
> > > > > There is also DTS.
> > > > >
> > > > >
> > > > >
> > > > > "Paul" wrote:
> > > > >
> > > > > > just wondering the best way to copy data from one database to
> another
> > > after
> > > > > > doing a dbase port? The destination database works with a .NET
> > > application
> > > > > > and the source database came from a My SQL database (used a mysql
> > > driver and
> > > > > > access to get the data to SQL2000 dbase). Also the source
> database
> > > does not
> > > > > > have constraints setup but the the destination tables do, so just
> want
> > > to
> > > > > > fill the destination tables with the data from the source tables.
> I
> > > have the
> > > > > > Query Analizer tool so was thinking there may be an easy way to
> copy
> > > the data
> > > > > > using this tool.
> > > > > > Thanks.
> > > > > > --
> > > > > > Paul G
> > > > > > Software engineer.
> > >
> > >
> > >
> > >
>
>
>

Relevant Pages

  • Re: Migration Question Again...
    ... You have to migrate the system databases too, specifically Master and MSDB ... As soon as you restore the Master and MSDB all your ... all DTS packages wil be in MSDB database. ...
    (microsoft.public.sqlserver.setup)
  • Migrating DTS to several locations
    ... I have over 182 DTS packages that reside on 54 SQL servers each is a diffrent ... server, the DTS packages pull data from SQL server databases, sybase & ... The plan is to move the databases to 4 SQL servers, ...
    (microsoft.public.sqlserver.dts)
  • what Permissions to Exec DTS ?
    ... We have lots of DTS packages doing all sorts of stuff like ... granting any access on other user databases? ...
    (microsoft.public.sqlserver.security)
  • Re: How to control the security in this case?
    ... > AMB ... one of the databases is maintained by a colleague of a regional ... >> and this colleague is going to make his own packages. ... he can only opens his own the DTS packages. ...
    (microsoft.public.sqlserver.security)