Exporting Multiple tables to a single file

I need to export data from multiple tables into one single file. The big problem here is that the tables will have different column types.

I am attempting to create something that allows users to be able to send me the contents of their tables's, through either email or ftp. I would prefer to make it easier for them so they only have to deal with one file, instead of the multiple files that bcp and dts create when exporting from multiple tables.

I was thinking of using DTS or BCP and then join (append) the files (either zip them or append the files together in some fashion), but I was hoping that there was an easier method out there.

Any ideas on how I may accomplish this would be greatly appreciated.

AndyWhy not create a table that matches the final datatype/s, do an insert from all the tables that need to be exported and then dump the table to a file? At the end, drop the table or truncate it. Varchar datatype usually does wel in storing most datatypes.|||I'd have DTS:

1 Build a scratch directory,
2 Create the needed files in the scratch directory
3 Zip the entire scratch directory
4 Email the zip file
5 Delete the files and scratch directory
6 Delete the zip file if it wasn't needed any more

-PatP|||I am attempting to create something that allows users to be able to send me the contents of their tables

WAIT: Fundamental flaw in process!

I'd have a sproc perform a backup and then send the backup file (after it was zipped).|||WAIT: Fundamental flaw in process!

I'd have a sproc perform a backup and then send the backup file (after it was zipped).If what needed to be sent was over 50% of the database, then I'd agree with Brett. If you only want 50 Kb of a 30 Gb database and the users connect via dial up, I like my plan better. I guess a lot depends on the circumstances.

-PatP|||"It Depends"

My favorite answer...|||"It Depends"

My favorite answer...Darned if I don't like that one too!

-PatP|||I think I forgot to mention one detail that might complicate things. I'm planning on doing some sort of web based .net application with C#. Is it difficult to use DTS through an application like this?

I know how to do sql queries in .net but is there some way to use DTS in this type of app?|||There are actually many ways to use a DTS package from c# (http://support.microsoft.com/default.aspx?scid=kb;en-us;810581).

-PatP|||And how about BCP in this situation?

Exporting multiple tables as flat files

Hi.

I've tried to create a SSIS package to simply export a bunch of tables as flat files, and am having troubles because when the for each loop hits the second table the column mappings in the flat file destination are not synchronised with its schema.

I created a for each loop with an enumerator that returns the table names and sets a user variable.

I created a data flow task which dynamically connects to the table name variable.

In the Flat File Destination there is a column mapping property, but I don't know how to reset these mappings on each iteration.

Any ideas?

Are all the tables you are trying to export have exactly same column metadata? If not, you can't do this using a for loop. This is because SSIS cannot dynamically update the column mapping/schema. If the tables have different metadata, you need to create separate data flow tasks for them.|||Thanks.

Exporting Data from tables to text file all at once

Hello !!!

Can u please suggest me , the way to Export the data from all the tables of the Data Base at a time in text files individually with file names similar to the table names .

So if i had 200 tables in my data base . i require 200 text files each having the data of each table in the data base.

File name of text file should be equal to that of the file name of table.

Quote:

Originally Posted by bvdrsganesh1981

Hello !!!

Can u please suggest me , the way to Export the data from all the tables of the Data Base at a time in text files individually with file names similar to the table names .

So if i had 200 tables in my data base . i require 200 text files each having the data of each table in the data base.

File name of text file should be equal to that of the file name of table.

Hi ,
You can take script the object in sql server along with the data with separeate object name.

Exporting data from Excel to SQL DATABASE

