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

如果目标表已经存在，我希望查询将记录从一个表插入到不同数据库中的另一个表中，那么它应该将记录追加到该表的末尾。

389307 次浏览
小开

How about this:

USE TargetDatabase
GO


INSERT INTO dbo.TargetTable(field1, field2, field3)
SELECT field1, field2, field3
FROM SourceDatabase.dbo.SourceTable
WHERE (some condition)
小开 
INSERT
INTO    remotedblink.remotedatabase.remoteschema.remotetable
SELECT  *
FROM    mytable

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

小开

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

2 configure the linked server to use Credentials 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
小开

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

小开

Here's a quick and easy method:

CREATE TABLE database1.employees
AS
SELECT * FROM database2.employees;
小开 
    --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. */
小开

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'

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.

小开

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

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

小开

Mostly we need this type of query in migration script

INSERT INTO  db1.table1(col1,col2,col3,col4)
SELECT col5,col6,col7,col8
FROM db1.table2

In this query column count must be same in both table


提交答案