Export data from Multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Export data from Multiple tables

Author

Topic

AKP2008
Starting Member

45 Posts

Posted - 2008-11-24 : 08:31:57

Hi,

How can i Export data from Multiple tables into a Single text file.

Please help.

Thanks in Advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 08:48:16

use bcp or ssis export import task.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 08:55:29

refer below links also

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/bcp-export-data-to-text-file.aspx

http://msdn.microsoft.com/en-us/library/ms141209(SQL.90).aspx

AKP2008
Starting Member

45 Posts

Posted - 2008-11-24 : 08:57:38

quote:
Originally posted by visakh16

use bcp or ssis export import task.

Hi,

I have to create package.

Using import export wizard how can i select the multiple tables.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 09:00:18

refer to link posted before. upon seleting a db, export import wizard lists all tables available in it. you can select which all tables you want by clicking on checkbox against each.

AKP2008
Starting Member

45 Posts

Posted - 2008-11-24 : 09:15:37

quote:
Originally posted by visakh16

refer to link posted before. upon seleting a db, export import wizard lists all tables available in it. you can select which all tables you want by clicking on checkbox against each.

here i am not able to get the checkboxes.

I am getting tablenames in Dropdown. That's why i am able to select only one table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 09:18:36

nope you will get all tables in source dbs listed out with check box on left. only against each table destination table column you've dropdown for selecting correct destination table.

AKP2008
Starting Member

45 Posts

Posted - 2008-11-25 : 01:39:56

quote:
Originally posted by visakh16

nope you will get all tables in source dbs listed out with check box on left. only against each table destination table column you've dropdown for selecting correct destination table.

I am getting tables in dropdown only.

Please suggest me another solution for this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 01:41:42

how are you starting the export import wizard?

AKP2008
Starting Member

45 Posts

Posted - 2008-11-25 : 01:45:49

quote:
Originally posted by visakh16

how are you starting the export import wizard?

Right click on the Database name.
Select Tasks -> ExportData.

I am selecting source as SQLnative client.
Destination is Flatfile source.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 02:09:10

quote:
Originally posted by AKP2008

quote:
Originally posted by visakh16

how are you starting the export import wizard?

Right click on the Database name.
Select Tasks -> ExportData.

I am selecting source as SQLnative client.
Destination is Flatfile source.

Ok after that are you selecting
Copy data from one or more tables/view
or
Write a query to specify data transfer

you should be selecting the former option.

AKP2008
Starting Member

45 Posts

Posted - 2008-11-25 : 02:39:06

quote:
Originally posted by visakh16

quote:
Originally posted by AKP2008

quote:
Originally posted by visakh16

how are you starting the export import wizard?

Right click on the Database name.
Select Tasks -> ExportData.

I am selecting source as SQLnative client.
Destination is Flatfile source.

Ok after that are you selecting
Copy data from one or more tables/view
or
Write a query to specify data transfer

you should be selecting the former option.

After that I am selecting "Copy data from one or more tables/view"
Click Next i am able to see the tablenames in dropdown.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 03:16:32

are you talking about destination tables or source tables?Is your requirement exporting data to multiple tables from single source?

AKP2008
Starting Member

45 Posts

Posted - 2008-11-25 : 03:57:23

quote:
Originally posted by visakh16

are you talking about destination tables or source tables?Is your requirement exporting data to multiple tables from single source?

No. My requirement is to export data from multiple tables into a single text file.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 04:15:46

In that case choose flat file destination and query option and write a query joining the tables like


SELECT t1.field1,t1.field2,t2.field1...
FROM table1 t1
JOIN table2 t2
ON fields....

AKP2008
Starting Member

45 Posts

Posted - 2008-11-25 : 08:20:19

quote:
Originally posted by visakh16

In that case choose flat file destination and query option and write a query joining the tables like

SELECT t1.field1,t1.field2,t2.field1...
FROM table1 t1
JOIN table2 t2
ON fields....

My requirement is

I am selecting multiple tables like "Table1, Table2, Table3..."
and destination as text file.

When i execute the package all tables data is exported into a Textfile specified in destination.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 11:57:15

single text file or multiple? if single, cant you just use a query like i specified as before?do you want fields from tables as seperate rows or columns?

AKP2008
Starting Member

45 Posts

Posted - 2008-11-26 : 00:31:48

quote:
Originally posted by visakh16

single text file or multiple? if single, cant you just use a query like i specified as before?do you want fields from tables as seperate rows or columns?

I want resultset in single text file only.

Like this

Table1 Data
Table2 Data
Table3 Data
.
.
.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 02:04:13

then use

select fields from table1
UNION ALl
Select fields... from table2
UNION ALL
select fields from table3

AKP2008
Starting Member

45 Posts

Posted - 2008-11-26 : 02:17:08

quote:
Originally posted by visakh16

then use
select fields from table1
UNION ALl
Select fields... from table2
UNION ALL
select fields from table3

worked great, thank you.

I have one doubt if i want to export data from two tables. But Table1 contains 15 columns and Table2 contains 20 columns. I want to export all colummns data.

But union operator must have an equal number of expressions in their target lists. How can i do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 03:40:52

[code]
select field1,..field15,
cast(null as datatype1),
cast(null as datatype2),
cast(null as datatype3),
cast(null as datatype4),
cast(null as datatype5)
from table1
UNION ALL
SELECT *
FROM Table2

[/code]
the datatypes1...5 corresponds to those of extra 5 columns in table2

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources