Copy rows from a table and then insert them with new values - MsSQL

[smartieoncomputer]

Hi,

I'm trying to copy from from one table in my database and then insert those rows with a value changed.

my table looks like this

DbID (float) | ClientID(float)
 556                 342
 556                 612
 446                 745
 142                 112
 142                 612
 142                 147
 142                 369
 223                 369

so i want to select all rows where DbID = 556
then with those selected rows modify DbID to say 557
and insert them into the table, and no i don't want to update those rows.

so my end result would look like this:

DbID (float) | ClientID(float)
 556                 342
 556                 612
 557                 342 // new row
 557                 612 // new row
 446                 745
 142                 112
 142                 612
 142                 147
 142                 369
 223                 369

You help would be appreciated

Thanks
Roman

[cloud255]

You haven't provided any C# code which may be giving you errors, so perhaps you meant to post this in the MySQL forum?

My SQL knowledge is not too great, but I believe you could create a temp table containing all the values which need to be updated, you can then execute a cursor on that table calling an insert statement for each row in the temp table to create a new entry in your original table.

If you did want to do it in C# you could always fill a Dictionary<stri ng,string> with the data, loop through it and send an INSERT statement to the DB for each element in the Dictionary<> (This is pretty much exactly the same as using the cursor in the DB).

[smartieoncomputer]

Well, I don't actually have any c# code for that matter.
The reason why i asked is because I'm close to clueless on how to do this.

I have already set up a connection with the server using 'SqlConnection' and can pass Quires using 'SqlCommand' with out any problems.

It would be nice if someone could give me an idea how to go with this and that would be great.

Cheers
Roman

P.S. its Microsoft SQL Server, not MySQL :)

[smartieoncomputer]

Ok, I came across a lead and had success.

for those with the same question here's the answer:

 
            SqlCommand cmd = new SqlCommand("SELECT * FROM [LnkClientDb] WHERE [DbID]=" + index.ToString() + ";", dbConn);
            SqlDataAdapter data = new SqlDataAdapter(cmd);

            data.Fill(temp);

            foreach (DataRow row in temp.Tables[0].Rows)
            {
                row["DbID"] = newIndex; // new index for the rows selected
            }

            SqlBulkCopy bulk = new SqlBulkCopy(dbConn); // dbConn is a SqlConnection
            bulk.DestinationTableName = "LnkClientDb"; 
            bulk.WriteToServer(temp.Tables[0]);

Cheers
Roman