Update fails with Primary Key Violation

[Manan]

Hi,

i'm copying a table from one SQL server to another. here i'm just
selecting all rows from source table and selecting 0 row from dest
table just to create the commands for insert/update/delete etc...now
problem is when i do dataadapter.update it will try to insert all new
rows in dest table which is fine but what if i'm trying to insert 5
rows but 1st row violates the Primary Key constraint..in this case i
want to skip the 1st row and insert reamining 4 rows. can someone
guide me how to do this ?

Thanks.

below is the code snipset

string sourcequerystring = "select * from " + table + "
with(nolock) " + filter;
string destquerystring = "select top 0 * from " + table;

//get source records
SqlConnection srcconn = new SqlConnection(sourcedb);
SqlConnection destconn = new SqlConnection(destdb);
DataSet srcDataSet = new DataSet();
SqlCommand srcCommand = new SqlCommand(sourcequerystring,srcconn);
SqlDataAdapter srcDA = new SqlDataAdapter();
srcCommand.CommandTimeout = commandtimeout;
srcDA.SelectCommand = srcCommand;
srcDA.Fill(srcDataSet);

//insert to the destionation table
SqlCommand destCommand = new SqlCommand(destquerystring,destconn);
SqlDataAdapter destDA = new SqlDataAdapter();
destDA.SelectCommand = destCommand;
destCommand.CommandTimeout = commandtimeout;
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(destDA);
DataSet destDataSet = new DataSet();
destDA.Fill(destDataSet);
foreach(DataRow dr in srcDataSet.Tables[0].Rows)
{
DataRow dr1 = destDataSet.Tables[0].NewRow() ;
dr1.ItemArray = dr.ItemArray;
destDataSet.Tables[0].Rows.Add(dr1);
}
destDA.Update(destDataSet);

 

[Cowboy \(Gregory A. Beamer\)]

The SQL command is

INSERT INTO Table1
SELECT * FROM Table2 t2
LEFT JOIN Table1 t1
on t2.PK = t1.PK
WHERE t2.PK IS NULL

That avoids all records that can currently join. You can add the locking
hints back in.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com
Co-author: Microsoft Expression Web Bible (upcoming)

************************************************
Think outside the box!
************************************************