• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 917
  • 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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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