ms sql insert with select and then update in the same command
Posted on 2009-05-12
Im creating a VB.NET application with a MS SQL database. I am trying to copy an undetermined number of rows to the same table and whilst at the same time change one value.
My code works perfectly as long as i dont try to set a manual value for one of the columns. If i try to do this i get an error saying the select part of the command must have the same amount of columns as the insert part. Is there a way to avoid this?
Possibly by updating each row as soon as its added? baring in mind this statement will usually copy between 2-5 rows.
'copy core ff Previous Address history to new sale
Dim objCommandCopyPA As SqlCommand = New SqlCommand()
objCommandCopyPA.Connection = objConnection
objCommandCopyPA.CommandText = "INSERT INTO previous_address (pa_sale_id, pa_client_id," & _
"pa_address1, pa_address2, pa_town, pa_postcode, pa_years, pa_months, pa_start, pa_end) " & _
"SELECT '" & IDone & "',pa_client_id, pa_address1, pa_address2, pa_town, pa_postcode," & _
"pa_years, pa_months, pa_start, pa_end " & _
"FROM previous_address WHERE (pa_sale_id = 0) AND (pa_client_id = '" & app1_id & "' OR " & _
"pa_client_id = '" & app2_id & "'); " & _
Dim cPAID As Integer
cPAID = objCommandCopyPA.ExecuteScalar()
Catch SqlExceptionErr As Exception