[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Trying to update a column in SQL 2005 from excel spread

Posted on 2014-03-24
10
Medium Priority
?
902 Views
Last Modified: 2014-03-29
Im using the MS Sql Server management Studio and doing a Task>Import>Excel

I have a table name "Customer" in that table I have a column "Discount_code"
In my spreadsheet I have a column for ID and a column for Discount code. When i try and import it I get this error.

 Copying to [RIPUSTST].[dbo].[CUSTOMER] (Error)
Messages
Information 0x402090df: Data Flow Task: The final commit for the data insertion has started.
 (SQL Server Import and Export Wizard)
 
Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "The statement has been terminated.".
An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Cannot insert the value NULL into column 'ENTITY_ID', table 'RIPUSTST.dbo.CUSTOMER'; column does not allow nulls. INSERT fails.".
 (SQL Server Import and Export Wizard)
 
Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047022: Data Flow Task: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Destination - CUSTOMER" (20) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED.  Thread "WorkThread0" has exited with error code 0xC0202009.  There may be error messages posted before this with more information on why the thread has exited.
 (SQL Server Import and Export Wizard)
0
Comment
Question by:AccordJ35
[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
  • 5
  • 3
  • 2
10 Comments
 
LVL 9

Expert Comment

by:BlueYonder
ID: 39951487
The first option is to enable identity insert in column mappings and then insert.

The second option is to insert without the ENTITY_ID column.  The ENTITY_ID column is a probably a unique identifier.
0
 

Author Comment

by:AccordJ35
ID: 39951509
So I tried exporting all columns in the customer table. Then make my changes to the discount_code there. Then tried to reupload since I think in the code is missing a column "Entity_ID" I get a new error. The option i have selected is "Append r ows to the destination table" and "Enable identity insert"

- Copying to [RIPUSTST].[dbo].[CUSTOMER] (Error)
Messages
Information 0x402090df: Data Flow Task: The final commit for the data insertion has started.
 (SQL Server Import and Export Wizard)
 
Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "The statement has been terminated.".
An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Violation of PRIMARY KEY constraint 'PK_CUSTOMER'. Cannot insert duplicate key in object 'dbo.CUSTOMER'.".
 (SQL Server Import and Export Wizard)
 
Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047022: Data Flow Task: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Destination - CUSTOMER" (461) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED.  Thread "WorkThread0" has exited with error code 0xC0202009.  There may be error messages posted before this with more information on why the thread has exited.
 (SQL Server Import and Export Wizard)
0
 
LVL 9

Expert Comment

by:BlueYonder
ID: 39951547
Remove "Append rows to the destination table."  Appending adds new rows, but if an entity_id is specified then the row already exists.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:AccordJ35
ID: 39951573
Would the Discount_code being a Foreign Key have anything to do with the errors im getting? Nothing seems to be working. I tried unselecting append and other option is delete. That bring up a whole new mess of errors.
FK
0
 
LVL 3

Expert Comment

by:englanddg
ID: 39951840
Have you tried using TSQL to do this?

I generally find it easier to export to csv from excel, and then use TSQL to import that csv into a Temporary Table and then run an update query against that table to update the primary table...

I may be missing something...but I don't see why that wouldn't work?
0
 

Accepted Solution

by:
AccordJ35 earned 0 total points
ID: 39952077
So I got it to work by making a sql statement for each record. I used excel to generate it.


Update Customer Set Discount_Code = 'value' where customer.id = 'customer id'
0
 
LVL 3

Expert Comment

by:englanddg
ID: 39952114
Yes, that would be the query, except where is your data source (the From statement)?

How did you use excel to generate that code?  It did it automatically?  I'd love to know about that feature!
0
 

Author Comment

by:AccordJ35
ID: 39952219
Excel Query builder
So how I used excel was since I already had my list of information. I pasted all my customer IDs in Column A. I then pasted the Discount code in column B. I then broken down the query so that excel can piece it back together

Update Customer Set Discount_Code = 'xxx' where customer.id = 'xxx';

became three separate columns. so

Column D was:   Update Customer Set Discount_Code = '
Column E was:    ' where customer.id = '
Column F was:    ';

Then in column H i put it all back together. by using a formula.
I created the first one manually then used dragging fill feature.

the forumula for first one is
=D3&B3&E3&A3&F3

You have to make sure you have a "sql build code" for every row or else it throws the excel dragging feature off because what it does it just put down the next sequence as you drag it.


=D3&B3&E3&A3&F3
=D4&B4&E4&A4&F4
=D5&B5&E5&A5&F5
=D6&B6&E6&A6&F6

once I had all the code I just pasted it into the SQL studio Management. :-)
I had about 4,000 accounts to update doing it this way took me about 10 minutes....after spending hours trying other options
0
 
LVL 3

Expert Comment

by:englanddg
ID: 39953103
Ah.  Nice thinking!
0
 

Author Closing Comment

by:AccordJ35
ID: 39963533
Was best solutiom for my needs
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

649 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