Hi,
Is it possible to export data from Excel spreadsheet to different tables in
SQL SERVER DATABASE using XML? & how? I have Excel 2003 on my computer. I
need to do this every time the user , say clicks a button on the Excel sheet
.
Thanks for any help.
--
pmudYou probably want to ask this in the Office/Excel newsgroup regarding
specific functionality in Excel. You can obviously export the XML and then
using any of the XML import functionality of SQL Server 2000 such as the
SQLXML XML Bulkload object or OpenXML in T-SQL...
Best regards
Michael
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:231C0050-33FD-4487-B7BD-24FC870E86B1@.microsoft.com...
> Hi,
> Is it possible to export data from Excel spreadsheet to different tables
> in
> SQL SERVER DATABASE using XML? & how? I have Excel 2003 on my computer. I
> need to do this every time the user , say clicks a button on the Excel
> sheet.
> Thanks for any help.
> --
> pmud|||Hi Michael,
As you said that I can export XML & then using XML import functionality fo
sql server 2000 , I can export data from excel to SQL database. But do I hav
e
to do this process everytime a spreadsheet comes in ? or this can be coded
somewhere so that the process is automatically taken care of?
Is there any walkthrough or aticle which I can read.. Thanks..
"Michael Rys [MSFT]" wrote:

> You probably want to ask this in the Office/Excel newsgroup regarding
> specific functionality in Excel. You can obviously export the XML and then
> using any of the XML import functionality of SQL Server 2000 such as the
> SQLXML XML Bulkload object or OpenXML in T-SQL...
> Best regards
> Michael
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:231C0050-33FD-4487-B7BD-24FC870E86B1@.microsoft.com...
>
>|||I would assume that you could use scripting to do it, but I am not a
scripting expert and I think checking with the Excel experts would be a good
idea.
Best regards
Michael
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:908D5EA2-E452-4165-8B35-43FFBC1EEA23@.microsoft.com...
> Hi Michael,
> As you said that I can export XML & then using XML import functionality fo
> sql server 2000 , I can export data from excel to SQL database. But do I
> have
> to do this process everytime a spreadsheet comes in ? or this can be coded
> somewhere so that the process is automatically taken care of?
> Is there any walkthrough or aticle which I can read.. Thanks..
> "Michael Rys [MSFT]" wrote:
>|||Hi Michael,
Leaving the scripting apart, how can I export data from excel to XML only'
Is there some walkthrough or article that shows this?
Thanks a lot.
"Michael Rys [MSFT]" wrote:

> I would assume that you could use scripting to do it, but I am not a
> scripting expert and I think checking with the Excel experts would be a go
od
> idea.
> Best regards
> Michael
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:908D5EA2-E452-4165-8B35-43FFBC1EEA23@.microsoft.com...
>
>|||See under save as... There should be some XML options (at least in Excel
2003).
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:947DAF2A-D2EB-4D7A-989E-E4EBABF0ED33@.microsoft.com...
> Hi Michael,
> Leaving the scripting apart, how can I export data from excel to XML
> only'
> Is there some walkthrough or article that shows this?
> Thanks a lot.
> "Michael Rys [MSFT]" wrote:
>

Exporting data from Excel to SQL DATABASE

Hi,
Is it possible to export data from Excel spreadsheet to different tables in
SQL SERVER DATABASE using XML? & how? I have Excel 2003 on my computer. I
need to do this every time the user , say clicks a button on the Excel sheet.
Thanks for any help.
pmud
You probably want to ask this in the Office/Excel newsgroup regarding
specific functionality in Excel. You can obviously export the XML and then
using any of the XML import functionality of SQL Server 2000 such as the
SQLXML XML Bulkload object or OpenXML in T-SQL...
Best regards
Michael
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:231C0050-33FD-4487-B7BD-24FC870E86B1@.microsoft.com...
> Hi,
> Is it possible to export data from Excel spreadsheet to different tables
> in
> SQL SERVER DATABASE using XML? & how? I have Excel 2003 on my computer. I
> need to do this every time the user , say clicks a button on the Excel
> sheet.
> Thanks for any help.
> --
> pmud
|||Hi Michael,
As you said that I can export XML & then using XML import functionality fo
sql server 2000 , I can export data from excel to SQL database. But do I have
to do this process everytime a spreadsheet comes in ? or this can be coded
somewhere so that the process is automatically taken care of?
Is there any walkthrough or aticle which I can read.. Thanks..
"Michael Rys [MSFT]" wrote:

