Results 1 to 6 of 6
  1. #1
    Join Date
    May 2006
    Posts
    20

    Unanswered: copy data from 2 tables

    I need to copy the data in the last name table to the field2 table do i use the update query in sql 2005?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I don't really know enough about your problem to answer your question. Depending on the table structure and the tools you are using the answer is probably either yes or no.

    I'm going to move your question to the SQL Server forum, since that is the database engine that you're using. See the FAQ at the top of the forum postings for the entry on how to ask questions to get quick and correct answers, it should help a lot.

    I'll be happy to help, once I know what your tables look like, and what client tool(s) you are using (Query Analyzer, Microsoft Access, etc).

    -PatP

  3. #3
    Join Date
    May 2006
    Posts
    20
    I have two databases database1 and database2 i need to copy all the data in database1 field lastname to database2 field field2 but only if database1.cust_no matches database2.sourceid

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    CREATE TABLE database1 (
       cust_no	VARCHAR(9)
    ,  lastname	VARCHAR(25)
       )
    
    CREATE TABLE database2 (
       cust_no	VARCHAR(9)
    ,  field2	VARCHAR(100)
       )
    
    INSERT INTO database1 (cust_no, lastname)
       SELECT 'doe', 'Doe' UNION
       SELECT 'jones', 'Jones' UNION
       SELECT 'freshfitz', 'Freshfitz' UNION
       SELECT 'pope', 'Pope' UNION
       SELECT 'smith', 'Smith'
    
    INSERT INTO database2 (cust_no, field2)
       SELECT 'doe', NULL UNION
       SELECT 'jones', 'Jones' UNION
       SELECT 'freshfitz', 'was here' UNION
       SELECT 'popper', NULL UNION
       SELECT 'smith', 'Smith'
    
    UPDATE z
       SET field2 = lastname
       FROM database2 AS z
       JOIN database1 AS x
          ON (x.cust_no = z.cust_no)
    
    SELECT *
       FROM database2
    
    DROP TABLE database1
    DROP TABLE database2
    -PatP

  5. #5
    Join Date
    May 2006
    Posts
    20
    Thats awesome but the problem is database1 and database 2 already exist and the fields already exist i just need to copy all the records in the field of database 1 field last name to database2 field field2(which has no data). The records in database1 have a unique id Cust_no that match the same unique id in database2 sourceid.

    So if cust_no = sourceid copy the last name field


    select * from database1 field lastname
    copy to database2 field field2
    where database1.cust_no = database2_sourceid

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    So just start with the UPDATE, and you'll be fine.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •