locked
select * into tableA [IN mydb] RRS feed

  • Question

  • Hi,

    how can I select all date from one table and insert it into another table in a different db but in the same sever?

    I've tried the following but witout success.

    use yourdb

    select * into tableA [IN mydb] from tableA

    Regards Wayne

     

     

     

    Thursday, March 25, 2010 8:38 AM

Answers

  • Hi wayne,

    suppose u want to insert values from "database2" database "d2-table2" into database1's  "d1_table1" then 

    USE database1
    INSERT INTO d1_table1
    (
     product_id
    )

    SELECT product_id FROM database2.dbo.table1

     


    Thanks and regards Razzak Sayyed
    • Edited by AbdulRazzak Thursday, March 25, 2010 8:51 AM forgot to erase alias name
    • Marked as answer by waynecod Thursday, March 25, 2010 9:47 AM
    Thursday, March 25, 2010 8:50 AM

All replies

  • Hi,

    Try Following Query:

    select * into DatabaseB.dbo.tableA from DatabaseA.dbo.tableA

    Failure in Life is failure to try...
    Thursday, March 25, 2010 8:43 AM
  • Here is one way to move all the data into a new table and create it at the same time. Just add the three part naming convention when moving the data into the other database, like databasename.schemaname.tablename

    CREATE TABLE #Temp(ID INT,Value VARCHAR(20),Dat DATETIME)
    INSERT INTO #Temp
    SELECT 1,'Test1', '10/12/2009' UNION ALL SELECT 2,'Test2','03/24/2010'
    
    SELECT *
    INTO #Temp2
    FROM #Temp

    If the second table already exists, you can do the following

    INSERT INTO #Temp2
    SELECT *
    FROM #Temp

    Abdallah El-Chal, PMP, ITIL, MCTS
    Thursday, March 25, 2010 8:44 AM
  • Hi wayne,

    suppose u want to insert values from "database2" database "d2-table2" into database1's  "d1_table1" then 

    USE database1
    INSERT INTO d1_table1
    (
     product_id
    )

    SELECT product_id FROM database2.dbo.table1

     


    Thanks and regards Razzak Sayyed
    • Edited by AbdulRazzak Thursday, March 25, 2010 8:51 AM forgot to erase alias name
    • Marked as answer by waynecod Thursday, March 25, 2010 9:47 AM
    Thursday, March 25, 2010 8:50 AM
  • Thanks it works perfect.

    Isn’t it easy when you know how

    Wayne

    Thursday, March 25, 2010 9:52 AM