How to insert table values from one database to another database

Question

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

User · Answer

INSERT INTO    remotedblink remotedatabase remoteschema remotetable SELECT    FROM    mytable   There is no such thing as  the end of the table  in relational databases

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

How about this   USE TargetDatabase GO  INSERT INTO dbo TargetTable field1  field2  field3     SELECT field1  field2  field3      FROM SourceDatabase dbo SourceTable      WHERE  some condition

User · Answer

Here s a quick and easy method   CREATE TABLE database1 employees AS SELECT   FROM database2 employees

User · Answer

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

User · Answer

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

[sql] How to insert table values from one database to another database?

Examples related to sql

Examples related to sql-server-2005

Examples related to integration

Examples related to append