Click here to Skip to main content
15,877,675 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
I have two tables ,i want to insert record from one table to another table ,if record exists in same id them update it otherwise insert new record, without using curser or loops.Please provide me simple solution for this problem.code sould support both sqlserver 2005/2008
Posted
Comments
Zoltán Zörgő 6-Aug-13 14:55pm    
1) what's wrong with cursors and loops?
2) as you want an insert-or-update that is working in 2005 too, you will need to do it row by row. From 2008 up, you have the MERGE statement to do that.
3) The update you need should be complete (you have all fields in both the source and the target?) I yes, than you can do it without parsing the table, but not in a single statement.
sanjay acharya 7-Aug-13 14:14pm    
Because Cursors is very expensive database object.So iwant to avoid it.
Zoltán Zörgő 7-Aug-13 14:31pm    
Depends.

In both cases there is possoble to use EXISTS[^] statement, which specifies a subquery to test for the existence of rows.

Insert scenario:
SQL
INSERT INTO TableName (Field1, Field2, ..., FieldN)
SELECT Field1, Field2, ..., FieldN
FROM TableName AS t1
WHERE NOT EXISTS (
    SELECT Field1, Field2, ..., FieldN
    FROM OtherTableName AS t2 
    WHERE t1.ID = t2.ID
    )

The same is possible to achieve using IN clause[^].

Update scenario:
SQL
UPDATE t1 
    SET t1.Field1 = t2.Field1,
        t1.Field2 = t2.Field2
FROM TableName AS t1 INNER JOIN OtherTableName AS t2 ON t1.ID = t2.ID


This is a general conception... Change it to your needs.
 
Share this answer
 
Comments
Manas Bhardwaj 7-Aug-13 14:26pm    
On the spot +5!
Maciej Los 7-Aug-13 14:52pm    
Thank you ;)
If I understand, you want to keep the 2nd table as copy of 1st. Why don't you do something like this i.e. make the 2nd table empty and then fill it from 1st table.

SQL
DELETE FROM Table2

INSERT Table2
    SELECT * FROM Table1


Please note that this is not the best solution for huge data. If you are trying to implement some kind of backup procedure, there are other techniques to do that.
 
Share this answer
 
Comments
Zoltán Zörgő 6-Aug-13 14:51pm    
You have missed OP's insert-or-update requirement.
Manas Bhardwaj 6-Aug-13 15:01pm    
Actually, I did not. :)

If you read my whole answer (and not just the code), you would realize that I intentionally did not provide that solution (Insert/Update).

My answer is a proposal and giving him another possible direction. Isn't my proposal doing functionally same thing and in a simpler way?
Zoltán Zörgő 6-Aug-13 15:15pm    
But I don't think your suggestion will help OP in any form. OP asked for a MERGE/UPSERT feature, and that one can be hardly substituted with any regular backup-restore mechanism - or anything else than a MERGE feature, which exists in SQL Server 2008 and above, in Oracle, Firebird, SQLite, kind of in MySQL.. but there is nothing alike in SQL Server 2005 or below. It can be implemented using a stored procedure, but there is no inbuilt statement.
Manas Bhardwaj 6-Aug-13 15:19pm    
He never asked for MERGE solution which is technical solution. What he has described is functional requirement where he wants to copy data from one table to another, basically to keep a copy of first table.
Zoltán Zörgő 6-Aug-13 15:22pm    
What mean this part than: if record exists in same id them update it otherwise insert new record?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900