Transfer data from one database to another database

Question

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.

Many ways to do this. What are your requirements? "I can't do this." What have you tried? What error do you see? — Jamie F, Apr 05 '12 at 12:59
The structure of the question leaves somewhat clear the author is completely lost. You can of course judge him by beginning his journey over here. I would just write an answer. — András Gyömrey, Oct 22 '15 at 13:42

score 149 · Accepted Answer · edited Jun 12 '20 at 17:47

149

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

edited Jun 12 '20 at 17:47

Glorfindel

21,988
13
81
109

answered Apr 05 '12 at 14:18

Virus

3,215
7
29
46

4

in sql server 2008 to copy all data of each table you should enable Script Data in 'Tasks' > 'Generate scripts'==>Script Wizard==> choose Script Option part – Arash Oct 23 '13 at 22:03
1

Option 1 I get an error on `preparing data` on the end step. Option 2 I get an error before `Choose the database to copy the tables to` – Emaborsa Jul 01 '15 at 06:40
1

i have data in both the databases .. will your solution help in this situation? – SanamShaikh Apr 25 '18 at 17:21
1

In SSMS 2019 scripting data is no longer supported. You must use export. – Rob Nov 02 '21 at 12:31
Facing same issues as mentioned by @Emaborsa – Abhi Feb 16 '22 at 09:09
What about the constraints that are there in the destination server? – t4thilina Jun 08 '23 at 23:05

score 19 · Answer 2 · edited Sep 14 '21 at 11:00

19

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

edited Sep 14 '21 at 11:00

CervEd

3,306
28
25

answered Jun 20 '15 at 07:10

Lokanathan

191
1
2

this worked for me, however, in SqlExpress 2014 I had to use the .. notation found in other answers i.e. INSERT INTO [db1]..table1( ID, MetaTitle ) SELECT ID, MetaTitle FROM [db2]..table1 – Adam Hey Dec 07 '17 at 16:37
4

The order should be `onedatabase.dbo.FolderStatus` and `Twodatabase.dbo.f_file_stat`. Or you use the `..` notation as @AdamHey pointed out. – bugybunny Jan 11 '19 at 15:10

pim · Answer 3 · 2016-03-15T18:44:57.760

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

Open SSMS.
Right-click the Database you wish to copy data from.
Select Generate Scripts >> Select Specific Database Objects >> Choose the tables/object you wish to transfer. strong text
In the "Save to file" pane, click Advanced
Set "Types of data to script" to Schema and data
Set "Script DROP and CREATE" to Script DROP and CREATE
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.
Set filepath >> Next >> Next
Open newly created SQL file. Remove "Use" from top of file.
Open new query window on destination database, paste script contents (without using) and execute.

Can't believe this is still the best solution out there 7 years later, but this was about the only thing that worked for me. Thanks a ton! — malthe.w, Aug 18 '23 at 13:20

score 9 · Answer 4 · answered Apr 05 '12 at 13:48

9

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.

answered Apr 05 '12 at 13:48

Zilog

468
3
7

2

If you use the select * into syntax the keys and constraints will not be copied – ArieKanarie Jun 26 '18 at 15:41

score 5 · Answer 5 · answered Mar 20 '18 at 09:33

5

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

Select source and target Database.

answered Mar 20 '18 at 09:33

Pavan Shevle

61
1
1

1

it's a great tool. although the comparison is slowly :) – Mi.HTR Jun 27 '18 at 06:40

score 3 · Answer 6 · answered Apr 05 '12 at 12:44

You can backup and restore the database using Management Studio.
Again from Management Studio you can use "copy database".
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?

score 3 · Answer 7 · edited May 23 '17 at 11:54

3

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

edited May 23 '17 at 11:54

Community

1
1

answered Apr 05 '12 at 12:54

Software Engineer

3,906
1
26
35

score 2 · Answer 8 · answered May 27 '16 at 09:00

2

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

answered May 27 '16 at 09:00

Anubis

2,484
2
22
33

3

That's all well and good, except that guy drops FKs and PKs and doesn't seem to put them back... Also aren't answers supposed to be more than effectively just a link to someone else's stuff? – Jon Aug 07 '17 at 21:52
1

@Jon Why do you want to restore PK and FK in Source database? It is better to copy it an throw into trash after export. – Anubis Aug 08 '17 at 14:32
1

Because what I'm trying to do is sync up two environments (i.e. bring QA down to Develop so that we don't have to duplicate all of their data entry). – Jon Aug 09 '17 at 16:03

score 1 · Answer 9 · answered Mar 28 '20 at 22:11

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.

score 0 · Answer 10 · answered Jul 21 '21 at 17:36

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.

Transfer data from one database to another database

10 Answers10

Linked