Inserting/Updating information from a temporary table?

Question

I have a temporary table T1, which holds 'new data' to be inserted into permanent table T2.

Some of the records that are in T1 already exist in T2 so they will need to be Updated. The values in T1 that do not exist in T2 should be inserted. Both T1 and T2 have a ListingID column for a join. Creating a temp table would also be possible if needed.

Deleting the existing rows from the permanent T2 table is not an option, they need to be updated and the new rows inserted. Can anyone recommend a way to do this?

Answer 1

you can do this by two queries.

1. first update the t2 table with t1 values where listingid in(select listingid from t1)

2. second insert the t1 values into t2 where listingid not in(select listingid from t1)



or



if you use PHP or other language , take lisingid form two table and put it in two array and compare. if it is equal then use update query or insert query.

Answer 2

Use undemanding syntax for growing to be a table with prefix '#' for interior of sight non everlasting table or '##' for international non everlasting table, for instance: CREATE table #T1 ()

Answer 3

This can be done with a single step by using a cursor in one step:

---------------------------------

DECLARE @listingid INT

DECLARE @col1_value VARCHAR(100)

DECLARE @col2_value VARCHAR(100)



DECLARE cc CURSOR FOR

SELECT listingid, col1, col2

FROM t1



OPEN cc



FETCH NEXT FROM cc INTO @listingid, @col1_value, @col2_value



WHILE @@FETCH_STATUS=0

BEGIN



IF EXISTS (SELECT listingid FROM t2 WHERE listingid = @listingid)

BEGIN



/* DO THE UPDATE PROCEDURE */

UPDATEt2

SETcol1 = @col1_value, col2 = col2_value

WHERElistingid = @listingid



END

ELSE

BEGIN



/* DO THE INSERT PROCEDURE */

INSERT INTO t2 (column1, column2)

VALUES (@col1_value, @col2_value)



END



END



CLOSE cc

DEALLOCATE cc

---------------------------------