Transfer data from one database to another database

if both databases are on same server and you want to transfer entire table (make copy of it) then use simple select into statement ...

select * into anotherDatabase..copyOfTable from oneDatabase..tableName

You can then write cursor top of sysobjects and copy entire set of tables that way.

If you want more complex data extraction & transformation, then use SSIS and build appropriate ETL in it.

For those on Azure, follow these modified instructions from Virus:

1. Open SSMS.
2. Right-click the Database you wish to copy data from.
3. Select Generate Scripts >> Select Specific Database Objects >> Choose the tables/object you wish to transfer. strong text
4. In the "Save to file" pane, click Advanced
5. Set "Types of data to script" to Schema and data
6. Set "Script DROP and CREATE" to Script DROP and CREATE
7. Under "Table/View Options" set relevant items to TRUE. Though I recommend setting all to TRUE just in case. You can always modify the script after it generates.
8. Set filepath >> Next >> Next
9. Open newly created SQL file. Remove "Use" from top of file.
10. Open new query window on destination database, paste script contents (without using) and execute.

Example for insert into values in One database table into another database table running on the same SQL Server

insert into dbo.onedatabase.FolderStatus
(
  [FolderStatusId],
  [code],
  [title],
  [last_modified]
)

select [FolderStatusId], [code], [title], [last_modified]
from dbo.Twodatabase.f_file_stat

There are several ways to do this, below are two options:

Option 1 - Right click on the database you want to copy

• Choose 'Tasks' > 'Generate scripts'

• 'Select specific database objects'

• Check 'Tables'

• Mark 'Save to new query window'

• Click 'Advanced'

• Set 'Types of data to script' to 'Schema and data'

• Next, Next

You can now run the generated query on the new database.

Option 2

• Right click on the database you want to copy

• 'Tasks' > 'Export Data'

• Next, Next

• Choose the database to copy the tables to

• Mark 'Copy data from one or more tables or views'

• Choose the tables you want to copy

• Finish