indentity problem when using data transformation services import expor

[sugu]

HI,

I'm trying to import data from excel to sql table using the data transformation services import export wizard. In the sql table, i have a id column which is set to be identity(1, 1). But, in the excel sheet, there is no id coulmn so i inserted a new column and named it as id (the rows are empty).

When i try to import, i'm getting error since the Id column is set to non null. I tried to remove the identity n set it as null and the imported. But the Id column in sql table is all <null>. How do i have data in the Id column other than manually entering the data in the Id column after importing?

thanks!

sugu

 

[bmpsu]

If the excel sheet does not contain 'ID' and it should because it is part of your database table...
depending on if you are assigning these values...
you could just give the ID column generic values in excel before you import.
You could make the excel column text and number it per row (1,2,3...).

If I am following correctly...

 

[sugu]

Hi,


yeah, thats what i'm doing now...manually typiing in the value in the ID in excel.

Thanks!

but just wondering if there is any other better wat to do it..eg by stored procedures.

-su

 

[bmpsu]

If you are assigning these values; in excel you can use its auto number feature. The first few rows you can number 1,2,3 then highlight those numbers (ex. column A, Row 1,2, and 3) and move your mouse over the bottom right corner of the highlighted fields until it turns to a plus/cross and then click and drag until you reach the last row. It will auto fill those rows numbering in increments of 1.

If the values are already assigned based off another table it is joined to, you could write a script to populate that column.

 

[sugu]

Thanks mate

-su