Mssql 2005 table transfer - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Mssql 2005 table transfer

Author

Topic

edward
Starting Member

8 Posts

Posted - 2008-06-23 : 04:53:43

Hello,

In MSSQL2000, I am able to transfer files from one db to other.
using addTask -> export data.

In MSSQL2005, I am not able to transfer files from one db to other.

Please advise.

Thanking You.
Regards,
Edward

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 05:05:51

Why? You still have the export data task. Go to SQL server management studio. Select db,right click select Tasks->Export data. then follow instruction to select source and destination db and tables to export data.
b/w if the two dbs are in same server. you can just use

INSERT Destndb..Table(fields....)
SELECT fields
FROM Sourcedb..table

edward
Starting Member

8 Posts

Posted - 2008-06-23 : 05:31:30

Consider I have 20 tables in DB1. 5 tables contains fields with huge data.

I have created Database DB2. Already existing 20 tables are there. but without data. So I have tried to select DB1 5 tables to DB2 5 tables.

Hope you understand.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 05:36:41

quote:
Originally posted by edward

Consider I have 20 tables in DB1. 5 tables contains fields with huge data.

I have created Database DB2. Already existing 20 tables are there. but without data. So I have tried to select DB1 5 tables to DB2 5 tables.

Hope you understand.

You can do it using Export data task in management studio. Select the 5 tables and corresponding 5 destination tables and it will do transfer for you.

edward
Starting Member

8 Posts

Posted - 2008-06-23 : 06:02:44

In Mssql server 2005 Management studio express,

1) I am not able to select multiple tables and
2) When I right click the table, I don't have export data option facility is there.

my mssql2005 info is below

Microsoft SQL Server Management Studio Express 9.00.2047.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

3)Could you tell me, anything I want to install here
4)From mssql2000, that facility is available.

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 06:23:46

You have to right click the DATABASE, not the table.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 06:36:06

See this too. ALso remember to use table as destination instead of excel as shown here

http://www.databasejournal.com/features/mssql/article.php/3580216

edward
Starting Member

8 Posts

Posted - 2008-06-23 : 07:56:18

Even when I right click the DB(not tables), I don't have import/export task facility.

I don't have "Data Transformation Services (DTS) tool" .
Could you give me the exact link, i have to download?

Please advise.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 08:00:27

quote:
Originally posted by edward

Even when I right click the DB(not tables), I don't have import/export task facility.

I don't have "Data Transformation Services (DTS) tool" .
Could you give me the exact link, i have to download?

Please advise.

the option is available in SQL 2005 management studio itself. are you sure you're using SQL 2005?

edward
Starting Member

8 Posts

Posted - 2008-06-23 : 08:08:54

Yes. I am using MSSQL 2005.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 08:20:08

quote:
Originally posted by edward

Yes. I am using MSSQL 2005.

Did you had a look at the link i gave before and tried spotting the option in SQL mgmnt studio?

http://www.databasejournal.com/features/mssql/article.php/3580216

edward
Starting Member

8 Posts

Posted - 2008-06-24 : 01:10:25

Hello Sir,

But I don't have Project Menu in my MSSQL2005 Management Studio.

Please advise.

thanks and regards,
edward

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-24 : 01:35:39

quote:
Originally posted by edward

Hello Sir,

But I don't have Project Menu in my MSSQL2005 Management Studio.

Please advise.

thanks and regards,
edward

look at screenshot under this paragraph in article

If you want to start the Import/Export wizard from the SQL Server Management Studio then connect to a Database Engine server type. Once you are connected right click on one of your user databases, select the "Tasks" item, then either click on the "Import Data..." or "Export Data..." menu item. Below is a screen shot of where to locate the "Import..." or "Export Data..." tasks:

edward
Starting Member

8 Posts

Posted - 2008-06-24 : 03:27:49

after connection, i rightclick the one db, in tasks menu contains only

1. detach
2. shrink
3. backup
4. restore
5. generate scripts

but i don't have import/export.

Please advise.
thanks and regards,
edward

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-24 : 03:38:19

quote:
Originally posted by edward

after connection, i rightclick the one db, in tasks menu contains only

1. detach
2. shrink
3. backup
4. restore
5. generate scripts

but i don't have import/export.

Please advise.
thanks and regards,
edward

Are you using SQL Server Express?

edward
Starting Member

8 Posts

Posted - 2008-06-24 : 04:08:49

I am using SQL Server Managment Studio Express 2005

Microsoft SQL Server Management Studio Express 9.00.2047.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-24 : 04:25:07

quote:
Originally posted by edward

I am using SQL Server Managment Studio Express 2005

Microsoft SQL Server Management Studio Express 9.00.2047.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

i think then you need to install DTs wizard from toolkit

http://mobiledeveloper.wordpress.com/2007/01/31/data-import-export-with-sql-server-express-using-dts-wizard/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources