Advertisement
Advertisement


Copy data from one column to other column (which is in a different table)


Question

I want to copy data from one column to another column of other table. How can I do that?

I tried the following:

Update tblindiantime Set CountryName =(Select contacts.BusinessCountry From contacts) 

but it did not work.

I want to copy "BusinessCountry" column of contact table to "CountryName" column of tblindiantime table.

2016/12/15
1
88
12/15/2016 7:22:54 AM

Accepted Answer

In SQL Server 2008 you can use a multi-table update as follows:

UPDATE tblindiantime 
SET tblindiantime.CountryName = contacts.BusinessCountry
FROM tblindiantime 
JOIN contacts
ON -- join condition here

You need a join condition to specify which row should be updated.

If the target table is currently empty then you should use an INSERT instead:

INSERT INTO tblindiantime (CountryName)
SELECT BusinessCountry FROM contacts
2010/07/29
88
7/29/2010 11:08:24 AM


Table2.Column2 => Table1.Column1

I realize this question is old but the accepted answer did not work for me. For future googlers, this is what worked for me:

UPDATE table1 
    SET column1 = (
        SELECT column2
        FROM table2
        WHERE table2.id = table1.id
    );

Whereby:

  • table1 = table that has the column that needs to be updated
  • table2 = table that has the column with the data
  • column1 = blank column that needs the data from column2 (this is in table1)
  • column2 = column that has the data (that is in table2)
2016/06/29

Hope you have key field is two tables.

 UPDATE tblindiantime t
   SET CountryName = (SELECT c.BusinessCountry 
                     FROM contacts c WHERE c.Key = t.Key 
                     )
2016/09/29

A similar question's answer worked more correctly for me than this question's selected answer (by Mark Byers). Using Mark's answer, my updated column got the same value in all the rows (perhaps the value from the first row that matched the join). Using ParveenaArora's answer from the other thread updated the column with the correct values.

Transforming Parveena's solution to use this question' table and column names, the query would be as follows (where I assume the tables are related through tblindiantime.contact_id):

UPDATE tblindiantime
SET CountryName = contacts.BusinessCountry
FROM contacts
WHERE tblindiantime.contact_id = contacts.id;
2017/05/23

I think that all previous answers are correct, this below code is very valid specially if you have to update multiple rows at once, note: it's PL/SQL

DECLARE
    CURSOR myCursor IS 
      Select contacts.BusinessCountry 
      From contacts c WHERE c.Key = t.Key;
    ---------------------------------------------------------------------
BEGIN
    FOR resultValue IN myCursor LOOP
        Update tblindiantime t
        Set CountryName=resultValue.BusinessCountry 
        where t.key=resultValue.key;
    END LOOP;
END;

I wish this could help.

2013/03/07

Now it's more easy with management studio 2016.

Using SQL Server Management Studio

To copy data from one table to another

1.Open the table with columns you want to copy and the one you want to copy into by right-clicking the tables, and then clicking Design.

2.Click the tab for the table with the columns you want to copy and select those columns.

3.From the Edit menu, click Copy.

4.Open a new Query Editor window.

5.Right-click the Query Editor, and then click Design Query in Editor.

6.In the Add Table dialog box, select the source and destination table, click Add, and then close the Add Table dialog box.

7.Right-click an open area of the the Query Editor, point to Change Type, and then click Insert Results.

8.In the Choose Target Table for Insert Results dialog box, select the destination table.

9.In the upper portion of the Query Designer, click the source column in the source table.

10.The Query Designer has now created an INSERT query. Click OK to place the query into the original Query Editor window.

11.Execute the query to insert the data from the source table to the destination table.

For More Information https://docs.microsoft.com/en-us/sql/relational-databases/tables/copy-columns-from-one-table-to-another-database-engine

2017/05/15

Source: https://stackoverflow.com/questions/3361768
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]