Updating table from another table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Updating table from another table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-30 : 09:31:54

Chade writes "I would like to know what the most effecient way of doing the following is:

I have a table that is update via a DTS package from another server (table1). I then have another table that holds the same information but is indexed - this is used for the intranet(table2). There is a common field that does not change.

What would be the best way to:

1. Add new records to table2 that exist in table1 and not in table2.
2. Update all record that exist in both table1 and table2.
3. Delete records from table2 that no longer exist in table1.

I know that I can write a script in VB but is there anyway to do this through SQL.

Thanks,"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-30 : 09:57:18

Three DML statements: one INSERT, one UPDATE, on DELETE . . . two correlated subqueries . . . one inner join....


insert into dbo.table2 (<col_list>)
select
    <col_list>
from
    dbo.table1 a
where
    not exists (
        select 1
        from
            dbo.table2
        where
            a.<common_column> = <common_column> )

update dbo.table2
set
    <columns>
from
    dbo.table1 a
    inner join dbo.table2 b
       on a.<common_column> = b.<common_column>

delete a
from
    dbo.table2 a
where
    not exists (
        select 1
        from
            dbo.table1
        where
            a.<common_column> = <common_column> )

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources