locked
Copy data from one table to another RRS feed

  • Question

  • Though experienced in the past with MySQL, I am newer to MSSQL so bear with me. I need to create a stored procedure or script that copies all the entires from one column in one table into another similiar column in another table. Where oh where to begin... 

    Many thanks ahead of time...



    • Edited by HSSNE Tuesday, November 20, 2012 2:22 PM
    Tuesday, November 20, 2012 2:21 PM

Answers

All replies

  • just one column ? Do you want to update existing table or insert new records ?

    IF new insert then you can do : 

    Insert into tableb(column1)

    Select column1 

    From tablea

    here is an example : 

    Declare @tablea table (id int , name varchar(100))
    insert into @tablea 
    values(1,'testa'),(2,'testb')

    Declare @tableb table (name varchar(100))

    Insert into @tableb 
    Select name
    From @tablea

    select * From @tableb

    --

    For a basic stored procedure you can use similar script :

    CReate procedure spinsert

    as

    begin

    Insert into @tableb 
    Select name
    From @tablea

    end


    • Marked as answer by HSSNE Tuesday, November 20, 2012 2:28 PM
    • Edited by JR1811 Tuesday, November 20, 2012 2:30 PM
    • Unmarked as answer by HSSNE Tuesday, November 20, 2012 4:49 PM
    Tuesday, November 20, 2012 2:26 PM
  • HSSNE, try as follows

    --Create tables
    Create table sourcetable(id int, data char(10))
    Create table destinationtable(id1 int, data1 char(10))
    --Insert Recrods
    Insert into sourcetable values(1,'a'),(2,'b')
    --Create SP
    Create proc Copydata as
    Begin
      Insert into destinationtable (data1) select data from sourcetable
    End
    
    --Execute SP
    Exec Copydata
    --Verifry data
    select * from destinationtable


    Thanks
    Manish

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by HSSNE Tuesday, November 20, 2012 2:28 PM
    • Unmarked as answer by HSSNE Tuesday, November 20, 2012 4:49 PM
    Tuesday, November 20, 2012 2:28 PM
  • Yes, just one column. Many thanks, I thought it would be more complicated than that. I will give it a try...

    I never expect anyone to rewrite my code. I am simply grateful for your continued patience, time, and your remembrance when you first started... HSSNE

    Tuesday, November 20, 2012 2:28 PM
  • Sorry to reopen this...

    So I attempted to create the stored procedure and am receiving an error "Invalid Object name" in my SELECT statement where i have concantenated the table and column name...

    Let me clarify a bit. I simply need to copy the values contained in one column to another column in the SAME table so here is what I have:

    CREATE proc COPYDATA as
    Begin
    INSERT into IV00101(PRCHSUOM)
    SELECT * FROM IV00101.UOMSCHDL
     End
    
    Exec COPYDATA

    Table:  IV00101

    Copy data from column:  PRCHSUOM

    Copy data to coulmn:  UOMSCHDL

    Thanks for helpiong me learn...


    I never expect anyone to rewrite my code. I am simply grateful for your continued patience, time, and your remembrance when you first started... HSSNE

    Tuesday, November 20, 2012 4:48 PM
  • Correct syntax:

    UPDATE IV00101 SET PRCHSUOM =  UOMSCHDL  

    You may want it the other way around, then just switch the columns.


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012






    • Proposed as answer by JR1811 Tuesday, November 20, 2012 5:02 PM
    • Edited by Kalman Toth Tuesday, November 20, 2012 5:06 PM
    • Marked as answer by HSSNE Tuesday, November 20, 2012 5:24 PM
    Tuesday, November 20, 2012 4:59 PM
  • insert into schemaname1.tablename1 (columnname1)

    Select columnname2

    from schemaname2.tablename2 


    • Edited by JR1811 Tuesday, November 20, 2012 5:04 PM
    Tuesday, November 20, 2012 5:03 PM