Solved

Trying to update a column in SQL 2005 from excel spread

Posted on 2014-03-24
10
890 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

729 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