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

Question

I want the query to insert records from one table into another table in another database, if the destination table already exists, it should add records to the end of the table.

Answer 1

How about this:

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

Answer 2

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

1 Creating Linked Servers {if necessary} (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 RELATED SERVERS

 EXEC sp_testlinkedserver N'NAMEOFLINKEDSERVER' 

---

INSERT THE NEW LOCAL TABLE

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

OR

INSERT AS NEW VALUES TO THE 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 

Answer 3

Here is a quick and easy way:

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

Answer 4

You can try

 Insert into your_table_in_db1 select * from your_table_in_db2@db2SID 

db2SID is the seed of another database. It will be present in the tnsnames.ora file.

Answer 5

  --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. */ 

Answer 6

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

In relational databases, there is no such thing as end of table.

Answer 7

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

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

Answer 8

Just do it.....

(This will create the same table structure as from the table, as in a table with the same data)

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