Dim Dim - 6 months ago 39
SQL Question

If id (for each sql row) exists update else insert

I have two tables. In first table i have columns id,name,
I want to check in the second table if id(from first table) exists, then update name.
If id doesnt exist insert id,name.

I'm using this code but it doesnt work.

System.Data.SqlClient.SqlCommand CheckNone = new System.Data.SqlClient.SqlCommand("IF EXISTS(SELECT id from test) SELECT 1 ELSE SELECT 0", con);
con.Open();
var result = (int)CheckNone.ExecuteScalar();
if (result == 0)
{
cmd = new SqlCommand(" insert into test(id,name) select id,name from Tamio.dbo.memberform", con);
cmd.ExecuteNonQuery();
con.Close();
}
else
{
SqlCommand cmd = new SqlCommand(" update test set test.name select memberform.name from Tamio.dbo.memberform", con);
cmd.ExecuteNonQuery();
con.Close();


I Want succeed this.
enter image description here

Answer

Uh

IF EXISTS(SELECT id from test) SELECT 1 ELSE SELECT 0

That will return 1 if any id exists

The update does not match up rows in any way
Not even valid syntax

update test set test.name select memberform.name from Tamio.dbo.memberform

syntax may be off as from memory but merge will do this

MERGE 
   table2 AS target
USING 
   table1 AS source
ON 
   target.id = source.id 
WHEN MATCHED THEN 
   UPDATE SET target.name = source.name
WHEN NOT MATCHED THEN 
   INSERT (id, name) VALUES (source.id, source.name);