> You probably want to ask this in the Office/Excel newsgroup regarding
> specific functionality in Excel. You can obviously export the XML and then
> using any of the XML import functionality of SQL Server 2000 such as the
> SQLXML XML Bulkload object or OpenXML in T-SQL...
> Best regards
> Michael
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:231C0050-33FD-4487-B7BD-24FC870E86B1@.microsoft.com...
>
>
|||I would assume that you could use scripting to do it, but I am not a
scripting expert and I think checking with the Excel experts would be a good
idea.
Best regards
Michael
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:908D5EA2-E452-4165-8B35-43FFBC1EEA23@.microsoft.com...[vbcol=seagreen]
> Hi Michael,
> As you said that I can export XML & then using XML import functionality fo
> sql server 2000 , I can export data from excel to SQL database. But do I
> have
> to do this process everytime a spreadsheet comes in ? or this can be coded
> somewhere so that the process is automatically taken care of?
> Is there any walkthrough or aticle which I can read.. Thanks..
> "Michael Rys [MSFT]" wrote:
|||Hi Michael,
Leaving the scripting apart, how can I export data from excel to XML only?
Is there some walkthrough or article that shows this?
Thanks a lot.
"Michael Rys [MSFT]" wrote:

> I would assume that you could use scripting to do it, but I am not a
> scripting expert and I think checking with the Excel experts would be a good
> idea.
> Best regards
> Michael
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:908D5EA2-E452-4165-8B35-43FFBC1EEA23@.microsoft.com...
>
>
|||See under save as... There should be some XML options (at least in Excel
2003).
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:947DAF2A-D2EB-4D7A-989E-E4EBABF0ED33@.microsoft.com...[vbcol=seagreen]
> Hi Michael,
> Leaving the scripting apart, how can I export data from excel to XML
> only?
> Is there some walkthrough or article that shows this?
> Thanks a lot.
> "Michael Rys [MSFT]" wrote:

Exporting data from Excel Tables to SQL Server Tables

Hi all,

I have a large Excel file with one large table which contains data, i've built a SQL Server DataBase and i want to fill it with the data from the excel file.

How can it be done?

Thanks, Michael.

If you are using SQL 2000 Standard or better, use DTS.

If you are using SQL2005/8 Standard or better, use SSIS.

If you are using SQL 2005/8 Express, get the Graphical User Interface for it and try that out.

p.s. What version of SQL Server are using?

Exporting data and keep the primary key

Hi all,

I am rather fresh to SQL2000, so...

When I export data from database A tot database B and I look at the copied tables in database B, all the primary keys are gone and the field is now a "normal" field ?

How can I keep the primary key in the copied tables ?

Help is appreciated, Ger.

I have the same problem. Is there an answer to this?|||How did you export ? If you make a SQL copy objects task via DTS you can select if you also want to preserve the PK / index structure etc. Its one of the advanced options of the DTS Wizard.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

I am using Sql Server 2005 Management Studio, I run the DTSWizard by running DTSWizard.exe from the "Run" menu. But I can not copy my database with the primary keys and relations.

After reading your post I have tried to find the advanced options of DTS Wizard but couldnt managed to do that. If you can give detailed information I will be glad.

Thanks in advance

Exporting data and keep the primary key

Hi all,

I am rather fresh to SQL2000, so...

When I export data from database A tot database B and I look at the copied tables in database B, all the primary keys are gone and the field is now a "normal" field ?

How can I keep the primary key in the copied tables ?

Help is appreciated, Ger.

I have the same problem. Is there an answer to this?|||How did you export ? If you make a SQL copy objects task via DTS you can select if you also want to preserve the PK / index structure etc. Its one of the advanced options of the DTS Wizard.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

I am using Sql Server 2005 Management Studio, I run the DTSWizard by running DTSWizard.exe from the "Run" menu. But I can not copy my database with the primary keys and relations.

