How to pull data from one database to another database using sqlsqerver 2005

Question

How to pull data from one database to another database by using sqlsqerver 2005

like ..Exporting data from one and importing to the another.

Answer 1

Take your pick.

1. Linked Server/databases.
2. SSIS
3. Exporting data from one and importing to the another.

Answer 2

In addition to no-one's answers, if the DB's are both SQL Server

4) Select * into New_Table From Server.Database.Owner.Table

Answer 3

Based on your comments I understand that both databases are on the same server You can simply do:

use YourDestinationDatabaseName;

select * into dbo.yourNewTable from YourSourceDatabase.dbo.YourSourceTable

and if you want to insert into table that already exists

use YourDestinationDatabaseName;

insert into dbo.YourDestinationTable 
select * from YourSourceDatabase.dbo.YourSourceTable

or more precisely:

use YourDestinationDatabaseName;

insert into dbo.YourDestinationTable( col1, col2, etc...) 
select 
    col1a, col2a, etc ... 
from 
    YourSourceDatabase.dbo.YourSourceTable

Answer 4

If you have VS 2005, I like this tool, because it generates t-sql: http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en