SQL 如何将表值从一个数据库插入到另一个数据库？

How to insert table values from one database to another database?

提问by naveenkumar

I want a query to insert records from one table to another table in a different database if the destination table already exists, it should append the records at the end of the table.

如果目标表已经存在，我想要一个查询将记录从一个表插入到另一个数据库中的另一个表，它应该将记录附加到表的末尾。

回答by marc_s

How about this:

这个怎么样：

USE TargetDatabase
GO

INSERT INTO dbo.TargetTable(field1, field2, field3)
   SELECT field1, field2, field3
     FROM SourceDatabase.dbo.SourceTable
     WHERE (some condition)

回答by OzzKr

How to insert table values from one server/database to another database?

如何将表值从一个服务器/数据库插入到另一个数据库？

1 Creating Linked Servers {if needs} (SQL server 2008 R2 - 2012) http://technet.microsoft.com/en-us/library/ff772782.aspx#SSMSProcedure

1 创建链接服务器{如果需要}（SQL server 2008 R2 - 2012） http://technet.microsoft.com/en-us/library/ff772782.aspx#SSMSProcedure

2 configure the linked server to use Credentials a) http://technet.microsoft.com/es-es/library/ms189811(v=sql.105).aspx

2 配置链接服务器以使用凭据 a) http://technet.microsoft.com/es-es/library/ms189811(v=sql.105).aspx

EXEC sp_addlinkedsrvlogin 'NAMEOFLINKEDSERVER', 'false', null, 'REMOTEUSERNAME', 'REMOTEUSERPASSWORD'

-- CHECK SERVERS

-- 检查服务器

SELECT * FROM sys.servers

-- TEST LINKED SERVERS

-- 测试链接的服务器

EXEC sp_testlinkedserver N'NAMEOFLINKEDSERVER'

---

---

INSERT INTO NEW LOCAL TABLE

插入新的本地表

SELECT * INTO NEWTABLE
FROM [LINKEDSERVER\INSTANCE].remoteDATABASE.remoteSCHEMA.remoteTABLE

OR

或者

INSERT AS NEW VALUES IN REMOTE TABLE

在远程表中作为新值插入

INSERT
INTO    [LINKEDSERVER\INSTANCE].remoteDATABASE.remoteSCHEMA.remoteTABLE
SELECT  *
FROM    localTABLE

INSERT AS NEW LOCAL TABLE VALUES

插入新的本地表值

INSERT
INTO    localTABLE
SELECT  *
FROM    [LINKEDSERVER\INSTANCE].remoteDATABASE.remoteSCHEMA.remoteTABLE

回答by Cillín

Here's a quick and easy method:

这是一个快速简便的方法：

CREATE TABLE database1.employees
AS
SELECT * FROM database2.employees;

回答by Sagar Mahajan

    --Code for same server
USE [mydb1]
GO

INSERT INTO dbo.mytable1 (
    column1
    ,column2
    ,column3
    ,column4
    )
SELECT column1
    ,column2
    ,column3
    ,column4
FROM [mydb2].dbo.mytable2 --WHERE any condition

/*
steps-
    1-  [mydb1] means our opend connection database 
    2-  mytable1 the table in mydb1 database where we want insert record
    3-  mydb2 another database.
    4-  mytable2 is database table where u fetch record from it. 
*/

--Code for different server
        USE [mydb1]

    SELECT *
    INTO mytable1
    FROM OPENDATASOURCE (
            'SQLNCLI'
            ,'Data Source=XXX.XX.XX.XXX;Initial Catalog=mydb2;User ID=XXX;Password=XXXX'
            ).[mydb2].dbo.mytable2

        /*  steps - 
            1-  [mydb1] means our opend connection database 
            2-  mytable1 means create copy table in mydb1 database where we want 
                insert record
            3-  XXX.XX.XX.XXX - another server name.
            4-  mydb2 another server database.
            5-  write User id and Password of another server credential
            6-  mytable2 is another server table where u fetch record from it. */

回答by sandywho

You can try

你可以试试

Insert into your_table_in_db1 select * from your_table_in_db2@db2SID 

db2SID is the sid of other DB. It will be present in tnsnames.ora file

db2SID 是其他 DB 的 sid。它将出现在 tnsnames.ora 文件中

回答by Quassnoi

INSERT
INTO    remotedblink.remotedatabase.remoteschema.remotetable
SELECT  *
FROM    mytable

There is no such thing as "the end of the table" in relational databases.

关系数据库中没有“表尾”这样的东西。

回答by Ghazali Shacklebolt

If both the tables have the same schema then use this query: insert into database_name.table_name select * from new_database_name.new_table_name where='condition'

如果两个表具有相同的架构，则使用此查询： insert into database_name.table_name select * from new_database_name.new_table_name where='condition'

Replace database_name with the name of your 1st database and table_name with the name of table you want to copy from also replace new_database_name with the name of your other database where you wanna copy and new_table_name is the name of the table.

将 database_name 替换为第一个数据库的名称，将 table_name 替换为要从中复制的表的名称，还将 new_database_name 替换为要复制的其他数据库的名称，而 new_table_name 是表的名称。

回答by Imranmadbar

Just Do it.....

去做就对了.....

( It will create same table structure as from table as to table with same data )

（它将创建与从表到具有相同数据的表相同的表结构）

 create table toDatabaseName.toTableName as select * from fromDatabaseName.fromTableName;

回答by lehieusoftware

For SQL Server, you can use tool Import Data from another Database, It's easier to configuration mapping columns.

对于 SQL Server，您可以使用工具从另一个数据库导入数据，配置映射列更容易。