asked on

Move Data from excel spreasheet to sql server 2008 table

I am trying to move data from an excel spreadsheet to a table in sql.
I have used the import export wizard to create an SSIS package.

When I run the package I get :

"Cannot insert the value NULL into column 'DisciplineMappingID', table 'ECARS.dbo.DISCIPLINE_MAPPING'; column does not allow nulls. INSERT fails.".
(SQL Server Import and Export Wizard)

The column 'DisciplineMappingID', is the primary key of the table, but it is not an identity column. It is not nullable. Has a type of int.

This table was created by an outside consultation firm as a part of a new system, and I have to accept the table as it was designed, I cannot change it.

-----------------------------------------------------------------------

Also,

There is one column in the excel spreadsheet that has a column, that
Needs to be split into two different fields in the table on sql.

Not sure how I can use the import export wizard to do this.

Thanks in Advance&

tpi007

The following would be an option to avoid complications using IMPORT/EXPORT wizard. For the column DisciplineMappingID. The quickest way will be too find max value of DisciplineMappingID in database and populate the excel sheet column with values increasing from this value; this assumes that the value that is inserted is not generated via application code or uses a table in the databases to assign new DisciplineMappingID values to this table. I would probable perform the split on the data once it is in the database using SQL if the column size permits that it is going in before split.
I would alterntaively upload excel to a temp table in database excluding DisciplineMappingID column. Add new column DisciplineMappingID to temp table ; add identify column setting the seed as max value in target table + 1. This will set value sequence; this assumes that the value that is inserted is not generated via application code or uses a table in the databases to assign new DisciplineMappingID values.

PedroCGD

Attache an excel sample and the create table script in SQL to better help you!

Regards,
Pedro
www.pedrocgd.blogspot.com
www.BIResort.net

ASKER CERTIFIED SOLUTION

misdevelopers

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial