locked
Insert/update Statement on two identical tables RRS feed

  • Question

  • HI,

    I have TWO  identical table structure on which I am trying to Insert/update change made in one table  into second table
     one table is temp and another one is permanent table ,permannet table will be any table in the database and in temp table the data will be in binary format

    i generated the insert and update statements for any table structure

    of two identical tables but the problem  is that

    when to fire insert and update statement,i have to check for the data for primary key if exists update else insert ,how can i do this process

    below is query am trying to check data exists

    SELECT COUNT(*) FROM Table_1 where id=value
            union
            select col1 from ##Mytemp where col1=convert(int,cast(col1 as binary))

    if u r not understood my question plz free to ask me again

    Thanks in advance



    • Edited by pradeepmanne Saturday, February 18, 2012 5:57 AM improve question
    Saturday, February 18, 2012 5:36 AM

Answers

All replies

  • "and in temp table the data will be in binary format"

    What does that mean. Is it a varbinary column??

    Check this.

    --Step1
    UPDATE PermTable
    SET PermTable.cols = t1.cols
    WHERE exists (SELECT 1 FROM PermTable t2 
    WHERE t1.pkid = t2.pkid)
    
    --Step2
    INSERT INTO PermTable
    SELECT * FROM TempTable t1
    WHERE NOT EXISTS(SELECT 1 FROM PermTable t2 
    WHERE t1.pkid = t2.pkid)

    You do it in two steps.

    • You are updating the existing records in your permanent table which has corresponding records in temp table
    • you then insert the non existing records.

    Hope this answers.


    Planet Earth is at risk. Global warming is on a high tide.
    Take Responsibility. Plant Trees. Keep your City Clean and Green.

    Mark all Helping Posts and Close your Threads. Keep the Forum Green.
     - Arun Kumar Allu

    • Proposed as answer by Budhi G Saturday, February 18, 2012 6:25 AM
    Saturday, February 18, 2012 5:56 AM
  • You can do in two ways like below:

    1) IF EXISTS (SELECT * FROM table1 WHERE id = 6)
    	begin
    	end
    
    2) declare @rowcnt int
    select @rowcnt = count(column1) from table1 where id = 6
    if(@rowcnt > 0 )
    	begin
    	end

    Saturday, February 18, 2012 6:06 AM
  • hi,

    thanks for the reply

    to insert into a permanenet table we have to use the statement like

    insert into table select convertfunction of cols from ##temptable

    i have generated the inseretstatement but how to pass the value and check for the existance

    and alla temp table are inserted at a time with single insert stastement


    • Edited by pradeepmanne Saturday, February 18, 2012 6:15 AM
    Saturday, February 18, 2012 6:15 AM
    • "insert into table select convertfunction of cols from ##temptable"????????????
    • "i have generated the inseretstatement but how to pass the value and check for the existance and alla temp table are inserted at a time with single insert stastement" - Check arun's script. It does the same.
    Saturday, February 18, 2012 6:24 AM
  • Probably you are looking for MERGE:

    http://www.sqlusa.com/bestpractices2008/advancedsql/merge-upsert/


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES


    • Edited by Kalman Toth Saturday, February 18, 2012 6:26 AM
    • Proposed as answer by Janos BerkeMVP Saturday, February 18, 2012 6:32 AM
    • Marked as answer by pradeepmanne Saturday, February 18, 2012 12:07 PM
    Saturday, February 18, 2012 6:26 AM
  • Hope this will help you

    declare @t1 table (c1 int)
    insert into @t1 values (1),(2),(3),(4),(5),(6)
    
    declare @i int = 3
    declare @t2 table (c1 int)
    
    --insert into @t2 values (3)
    if exists (select * from @t2 where c1 = @i)
    	begin
    		update @t2 set c1 = 6 where c1 = @i
    	end
    else 
    	begin
    		insert into @t2
    		select * from @t1 where c1 = @i
    	end
    
    select * from @t2

    Saturday, February 18, 2012 6:26 AM
  • HI ,

    Thanks for the solution ,it solved the problem

    Saturday, February 18, 2012 12:08 PM