Solved

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

Posted on 2009-05-12
13
669 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
  • 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
 

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Select - Help with CASE 4 42
tempdb latch contention 12 47
SQL Error in WHERE Clause 5 39
Sql Join Problem 2 25
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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.

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now