CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Bharathi
Bharathi

Reputation: 1545

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.

Upvotes: 109

Views: 467013

Answers (10)

Virus
Virus

Reputation: 3425

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

Option 1 - Script the data to move, then run the script where you want the data:

  • 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 - Export the data you want to move, then import it where you want it:

  • 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

Upvotes: 162

Lokanathan
Lokanathan

Reputation: 201

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

Upvotes: 20

pianocomposer
pianocomposer

Reputation: 128

Doing this programmatically between two different databases could involve a scheduled job using a linked server. But linked servers require DBA-level knowledge to set up. If you can't use a linked server, just write a program that 1. Reads a row from the source table and 2. Inserts it into the target table. The programmer just needs to use a connection string that has INSERT privileges into the target database table. I have solved this problems using both approaches.

Upvotes: 0

themefield
themefield

Reputation: 4265

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://learn.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.

Upvotes: 1

Pavan Shevle
Pavan Shevle

Reputation: 71

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

Select source and target Database.

Upvotes: 5

Anubis
Anubis

Reputation: 2624

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

Upvotes: 2

pim
pim

Reputation: 12587

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.

Upvotes: 12

Zilog
Zilog

Reputation: 476

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.

Upvotes: 9

Software Engineer
Software Engineer

Reputation: 3956

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.

Upvotes: 3

e4rthdog
e4rthdog

Reputation: 5233

  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?

Upvotes: 3

Related Questions