[sql] Transfer data from one database to another database

How to fetch the data from one database and insert in to another database table? I can't to do this. Please help me in transferring data from one to another.

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.

This can be achieved by a T-SQL statement, if you are aware that FROM clause can specify database for table name.

insert into database1..MyTable
select from database2..MyTable

Here is how Microsoft explains the syntax: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-ver15

If the table or view exists in another database on the same instance of SQL Server, use a fully qualified name in the form database.schema.object_name.

schema_name can be omitted, like above, which means the default schema of the current user. By default, it's dbo.

Add any filtering to columns/rows if you want to. Be sure to create any new table before moving data.

1. You can backup and restore the database using Management Studio.
2. Again from Management Studio you can use "copy database".
3. you can even do it manually if there is a reason to do so. I mean manually create the target db and manually copying data by sql statements...

can you clarify why you ask this? Is it that you dont have expierience in doing it or something else?

Example for insert into values in One database table into another database table

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

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

You can use visual studio 2015. Go to Tools => SQL server => New Data comparison

Select source and target Database.

These solutions are working in case when target database is blank. In case when both databases already have some data you need something more complicated http://byalexblog.net/merge-sql-databases

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.

There are multiple options and depend on your needs. See the following links:

1. Copying Data Between Servers
2. Copy tables from one database to another in SQL Server.