将数据从一个数据库传输到另一个数据库

如何从一个数据库获取数据并插入到另一个数据库表中？我不能这么做。请帮助我把数据从一个转移到另一个。

369911 次浏览
小开
  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?

小开

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.
小开

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 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

小开

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
小开

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.
小开

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

小开

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

Select source and target Database.

小开

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.

小开

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.


提交答案