• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 920
  • Last Modified:

Trying to update a column in SQL 2005 from excel spread

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
AccordJ35
Asked:
AccordJ35
  • 5
  • 3
  • 2
1 Solution
 
BlueYonderCommented:
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
 
AccordJ35Author Commented:
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
 
BlueYonderCommented:
Remove "Append rows to the destination table."  Appending adds new rows, but if an entity_id is specified then the row already exists.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
AccordJ35Author Commented:
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
 
englanddgCommented:
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
 
AccordJ35Author Commented:
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
 
englanddgCommented:
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
 
AccordJ35Author Commented:
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
 
englanddgCommented:
Ah.  Nice thinking!
0
 
AccordJ35Author Commented:
Was best solutiom for my needs
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now