Solved

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

Posted on 2009-05-12
13
673 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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 500 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

688 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