How to insert table values from one database to another database?
SqlSql Server-2005IntegrationAppendSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
How about this:
USE TargetDatabase
GO
INSERT INTO dbo.TargetTable(field1, field2, field3)
SELECT field1, field2, field3
FROM SourceDatabase.dbo.SourceTable
WHERE (some condition)
Solution 2 - Sql
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
Solution 3 - Sql
--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. */
Solution 4 - Sql
Here's a quick and easy method:
CREATE TABLE database1.employees
AS
SELECT * FROM database2.employees;
Solution 5 - Sql
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
Solution 6 - Sql
INSERT
INTO remotedblink.remotedatabase.remoteschema.remotetable
SELECT *
FROM mytable
There is no such thing as "the end of the table" in relational databases.
Solution 7 - Sql
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;
Solution 8 - Sql
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
Solution 9 - Sql
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.
Solution 10 - Sql
For SQL Server, you can use tool Import Data from another Database, It's easier to configuration mapping columns.