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);

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!
************************************************
"Manan"  wrote in message 
news:1184066252.954823.103530@k79g2000hse.googlegroups.com...

> 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);
>