Copy data from one table to another

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

---

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

insert into schemaname1.tablename1 (columnname1)

Select columnname2

from schemaname2.tablename2