insert record from one table to another table in sql server

Question

1.00/5 (2 votes)

See more:

SQL-Server-2005

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 6-Aug-13 6:47am

sanjay acharya

Add a Solution

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.

2 solutions

Solution 1

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.

Posted 6-Aug-13 8:38am

Manas Bhardwaj

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?

Manas Bhardwaj 6-Aug-13 15:27pm

He basically wants to keep the tables in sync. I don't think it matters to him how you implement it. And to be frank, my solution seems to simplest of those (literally, 2 statements).

Well, I would rather stop here to assume what OP actually wants.

Zoltán Zörgő 6-Aug-13 15:31pm

Well, you'r probably right - let us wait until OP clarifies it's needs.
(Just a note: if you want two tables to be in sync in a single database, than you don't need two tables at all.)

Manas Bhardwaj 6-Aug-13 15:36pm

Well again subjective :), the tables could be in different databases which could potentially be in different servers altogether.

Zoltán Zörgő 6-Aug-13 15:39pm

Nothing subjective: just a bifurcation. If more than one databases are involved, that implies many other questions - and possible solutions.

Manas Bhardwaj 6-Aug-13 15:37pm

Of course, my suggestion does not cover the multiple database scenario.

Manas Bhardwaj 7-Aug-13 14:26pm

Somehow, this solution was 'valuable' for OP. See him comment ;)

Zoltán Zörgő 7-Aug-13 14:30pm

Well, people just don't know how to ask questions properly. Or how to analyze problems... or both. You won!

sanjay acharya 7-Aug-13 14:17pm

Thank you for your valuable solution for me.

Manas Bhardwaj 7-Aug-13 14:25pm

Glad it helped you. You should close the question by accepting the answer if you don't have question on this topic anymore.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2013-08-06T10:06:00

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.