Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

updating a table with values from another table

Posted on 1999-07-01
2
Medium Priority
?
177 Views
Last Modified: 2010-03-19
Can someone provide me with the sql which will update the records in table 1 with the records from table2 that are different, but columns 'ID' are equal in both tables.  and also append any records from table2 that arent in table1.

I know the easiest way would be to delete from table1 then insert into table1 (select * from table2), but I don't want to enable the select into option on the database because it doesn't use the transaction log.

Daz
0
Comment
Question by:Dazza051197
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 7

Accepted Solution

by:
simonsabin earned 400 total points
ID: 1096074
Use this
you will need to add the extra columns in your tables

begin
  declare @tid int,@descr varchar(255)
  declare ctable2 CURSOR FOR SELECT tid,descr FROM table2

  open ctable2

nextrow:
  FETCH ctable2 INTO @tid,@descr
  IF @@FETCH_STATUS <> -1
    BEGIN
      IF EXISTS (SELECT 1 FROM table1 WHERE tid = @tid)
        UPDATE table1 SET descr = @descr WHERE tid = @tid
      ELSE
        insert into table1 (tid,descr) VALUES (@tid,@descr)
    GOTO nextrow
    END

  CLOSE ctable2
  DEALLOCATE ctable2
END

go
0
 
LVL 1

Author Comment

by:Dazza051197
ID: 1096075
Thanks.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question