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
Solved

Trying to update a column in SQL 2005 from excel spread

Posted on 2014-03-24
10
881 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
  • 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
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.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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

791 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