how to insert/up[date rows from one table to anoth

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 2005 Forums

Transact-SQL (2005)

how to insert/up[date rows from one table to anoth

Author

Topic

dhani
Posting Yak Master

132 Posts

Posted - 2008-10-09 : 13:23:23

hello all,

can any one tell me, how can i write a procedure or t-sql statement
that i have 2 tables (example: tempTransaction, originalTransactions)

in above both tables it has columns (example: transno, amount,no.of items)

so temptransaction table rows compare with originaltransaction table rows,
if row(transno) not found in originaltransaction table then insert the row
if found and has updates (either in amount or no.of items) then update with temptransaction table values for particular transaction(transno)

can you please provide any examples or thoughts

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 13:37:07

[code]CREATE PROC ApplyDeltas
AS
INSERT INTO originalTransactions
SELECT tmp.transno, tmp.amount,tmp.[no.of items]
FROM tempTransaction tmp
LEFT JOIN originalTransactions org
ON org.transno = tmp.transno
WHERE org.transno IS NULL

UPDATE org
SET org.amount=tmp.amount,
org.[no.of items]=tmp.[no.of items]
FROM originalTransactions org
INNER JOIN tempTransaction tmp
ON org.transno = tmp.transno
WHERE org.amount<>tmp.amount
OR org.[no.of items]<>tmp.[no.of items]
GO[/code]

dhani
Posting Yak Master

132 Posts

Posted - 2008-10-09 : 19:39:15

Hello Vishak,
Thank you for your response,

your answer worked me very fine, but i have some adjustments in the query, i tried it but unable to get exactly,

can you please give me idea or some procedure or t-sql statement
that i have 2 tables (example: tempTransaction, originalTransactions)

tempTransactions table columns(transno, amount,no.of items,status)
OrigTransactions table columns(transno, amount,no.of items)

so tempTransaction table rows will compare with originalTransaction table rows,
if row(transno) not found in originalTransaction table, then insert the row in OriginalTransaction table, and also update the status column (in tempTransaction table) to "INS"

if found and it has updates (either in amount or no.of items) then update with tempTransaction table values for particular transaction(transno) in OrigTransactions table, and also update the status column (in tempTransaction table) to "UPD"

if found and it has NO updates (in both tables same record) then delete from tempTransaction
can you please provide any examples or thoughts

Best Regards
aswani

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-10 : 01:04:06

[code]CREATE PROC ApplyDeltas
AS
DECLARE @INSERTED_IDS table
(
transno int
)
DECLARE @UPDATED_IDS table
(
transno int
)

INSERT INTO originalTransactions
OUTPUT INSERTED.transno INTO @INSERTED_IDS
SELECT tmp.transno, tmp.amount,tmp.[no.of items]
FROM tempTransaction tmp
LEFT JOIN originalTransactions org
ON org.transno = tmp.transno
WHERE org.transno IS NULL

UPDATE org
SET org.amount=tmp.amount,
org.[no.of items]=tmp.[no.of items]
OUTPUT INSERTED.transno INTO @UPDATED_IDS
FROM originalTransactions org
INNER JOIN tempTransaction tmp
ON org.transno = tmp.transno
WHERE org.amount<>tmp.amount
OR org.[no.of items]<>tmp.[no.of items]

DELETE tmp
FROM tempTransaction tmp
INNER JOIN originalTransactions org
ON org.transno = tmp.transno
AND org.amount=tmp.amount
AND org.[no.of items]=tmp.[no.of items]

UPDATE tmp
SET tmp.status=CASE WHEN i.transno IS NOT NULL THEN 'INS'
ELSE 'UPD'
END
FROM tempTransaction tmp
LEFT JOIN @INSERTED_IDS i
ON i.transno=tmp.transno
LEFT JOIN @UPDATED_IDS u
ON u.transno=tmp.transno
WHERE i.transno IS NOT NULL
OR u.transno IS NOT NULL
GO[/code]
i've assumed transno is int. if not change accordingly in two table varuables.

dhani
Posting Yak Master

132 Posts

Posted - 2008-10-10 : 10:52:58

Hello Visakh

thank you for your immediate response

the code is working as below

in originalTrans table it is inserting all new values, updating the changed values in origtrans table
but
it is deleting all rows in the temptransaction table
even i changed delete (query) to the above of insert query
even there is no change

can you look at once please

Regards
asini

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-10 : 11:49:40

i think moving DELETE statement as first one above insert should solve the problem.

dhani
Posting Yak Master

132 Posts

Posted - 2008-10-10 : 12:15:46

sorry visakh i did a small mistake( that i created procedure but i didn't move delete part in procedure)
i moved delete code above to insert statement
and it is worked fine

thank you very much

i grateful to you

asini

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-10 : 12:42:30

quote:
Originally posted by dhani

sorry visakh i did a small mistake( that i created procedure but i didn't move delete part in procedure)
i moved delete code above to insert statement
and it is worked fine

thank you very much

i grateful to you

asini

no worries

you're welcome
glad that i could help you out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources