copying data from 1 table to another

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 dat
a
using this tool.
Thanks.
--
Paul G
Software engineer.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 afte
r
> doing a dbase port? The destination database works with a .NET applicatio
n
> and the source database came from a My SQL database (used a mysql driver a
nd
> access to get the data to SQL2000 dbase). Also the source database does n
ot
> 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 t
he
> Query Analizer tool so was thinking there may be an easy way to copy the d
ata
> using this tool.
> Thanks.
> --
> Paul G
> Software engineer.|||Hi
If the source database is outside SQL Server you may want to look at DTS or
the Import Wizard in Enterprise Manager (assuming that you don't have
MSDE!). If you save this as a package then it will be modifyable and
repeatable. If they are in the same database you can use INSERT...SELECT
statements (See Books online) to copy from one table to another. These could
be put into a script or DTS package so that you can run them when needed.
John
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2EE0D663-4101-400B-8EBF-BEB8BF188FBF@.microsoft.com...
> 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.|||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 inser
t
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:
[vbcol=seagreen]
> It depends with which tool you are the most confortable. Quick way could b
e
> to use:
> -insert into <destinationTable> select * from <SourceTable>. Make sure yo
u
> 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:
>|||Thanks for the response. The source database was mysql but I now have it on
SQL2000 server, same server as the destination database. Will probably try
to use the insert, select statements, hopefully can use them copying between
databases. A DTS package seems to be quite useful but have not created any
before but will look into this in the future.
"John Bell" wrote:

> Hi
> If the source database is outside SQL Server you may want to look at DTS o
r
> the Import Wizard in Enterprise Manager (assuming that you don't have
> MSDE!). If you save this as a package then it will be modifyable and
> repeatable. If they are in the same database you can use INSERT...SELECT
> statements (See Books online) to copy from one table to another. These cou
ld
> be put into a script or DTS package so that you can run them when needed.
> John
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2EE0D663-4101-400B-8EBF-BEB8BF188FBF@.microsoft.com...
> after
> application
> and
> not
> the
> data
>
>|||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/defaul...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[vbcol=seagreen]
> 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:
>
be[vbcol=seagreen]
you[vbcol=seagreen]
how[vbcol=seagreen]
after[vbcol=seagreen]
application[vbcol=seagreen]
driver and[vbcol=seagreen]
does not[vbcol=seagreen]
to[vbcol=seagreen]
have the[vbcol=seagreen]
the data[vbcol=seagreen]|||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 lik
e
(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 an
d
> log files (similarly if they are in a different location on the destinatio
n
> computer you will have to do the same).
> http://support.microsoft.com/defaul...en-us;Q314546#2
> If you are doing this from Query Analyser check out the WITH MOVE syntax o
f
> the RESTORE command in Books online.
> John
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:8F21C830-8066-4875-B0DD-4955F46F3A3B@.microsoft.com...
> insert
> be
> you
> how
> after
> application
> driver and
> does not
> to
> have the
> the data
>
>|||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[vbcol=seagreen]
> but good information to know.
> "John Bell" wrote:
>
backup/restore to[vbcol=seagreen]
and[vbcol=seagreen]
destination[vbcol=seagreen]
of[vbcol=seagreen]
of[vbcol=seagreen]
remaining 8[vbcol=seagreen]
the[vbcol=seagreen]
could[vbcol=seagreen]
sure[vbcol=seagreen]
and[vbcol=seagreen]
your[vbcol=seagreen]
another[vbcol=seagreen]
database[vbcol=seagreen]
want[vbcol=seagreen]
I[vbcol=seagreen]
copy[vbcol=seagreen]|||Thanks for he additional information. Staging or temporary tables seems ver
y
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 b
e
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 Serv
er
> 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 i
s
> 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...
> to
> like
> copy
> backup/restore to
> and
> destination
> of
> of
> remaining 8
> the
> could
> sure
> and
> your
> another
> database
> want
> I
> copy
>
>