After reading your post I have tried to find the advanced options of DTS Wizard but couldnt managed to do that. If you can give detailed information I will be glad.

Thanks in advance

sql

Exporting BIT datatypes?

Hi all. Im tryin to export (DTS) my some SQL server tables, many of which contain 'bit' datatypes. However, when DTS/SQL Serv. moves these bit datatypes out, it changes bit values to True/False values - which makes sense - however these are all going to plugin to web frontends where the SQL specifies condtions like: "if column1 = 0 then" etc..
Is there anyway to get SQL server to export bit datatypes as just numeric values of 0/1?I tried it and I'm getting the same thing..

You could do a view and dts that out

SELECT CASE WHEN Col1 = 1 THEN '1' WHEN Col1 = 0 THEN '0' ELSE NULL END|||select cast(colBit as int) as colInt|||I tried it and I'm getting the same thing..

You could do a view and dts that out

SELECT CASE WHEN Col1 = 1 THEN '1' WHEN Col1 = 0 THEN '0' ELSE NULL END

That is a darn good idea - but I dont have the privs. to make a view :-/
Edit: this is all going straight into oracle.|||So what format is the output in?|||So what format is the output in?

If I let DTS handle the datatype - it gives it the datatype NUMBER and the value 0 or -1.

If I manually change the columns datatype to char, I get T or F.
edit:this is all going directly into oracle.|||You know you can just type the sql in to the source in DTS

How much data are we talking about...|||You know you can just type the sql in to the source in DTS

How much data are we talking about...

This happens in a few tables...We're talking over 10,000 rows.|||Does Oracle even have a bit datatype?

What version we talking about?|||it most certainly does not. 9i.
if i could use number, and it didn't automatically turn "1" into "-1" this wouldn't be a big deal but alas...|||How about the ABS function? Would that work?|||How about the ABS function? Would that work?

