?
Solved

ms sql insert with select and then update in the same command

Posted on 2009-05-12
13
Medium Priority
?
675 Views
Last Modified: 2012-05-06
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 & "'); " & _
          "Select Scope_Identity()"
            Dim cPAID As Integer

            Try
                cPAID = objCommandCopyPA.ExecuteScalar()
            Catch SqlExceptionErr As Exception
                MessageBox.Show(SqlExceptionErr.Message)
            End Try
0
Comment
Question by:drews1f
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
13 Comments
 
LVL 15

Expert Comment

by:rob_farley
ID: 24365077
Are you trying to insert a value into the Identity column?

Also, watch out for SQL Injection issues... for more on this, check out http://xkcd.com/327

Rob
0
 

Author Comment

by:drews1f
ID: 24365123
no its not an identity column :(
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24365203
What's a "manual value"?

Are you having trouble with values that have apostrophes in them?

Rob
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:drews1f
ID: 24365272
nope the manual value is a variable which is defined earlier in the form.
If i change the statement to set the pa_sale_id value from the select statement it works fine.
however when i remove pa_sale_id and replace it with '" & IDone & "'

i get the error saying the select and insert statements need to have the same amount of columns :S
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24365380
So what is IDone set to?
0
 

Author Comment

by:drews1f
ID: 24365395
an integer. in this particular case it was

74
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24365530
Put a breakpoint in, grab the command, and run it in SSMS. That might give you some more clues.

Rob
0
 

Author Comment

by:drews1f
ID: 24365589
error from SSMS:

Msg 120, Level 15, State 1, Line 1
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
0
 

Author Comment

by:drews1f
ID: 24365592
command used:

INSERT INTO debt (debt_sale_id, debt_client_id_type,
debt_client_id1, debt_client_id2, debt_client_id3, debt_client_id4, debt_type, debt_company,
debt_add, debt_ceasing, debt_balance, debt_cleared_monthly, debt_repayments, debt_ends,
debt_prop_value, debt_rental, debt_no, debt_prop_type, debt_tenancies, debt_tenancy,
debt_prop_address1, debt_prop_address2, debt_prop_town, debt_prop_postcode)
SELECT '77',debt_client_id_type, debt_client_id1, debt_client_id2 debt_client_id3,
debt_client_id4, debt_type, debt_company, debt_add, debt_ceasing, debt_balance, debt_cleared_monthly,
debt_repayments, debt_ends, debt_prop_value, debt_rental, debt_no, debt_prop_type,
debt_tenancies, debt_tenancy, debt_prop_address1, debt_prop_address2, debt_prop_town, debt_prop_postcode
FROM debt WHERE (debt_sale_id = 0) AND (debt_client_id1 = '835' OR
debt_client_id1 = '2999');
Select Scope_Identity()
0
 

Author Comment

by:drews1f
ID: 24365642
I think i need to ignore the debt_sale_id column and update it once the new row is created.
If i was just copying one row i could do this fine - but i dont know how i can do this for multiple rows.
0
 
LVL 2

Expert Comment

by:dzex13
ID: 24367173
the problem, most likely in quotes - remove quotes surrounding '77':

INSERT INTO debt (debt_sale_id, debt_client_id_type,
debt_client_id1, debt_client_id2, debt_client_id3, debt_client_id4, debt_type, debt_company,
debt_add, debt_ceasing, debt_balance, debt_cleared_monthly, debt_repayments, debt_ends,
debt_prop_value, debt_rental, debt_no, debt_prop_type, debt_tenancies, debt_tenancy,
debt_prop_address1, debt_prop_address2, debt_prop_town, debt_prop_postcode)
SELECT 77,debt_client_id_type, debt_client_id1, debt_client_id2 debt_client_id3,
debt_client_id4, debt_type, debt_company, debt_add, debt_ceasing, debt_balance, debt_cleared_monthly,
debt_repayments, debt_ends, debt_prop_value, debt_rental, debt_no, debt_prop_type,
debt_tenancies, debt_tenancy, debt_prop_address1, debt_prop_address2, debt_prop_town, debt_prop_postcode
FROM debt WHERE (debt_sale_id = 0) AND (debt_client_id1 = '835' OR
debt_client_id1 = '2999');
Select Scope_Identity()
0
 
LVL 15

Accepted Solution

by:
rob_farley earned 2000 total points
ID: 24371315
Ok... you're missing a comma in the SELECT statement:

debt_client_id2 debt_client_id3

Rob
0
 

Author Comment

by:drews1f
ID: 24372941
hah yeh - missing comma! my bad!
thanks
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question