I want to export 3 tables in a SQL database with the table definition
and want to then import from this dump into another SQL database where
these tables do not exist.
The tables are - Cust, MatProd, Plant
the database to be exported is - SourcePur and the database these table
have to be created and imported is TargetPur.
Please can you share what utility and what command line options I use
to export these tables into one dump and how to import it back.
If I want to use DTS how can I do it.
Thanks
KarenAre the database servers on the same network? Can they "talk" to each
other?
You could use DTS to import data from one server. It is a fairly easy
process. Using DTS in this type of basic scenario is pretty
straightforward. You simply import data from a specific source.
Keith
<karenmiddleol@.yahoo.com> wrote in message
news:1129118048.865757.65170@.g47g2000cwa.googlegroups.com...
>I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>|||Right click on tables--> all tasks-->export data
Then you will get a wizard, set up your source DB and your destination DB,
select the tables hit run and that's it
you can also use BCP to export and import them
http://sqlservercode.blogspot.com/
"karenmiddleol@.yahoo.com" wrote:
> I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>|||DTS or the "bulk copy" command can be used to export the data to a file such
as tab delimited text, MS Access, native, etc.
However, none of these file formats will retain all the table structure
including the exact data type, constraints, triggers, etc. For this, you
will need to use the "Generate Scripts" feature of Enterprise Manager. On
the destination database, run the scripts to re-create empty tables, then
use DTS or bulk copy the files into the appropriate tables.
CREATE TABLE
http://msdn.microsoft.com/library/e...reate2_8g9x.asp
How to generate a script (Enterprise Manager)
http://msdn.microsoft.com/library/e...design_3qt0.asp
Preparing Data for Importing and Exporting
http://msdn.microsoft.com/library/e...pt_bcp_1njt.asp
Copying Data From a Data File to SQL Server
http://msdn.microsoft.com/library/e...pt_bcp_4t9u.asp
<karenmiddleol@.yahoo.com> wrote in message
news:1129118048.865757.65170@.g47g2000cwa.googlegroups.com...
>I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>
Showing posts with label dump. Show all posts
Showing posts with label dump. Show all posts
Wednesday, March 21, 2012
Exporting a table and importing it
I want to export 3 tables in a SQL database with the table definition
and want to then import from this dump into another SQL database where
these tables do not exist.
The tables are - Cust, MatProd, Plant
the database to be exported is - SourcePur and the database these table
have to be created and imported is TargetPur.
Please can you share what utility and what command line options I use
to export these tables into one dump and how to import it back.
If I want to use DTS how can I do it.
Thanks
KarenAre the database servers on the same network? Can they "talk" to each
other?
You could use DTS to import data from one server. It is a fairly easy
process. Using DTS in this type of basic scenario is pretty
straightforward. You simply import data from a specific source.
Keith
<karenmiddleol@.yahoo.com> wrote in message
news:1129118048.865757.65170@.g47g2000cwa.googlegroups.com...
>I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>|||Right click on tables--> all tasks-->export data
Then you will get a wizard, set up your source DB and your destination DB,
select the tables hit run and that's it
you can also use BCP to export and import them
http://sqlservercode.blogspot.com/
"karenmiddleol@.yahoo.com" wrote:
> I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>|||DTS or the "bulk copy" command can be used to export the data to a file such
as tab delimited text, MS Access, native, etc.
However, none of these file formats will retain all the table structure
including the exact data type, constraints, triggers, etc. For this, you
will need to use the "Generate Scripts" feature of Enterprise Manager. On
the destination database, run the scripts to re-create empty tables, then
use DTS or bulk copy the files into the appropriate tables.
CREATE TABLE
http://msdn.microsoft.com/library/e...reate2_8g9x.asp
How to generate a script (Enterprise Manager)
http://msdn.microsoft.com/library/e...design_3qt0.asp
Preparing Data for Importing and Exporting
http://msdn.microsoft.com/library/e...pt_bcp_1njt.asp
Copying Data From a Data File to SQL Server
http://msdn.microsoft.com/library/e...pt_bcp_4t9u.asp
<karenmiddleol@.yahoo.com> wrote in message
news:1129118048.865757.65170@.g47g2000cwa.googlegroups.com...
>I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>sql
and want to then import from this dump into another SQL database where
these tables do not exist.
The tables are - Cust, MatProd, Plant
the database to be exported is - SourcePur and the database these table
have to be created and imported is TargetPur.
Please can you share what utility and what command line options I use
to export these tables into one dump and how to import it back.
If I want to use DTS how can I do it.
Thanks
KarenAre the database servers on the same network? Can they "talk" to each
other?
You could use DTS to import data from one server. It is a fairly easy
process. Using DTS in this type of basic scenario is pretty
straightforward. You simply import data from a specific source.
Keith
<karenmiddleol@.yahoo.com> wrote in message
news:1129118048.865757.65170@.g47g2000cwa.googlegroups.com...
>I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>|||Right click on tables--> all tasks-->export data
Then you will get a wizard, set up your source DB and your destination DB,
select the tables hit run and that's it
you can also use BCP to export and import them
http://sqlservercode.blogspot.com/
"karenmiddleol@.yahoo.com" wrote:
> I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>|||DTS or the "bulk copy" command can be used to export the data to a file such
as tab delimited text, MS Access, native, etc.
However, none of these file formats will retain all the table structure
including the exact data type, constraints, triggers, etc. For this, you
will need to use the "Generate Scripts" feature of Enterprise Manager. On
the destination database, run the scripts to re-create empty tables, then
use DTS or bulk copy the files into the appropriate tables.
CREATE TABLE
http://msdn.microsoft.com/library/e...reate2_8g9x.asp
How to generate a script (Enterprise Manager)
http://msdn.microsoft.com/library/e...design_3qt0.asp
Preparing Data for Importing and Exporting
http://msdn.microsoft.com/library/e...pt_bcp_1njt.asp
Copying Data From a Data File to SQL Server
http://msdn.microsoft.com/library/e...pt_bcp_4t9u.asp
<karenmiddleol@.yahoo.com> wrote in message
news:1129118048.865757.65170@.g47g2000cwa.googlegroups.com...
>I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>sql
Exporting a table and importing it
I want to export 3 tables in a SQL database with the table definition
and want to then import from this dump into another SQL database where
these tables do not exist.
The tables are - Cust, MatProd, Plant
the database to be exported is - SourcePur and the database these table
have to be created and imported is TargetPur.
Please can you share what utility and what command line options I use
to export these tables into one dump and how to import it back.
If I want to use DTS how can I do it.
Thanks
Karen
Are the database servers on the same network? Can they "talk" to each
other?
You could use DTS to import data from one server. It is a fairly easy
process. Using DTS in this type of basic scenario is pretty
straightforward. You simply import data from a specific source.
Keith
<karenmiddleol@.yahoo.com> wrote in message
news:1129118048.865757.65170@.g47g2000cwa.googlegro ups.com...
>I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>
|||Right click on tables--> all tasks-->export data
Then you will get a wizard, set up your source DB and your destination DB,
select the tables hit run and that's it
you can also use BCP to export and import them
http://sqlservercode.blogspot.com/
"karenmiddleol@.yahoo.com" wrote:
> I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>
|||DTS or the "bulk copy" command can be used to export the data to a file such
as tab delimited text, MS Access, native, etc.
However, none of these file formats will retain all the table structure
including the exact data type, constraints, triggers, etc. For this, you
will need to use the "Generate Scripts" feature of Enterprise Manager. On
the destination database, run the scripts to re-create empty tables, then
use DTS or bulk copy the files into the appropriate tables.
CREATE TABLE
http://msdn.microsoft.com/library/en...eate2_8g9x.asp
How to generate a script (Enterprise Manager)
http://msdn.microsoft.com/library/en...esign_3qt0.asp
Preparing Data for Importing and Exporting
http://msdn.microsoft.com/library/en...t_bcp_1njt.asp
Copying Data From a Data File to SQL Server
http://msdn.microsoft.com/library/en...t_bcp_4t9u.asp
<karenmiddleol@.yahoo.com> wrote in message
news:1129118048.865757.65170@.g47g2000cwa.googlegro ups.com...
>I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>
and want to then import from this dump into another SQL database where
these tables do not exist.
The tables are - Cust, MatProd, Plant
the database to be exported is - SourcePur and the database these table
have to be created and imported is TargetPur.
Please can you share what utility and what command line options I use
to export these tables into one dump and how to import it back.
If I want to use DTS how can I do it.
Thanks
Karen
Are the database servers on the same network? Can they "talk" to each
other?
You could use DTS to import data from one server. It is a fairly easy
process. Using DTS in this type of basic scenario is pretty
straightforward. You simply import data from a specific source.
Keith
<karenmiddleol@.yahoo.com> wrote in message
news:1129118048.865757.65170@.g47g2000cwa.googlegro ups.com...
>I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>
|||Right click on tables--> all tasks-->export data
Then you will get a wizard, set up your source DB and your destination DB,
select the tables hit run and that's it
you can also use BCP to export and import them
http://sqlservercode.blogspot.com/
"karenmiddleol@.yahoo.com" wrote:
> I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>
|||DTS or the "bulk copy" command can be used to export the data to a file such
as tab delimited text, MS Access, native, etc.
However, none of these file formats will retain all the table structure
including the exact data type, constraints, triggers, etc. For this, you
will need to use the "Generate Scripts" feature of Enterprise Manager. On
the destination database, run the scripts to re-create empty tables, then
use DTS or bulk copy the files into the appropriate tables.
CREATE TABLE
http://msdn.microsoft.com/library/en...eate2_8g9x.asp
How to generate a script (Enterprise Manager)
http://msdn.microsoft.com/library/en...esign_3qt0.asp
Preparing Data for Importing and Exporting
http://msdn.microsoft.com/library/en...t_bcp_1njt.asp
Copying Data From a Data File to SQL Server
http://msdn.microsoft.com/library/en...t_bcp_4t9u.asp
<karenmiddleol@.yahoo.com> wrote in message
news:1129118048.865757.65170@.g47g2000cwa.googlegro ups.com...
>I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>
Exporting a table and importing it
I want to export 3 tables in a SQL database with the table definition
and want to then import from this dump into another SQL database where
these tables do not exist.
The tables are - Cust, MatProd, Plant
the database to be exported is - SourcePur and the database these table
have to be created and imported is TargetPur.
Please can you share what utility and what command line options I use
to export these tables into one dump and how to import it back.
If I want to use DTS how can I do it.
Thanks
KarenAre the database servers on the same network? Can they "talk" to each
other?
You could use DTS to import data from one server. It is a fairly easy
process. Using DTS in this type of basic scenario is pretty
straightforward. You simply import data from a specific source.
--
Keith
<karenmiddleol@.yahoo.com> wrote in message
news:1129118048.865757.65170@.g47g2000cwa.googlegroups.com...
>I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>|||Right click on tables--> all tasks-->export data
Then you will get a wizard, set up your source DB and your destination DB,
select the tables hit run and that's it
you can also use BCP to export and import them
http://sqlservercode.blogspot.com/
"karenmiddleol@.yahoo.com" wrote:
> I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>|||DTS or the "bulk copy" command can be used to export the data to a file such
as tab delimited text, MS Access, native, etc.
However, none of these file formats will retain all the table structure
including the exact data type, constraints, triggers, etc. For this, you
will need to use the "Generate Scripts" feature of Enterprise Manager. On
the destination database, run the scripts to re-create empty tables, then
use DTS or bulk copy the files into the appropriate tables.
CREATE TABLE
http://msdn.microsoft.com/library/en-us/tsqlref/ts_create2_8g9x.asp
How to generate a script (Enterprise Manager)
http://msdn.microsoft.com/library/en-us/howtosql/ht_7_design_3qt0.asp
Preparing Data for Importing and Exporting
http://msdn.microsoft.com/library/en-us/adminsql/ad_impt_bcp_1njt.asp
Copying Data From a Data File to SQL Server
http://msdn.microsoft.com/library/en-us/adminsql/ad_impt_bcp_4t9u.asp
<karenmiddleol@.yahoo.com> wrote in message
news:1129118048.865757.65170@.g47g2000cwa.googlegroups.com...
>I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>
and want to then import from this dump into another SQL database where
these tables do not exist.
The tables are - Cust, MatProd, Plant
the database to be exported is - SourcePur and the database these table
have to be created and imported is TargetPur.
Please can you share what utility and what command line options I use
to export these tables into one dump and how to import it back.
If I want to use DTS how can I do it.
Thanks
KarenAre the database servers on the same network? Can they "talk" to each
other?
You could use DTS to import data from one server. It is a fairly easy
process. Using DTS in this type of basic scenario is pretty
straightforward. You simply import data from a specific source.
--
Keith
<karenmiddleol@.yahoo.com> wrote in message
news:1129118048.865757.65170@.g47g2000cwa.googlegroups.com...
>I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>|||Right click on tables--> all tasks-->export data
Then you will get a wizard, set up your source DB and your destination DB,
select the tables hit run and that's it
you can also use BCP to export and import them
http://sqlservercode.blogspot.com/
"karenmiddleol@.yahoo.com" wrote:
> I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>|||DTS or the "bulk copy" command can be used to export the data to a file such
as tab delimited text, MS Access, native, etc.
However, none of these file formats will retain all the table structure
including the exact data type, constraints, triggers, etc. For this, you
will need to use the "Generate Scripts" feature of Enterprise Manager. On
the destination database, run the scripts to re-create empty tables, then
use DTS or bulk copy the files into the appropriate tables.
CREATE TABLE
http://msdn.microsoft.com/library/en-us/tsqlref/ts_create2_8g9x.asp
How to generate a script (Enterprise Manager)
http://msdn.microsoft.com/library/en-us/howtosql/ht_7_design_3qt0.asp
Preparing Data for Importing and Exporting
http://msdn.microsoft.com/library/en-us/adminsql/ad_impt_bcp_1njt.asp
Copying Data From a Data File to SQL Server
http://msdn.microsoft.com/library/en-us/adminsql/ad_impt_bcp_4t9u.asp
<karenmiddleol@.yahoo.com> wrote in message
news:1129118048.865757.65170@.g47g2000cwa.googlegroups.com...
>I want to export 3 tables in a SQL database with the table definition
> and want to then import from this dump into another SQL database where
> these tables do not exist.
> The tables are - Cust, MatProd, Plant
> the database to be exported is - SourcePur and the database these table
> have to be created and imported is TargetPur.
> Please can you share what utility and what command line options I use
> to export these tables into one dump and how to import it back.
> If I want to use DTS how can I do it.
> Thanks
> Karen
>
Monday, March 19, 2012
export/data dump to flat file is too slow
I am trying to export a table with ~ 10 Million rows to a flat file and it is taking for ever with SQL2005 export functionality. I have tried creating an SSIS package with a flat-file destination and the results are the same. In each case it does the operation in chunks of about 9900+ rows, and each chunk takes ~1-2 minutes which sounds unreasonable.
I tried bcp, and it fails after a few thousand rows. I tried moving the data to SQL2000 first then to flat file from SQL2K, but the move from SQL2005->SQL2000 was going at the same rate as above.
So, the bottleneck seems to be data going out of SQL2005 no matter what the destination is. I'm wondering if there is some setting that Iam missing that would make this run in a reasonable amount of time?
Never mind. I was outputting it to a share, and that is the reason. When I modified it to do a bcp on the local drive on that server, it was flying like it is supposed to. I'll deal with copying it across the network later. Thanks.
Subscribe to:
Posts (Atom)