hmmm yes, that'd work on the front end for the conditonal statements but it'd still require me to change that all over the application unless there was some way to run that during DTS...(which there may be? I'm not too familiar with SQL server or DTS)

Exporting binary data

We're using MS-SQL Sever for a documentation database. One of the tables
stores all the DOC, XLS and PPT documents fed by our users.
We need to export all the files to certain directory and the rest of the
table contents to a spreadsheet. We want to distribute the documents in a
promotional CD.
The data export to a CSV works without a problem but we don't know how to
export the binary data of the files that are stored in the table. How can
this be done?
Thanks in advance,
JTJaime Torres wrote:
> We're using MS-SQL Sever for a documentation database. One of the
> tables stores all the DOC, XLS and PPT documents fed by our users.
> We need to export all the files to certain directory and the rest of
> the table contents to a spreadsheet. We want to distribute the
> documents in a promotional CD.
> The data export to a CSV works without a problem but we don't know
> how to export the binary data of the files that are stored in the
> table. How can this be done?
> Thanks in advance,
> JT
Please try not to multi-post. See ".Clients" for the current active
thread.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

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
>

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

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
>

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
>

Exporting & Importing certain tables

We have a database which services a number of websites. We want to move
individual tables for each website from the exisiting server to the new SQL
2005 Cluster as we modify the websites to point to the new location.
Is this possible or do I need to restore the whole database?
Thanks,
Neil Paddock.SSSIS/DTS, BCP, Import/Export Wizard, BULKCOPY will all allow you to easily
move individual tables from one database/server to another. See BOL for
details.
TheSQLGuru
President
Indicium Resources, Inc.
"Neil Paddock" <NeilPaddock@.discussions.microsoft.com> wrote in message
news:AA18F359-6356-48EF-B458-32060A0552BE@.microsoft.com...
> We have a database which services a number of websites. We want to move
> individual tables for each website from the exisiting server to the new
> SQL
> 2005 Cluster as we modify the websites to point to the new location.
> Is this possible or do I need to restore the whole database?
> Thanks,
> Neil Paddock.
>|||Thanks for the info.
I have attempted to copy a table from one database to another using
Import/Export Wizard but I'm receiving an error message:
Validating (Error)
Messages
Error 0xc0202049: Data Flow Task: Failure inserting into the read-only
column "Employee_id".
(SQL Server Import and Export Wizard)
Error 0xc0202045: Data Flow Task: Column metadata validation failed.
(SQL Server Import and Export Wizard)
Error 0xc004706b: Data Flow Task: "component "Destination -
employee_details" (130)" failed validation and returned validation status
"VS_ISBROKEN".
(SQL Server Import and Export Wizard)
Error 0xc004700c: Data Flow Task: One or more component failed validation.
(SQL Server Import and Export Wizard)
Error 0xc0024107: Data Flow Task: There were errors during task validation.
(SQL Server Import and Export Wizard)
It seems to be a validation error to do with the destination being read
only? How do I change this?
Thanks,
Neil Paddock.
"TheSQLGuru" wrote:

> SSSIS/DTS, BCP, Import/Export Wizard, BULKCOPY will all allow you to easil
y
> move individual tables from one database/server to another. See BOL for
> details.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Neil Paddock" <NeilPaddock@.discussions.microsoft.com> wrote in message
> news:AA18F359-6356-48EF-B458-32060A0552BE@.microsoft.com...
>
>sql

Exporting & Importing certain tables

We have a database which services a number of websites. We want to move
individual tables for each website from the exisiting server to the new SQL
2005 Cluster as we modify the websites to point to the new location.
Is this possible or do I need to restore the whole database?
Thanks,
Neil Paddock.
SSSIS/DTS, BCP, Import/Export Wizard, BULKCOPY will all allow you to easily
move individual tables from one database/server to another. See BOL for
details.
TheSQLGuru
President
Indicium Resources, Inc.
"Neil Paddock" <NeilPaddock@.discussions.microsoft.com> wrote in message
news:AA18F359-6356-48EF-B458-32060A0552BE@.microsoft.com...
> We have a database which services a number of websites. We want to move
> individual tables for each website from the exisiting server to the new
> SQL
> 2005 Cluster as we modify the websites to point to the new location.
> Is this possible or do I need to restore the whole database?
> Thanks,
> Neil Paddock.
>
|||Thanks for the info.
I have attempted to copy a table from one database to another using
Import/Export Wizard but I'm receiving an error message:
Validating (Error)
Messages
Error 0xc0202049: Data Flow Task: Failure inserting into the read-only
column "Employee_id".
(SQL Server Import and Export Wizard)
Error 0xc0202045: Data Flow Task: Column metadata validation failed.
(SQL Server Import and Export Wizard)
Error 0xc004706b: Data Flow Task: "component "Destination -
employee_details" (130)" failed validation and returned validation status
"VS_ISBROKEN".
(SQL Server Import and Export Wizard)
Error 0xc004700c: Data Flow Task: One or more component failed validation.
(SQL Server Import and Export Wizard)
Error 0xc0024107: Data Flow Task: There were errors during task validation.
(SQL Server Import and Export Wizard)
It seems to be a validation error to do with the destination being read
only? How do I change this?
Thanks,
Neil Paddock.
"TheSQLGuru" wrote:

> SSSIS/DTS, BCP, Import/Export Wizard, BULKCOPY will all allow you to easily
> move individual tables from one database/server to another. See BOL for
> details.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Neil Paddock" <NeilPaddock@.discussions.microsoft.com> wrote in message
> news:AA18F359-6356-48EF-B458-32060A0552BE@.microsoft.com...
>
>

Exporting & Importing certain tables

We have a database which services a number of websites. We want to move
individual tables for each website from the exisiting server to the new SQL
2005 Cluster as we modify the websites to point to the new location.
Is this possible or do I need to restore the whole database?
Thanks,
Neil Paddock.SSSIS/DTS, BCP, Import/Export Wizard, BULKCOPY will all allow you to easily
move individual tables from one database/server to another. See BOL for
details.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Neil Paddock" <NeilPaddock@.discussions.microsoft.com> wrote in message
news:AA18F359-6356-48EF-B458-32060A0552BE@.microsoft.com...
> We have a database which services a number of websites. We want to move
> individual tables for each website from the exisiting server to the new
> SQL
> 2005 Cluster as we modify the websites to point to the new location.
> Is this possible or do I need to restore the whole database?
> Thanks,
> Neil Paddock.
>|||Thanks for the info.
I have attempted to copy a table from one database to another using
Import/Export Wizard but I'm receiving an error message:
Validating (Error)
Messages
Error 0xc0202049: Data Flow Task: Failure inserting into the read-only
column "Employee_id".
(SQL Server Import and Export Wizard)
Error 0xc0202045: Data Flow Task: Column metadata validation failed.
(SQL Server Import and Export Wizard)
Error 0xc004706b: Data Flow Task: "component "Destination -
employee_details" (130)" failed validation and returned validation status
"VS_ISBROKEN".
(SQL Server Import and Export Wizard)
Error 0xc004700c: Data Flow Task: One or more component failed validation.
(SQL Server Import and Export Wizard)
Error 0xc0024107: Data Flow Task: There were errors during task validation.
(SQL Server Import and Export Wizard)
It seems to be a validation error to do with the destination being read
only? How do I change this?
Thanks,
Neil Paddock.
"TheSQLGuru" wrote:
> SSSIS/DTS, BCP, Import/Export Wizard, BULKCOPY will all allow you to easily
> move individual tables from one database/server to another. See BOL for
> details.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Neil Paddock" <NeilPaddock@.discussions.microsoft.com> wrote in message
> news:AA18F359-6356-48EF-B458-32060A0552BE@.microsoft.com...
> > We have a database which services a number of websites. We want to move
> > individual tables for each website from the exisiting server to the new
> > SQL
> > 2005 Cluster as we modify the websites to point to the new location.
> >
> > Is this possible or do I need to restore the whole database?
> >
> > Thanks,
> > Neil Paddock.
> >
> >
>
>

Export/Import tables from MSQL from/to .xml files

When I try to export a sql table to a .xml file I get the following error
when I use either sqlxmloledb or sqlxmloledb.3.0 as the destination:
sqloledb must be specified as the data provider.
However, sqloledb does not appear in the list of ole db providers. Does
anyone know how I can register sqloledb as a data provider please
I also have a problem using sql query analyzer to access a .xml file. All of
the examples in the help files show encoded xml data rather than accessing a
.xml data file on disk. Does anyone know how to link in the .xml file to sq
l
script please.
Regards,
Alan de Wetsqloledb should already be part of the OS. If you install MDAC / SqlClient
Tools / VS, SQLOLEDB should be installed.
Check C:\Program Files\Common Files\System\Oledb\sqloledb.dll for
verification. If the dll is there and is registered, then SQLOLEDB should be
there.
To use SQLOLEDB as the provider in the connection string, append
Provider=SQLOLEDB to the connection string.
thanks
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Alan de Wet" <AlandeWet@.discussions.microsoft.com> wrote in message
news:B79E3526-4685-44FB-8CA1-94B0E4D97F90@.microsoft.com...
> When I try to export a sql table to a .xml file I get the following error
> when I use either sqlxmloledb or sqlxmloledb.3.0 as the destination:
> sqloledb must be specified as the data provider.
> However, sqloledb does not appear in the list of ole db providers. Does
> anyone know how I can register sqloledb as a data provider please
> I also have a problem using sql query analyzer to access a .xml file. All
> of
> the examples in the help files show encoded xml data rather than accessing
> a
> .xml data file on disk. Does anyone know how to link in the .xml file to
> sql
> script please.
> Regards,
> Alan de Wet|||Thanks for your reply Chandra. SQLOLEDB does not appear in the list of ole d
b
providers when I try to do an export from the Import and Export tool in MSQL
and am entering the Destination details. I am not trying to do the export
from a VB application so I do not have a connection string. SQLOLEDB is in
C:\Program Files\Common Files\System\Ole DB (being Ole DB and not Oledb). Ho
w
do I check if it is registered please?
Regards,
Alan
"Chandra Kalyanaraman [MSFT]" wrote:

> sqloledb should already be part of the OS. If you install MDAC / SqlClient
> Tools / VS, SQLOLEDB should be installed.
> Check C:\Program Files\Common Files\System\Oledb\sqloledb.dll for
> verification. If the dll is there and is registered, then SQLOLEDB should
be
> there.
> To use SQLOLEDB as the provider in the connection string, append
> Provider=SQLOLEDB to the connection string.
>
> thanks
> Chandra
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Alan de Wet" <AlandeWet@.discussions.microsoft.com> wrote in message
> news:B79E3526-4685-44FB-8CA1-94B0E4D97F90@.microsoft.com...
>
>|||Is it true that SQLXMLOLEDB provider supports data export via DTS
Import/Export Wizard?
Best regards
Martin Rakhmanov
"Alan de Wet" wrote:
> Thanks for your reply Chandra. SQLOLEDB does not appear in the list of ole
db
> providers when I try to do an export from the Import and Export tool in MS
QL
> and am entering the Destination details. I am not trying to do the export
> from a VB application so I do not have a connection string. SQLOLEDB is in
> C:\Program Files\Common Files\System\Ole DB (being Ole DB and not Oledb).
How
> do I check if it is registered please?
> Regards,
> Alan
> "Chandra Kalyanaraman [MSFT]" wrote:
>|||After I installed sqlxml 2 new Data Providers (SQLXMLOLEDB and
SQLXMLOLEDB.3.0) appear in the list when I select my destination. I have
tried both of them. When I select either one I get a message saying: To
connect click properties and enter the required information. When I click on
properties I do not see sqloledb as a ole db provider but if I select any
other provider, click OK and then click next I get a message saying that
SQLOLEDB must be specified as the data provider. If I select either
sqxmloledb or sqlxmloledb.3.0 as the ole db provider under properties I am
then asked to enter Data Source, Location and Server Log On Information whic
h
does not make sense as I am trying to export to a .xml file.
So to answer your question, maybe it was supposed to but it does not appear
to support data export via DTS Import/Export Wizard.
Best Regards,
Alan de Wet
"jimmers" wrote:
> Is it true that SQLXMLOLEDB provider supports data export via DTS
> Import/Export Wizard?
> Best regards
> Martin Rakhmanov
>
> "Alan de Wet" wrote:
>

Export/Import tables from MSQL from/to .xml files

When I try to export a sql table to a .xml file I get the following error
when I use either sqlxmloledb or sqlxmloledb.3.0 as the destination:
sqloledb must be specified as the data provider.
However, sqloledb does not appear in the list of ole db providers. Does
anyone know how I can register sqloledb as a data provider please
I also have a problem using sql query analyzer to access a .xml file. All of
the examples in the help files show encoded xml data rather than accessing a
..xml data file on disk. Does anyone know how to link in the .xml file to sql
script please.
Regards,
Alan de Wet
sqloledb should already be part of the OS. If you install MDAC / SqlClient
Tools / VS, SQLOLEDB should be installed.
Check C:\Program Files\Common Files\System\Oledb\sqloledb.dll for
verification. If the dll is there and is registered, then SQLOLEDB should be
there.
To use SQLOLEDB as the provider in the connection string, append
Provider=SQLOLEDB to the connection string.
thanks
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Alan de Wet" <AlandeWet@.discussions.microsoft.com> wrote in message
news:B79E3526-4685-44FB-8CA1-94B0E4D97F90@.microsoft.com...
> When I try to export a sql table to a .xml file I get the following error
> when I use either sqlxmloledb or sqlxmloledb.3.0 as the destination:
> sqloledb must be specified as the data provider.
> However, sqloledb does not appear in the list of ole db providers. Does
> anyone know how I can register sqloledb as a data provider please
> I also have a problem using sql query analyzer to access a .xml file. All
> of
> the examples in the help files show encoded xml data rather than accessing
> a
> .xml data file on disk. Does anyone know how to link in the .xml file to
> sql
> script please.
> Regards,
> Alan de Wet
|||Thanks for your reply Chandra. SQLOLEDB does not appear in the list of ole db
providers when I try to do an export from the Import and Export tool in MSQL
and am entering the Destination details. I am not trying to do the export
from a VB application so I do not have a connection string. SQLOLEDB is in
C:\Program Files\Common Files\System\Ole DB (being Ole DB and not Oledb). How
do I check if it is registered please?
Regards,
Alan
"Chandra Kalyanaraman [MSFT]" wrote:

> sqloledb should already be part of the OS. If you install MDAC / SqlClient
> Tools / VS, SQLOLEDB should be installed.
> Check C:\Program Files\Common Files\System\Oledb\sqloledb.dll for
> verification. If the dll is there and is registered, then SQLOLEDB should be
> there.
> To use SQLOLEDB as the provider in the connection string, append
> Provider=SQLOLEDB to the connection string.
>
> thanks
> Chandra
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Alan de Wet" <AlandeWet@.discussions.microsoft.com> wrote in message
> news:B79E3526-4685-44FB-8CA1-94B0E4D97F90@.microsoft.com...
>
>
|||Is it true that SQLXMLOLEDB provider supports data export via DTS
Import/Export Wizard?
Best regards
Martin Rakhmanov
"Alan de Wet" wrote:
[vbcol=seagreen]
> Thanks for your reply Chandra. SQLOLEDB does not appear in the list of ole db
> providers when I try to do an export from the Import and Export tool in MSQL
> and am entering the Destination details. I am not trying to do the export
> from a VB application so I do not have a connection string. SQLOLEDB is in
> C:\Program Files\Common Files\System\Ole DB (being Ole DB and not Oledb). How
> do I check if it is registered please?
> Regards,
> Alan
> "Chandra Kalyanaraman [MSFT]" wrote:
|||After I installed sqlxml 2 new Data Providers (SQLXMLOLEDB and
SQLXMLOLEDB.3.0) appear in the list when I select my destination. I have
tried both of them. When I select either one I get a message saying: To
connect click properties and enter the required information. When I click on
properties I do not see sqloledb as a ole db provider but if I select any
other provider, click OK and then click next I get a message saying that
SQLOLEDB must be specified as the data provider. If I select either
sqxmloledb or sqlxmloledb.3.0 as the ole db provider under properties I am
then asked to enter Data Source, Location and Server Log On Information which
does not make sense as I am trying to export to a .xml file.
So to answer your question, maybe it was supposed to but it does not appear
to support data export via DTS Import/Export Wizard.
Best Regards,
Alan de Wet
"jimmers" wrote:
[vbcol=seagreen]
> Is it true that SQLXMLOLEDB provider supports data export via DTS
> Import/Export Wizard?
> Best regards
> Martin Rakhmanov
>
> "Alan de Wet" wrote:

Export/Import data with XML - BLOB's, how to?

I need export data from few master->detail tables and then import this data
into other database with the same structure.
I.e. export some my "object" and import.
The question is: how can I export and import tables which have ntext, image
columns?
Thank you.
Did you look at FOR XML SELECT statements for exporting and either the
client-side SQLXML Bulkload object or the server-side OpenXML rowset
provider for importing?
Best regards
Michael
"Oleg Cherkasenko" <oleg@.opel.com.ua> wrote in message
news:%235nORZ5NFHA.2604@.TK2MSFTNGP10.phx.gbl...
>I need export data from few master->detail tables and then import this data
>into other database with the same structure.
> I.e. export some my "object" and import.
> The question is: how can I export and import tables which have ntext,
> image columns?
> Thank you.
>