Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Inserting data from a table in database 1 to an identical table in database 2...both databases are on the same server

Answered Inserting data from a table in database 1 to an identical table in database 2...both databases are on the same server

  • Friday, January 18, 2013 9:51 PM
     
     
    Sign In to Vote
    0

    Hello,

    I am probably missing something very easy however, I cannot seem to figure out how to insert data from a table in database 1 to an identical table in database 2. Both databases are on the same server.

    Can't I use a simple insert query??

    Thanks in advance!

     

All Replies

  • Friday, January 18, 2013 9:56 PM
     
     Answered Has Code
    Sign In to Vote
    0

    You can use:

    INSERT INTO NameDatabase1..YourTableOfDB1
SELECT alias.thefieldsOfTableOfDB2 
FROM NameDatabase2..YourTableOfDB2 alias

    But the user you are using to execute the query needs to have access both databases

    please, check this post:

    SQL Express Database table data insert into another database

    Regards !

    Sergio Sánchez Arias



     
  • Friday, January 18, 2013 9:59 PM
     
      Has Code
    Sign In to Vote
    0

    You can. If you are in database a and need to insert into database b for a table called mytable, you would write something like

    INSERT INTO b.dbo.MyTable(col1, col2, col3...)
SELECT
    col1, col2, col3,.....
FROM
    MyTable

    Additionally, you may need to handle things like identities. What problems are you facing? Can you please elaborate?

     
  • Friday, January 18, 2013 10:12 PM
     
     
    Sign In to Vote
    0

    Hi Sergio, thanks for this....I am getting the following error associated with this method.

    "An explicit value for the identity column in table 'ambientair..tbl_StationWind' can only be specified when a column list is used and IDENTITY_INSERT is ON."

    Any idea why?

    Thanks

     
  • Friday, January 18, 2013 10:18 PM
     
     Answered Has Code
    Sign In to Vote
    0

    This error occurs when you're trying to insert data into a table that contains an identity column.

    You need to do this:

    Before your SQL statement.

    --Before your SQL INSERT STATEMENT
SET IDENTITY_INSERT <tablename> ON

{YOUR SQL INSERT STATEMENT}
--After your SQL INSERT STATEMENT:
SET IDENTITY_INSERT <tablename> OFF
    The tablename is the table are using in your INSERT stament.

    Sergio Sánchez Arias

     
  • Friday, January 18, 2013 10:21 PM
     
      Has Code
    Sign In to Vote
    0

    try this..
    Use DestinationDatabase
    GO
    set identity_insert dbo.DestinationTable  on
    INSERT INTO dbo.DestinationTable(col1, col2, col3)
SELECT col1, col2, col3
FROM SourceDatabase.schemaName.SourceTable

    Hope it Helps!!



    • Edited by Stan210 Friday, January 18, 2013 10:23 PM
    • Edited by Stan210 Friday, January 18, 2013 10:27 PM
    •  
     
  • Friday, January 18, 2013 10:35 PM
     
      Has Code
    Sign In to Vote
    0

    Since both table are identical, you might not need to specify the 'Identity' column while you are inserting into the Second table.

      INSERT INTO DB2.SchemaName.Table1
	(--Col1 (IdentityColumn)
      ,Col2
      ,Col3
      ,Col3
     )
     SELECT  
      --Col1
	  ,Col2
      ,Col3
      ,Col3]
    FROM DB1.SchemaName.Table1

    Narsimha