December 16, 2005 at 3:26 pm
Hi experts,
How to insert data into a table which has a column with not null row_id (numeric) datatype?
December 16, 2005 at 3:29 pm
Important piece of info missing - is the column an Identity ?
December 16, 2005 at 3:39 pm
It's not an identity field.
Actually, I am using DTS load the summary data into a table which contains the row_id column and I received an error:
Cannot insert the value null into column RecID. Insert failed.
The RecID is the row_id column, not null, not an identity and it's a numeric field.
December 16, 2005 at 4:05 pm
??
Not sure what your question is then. If the column is not-null and not identity, then you have to insert something into it. What you insert into it depends on your project's requirements and business rules.
December 16, 2005 at 8:41 pm
Looks like your DTS is not providing the values for this column. What you can probably do (if you have the rights to) is to setup default on that column so it does insert some value automatically and does not violate the NOT NULL constraint.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
December 17, 2005 at 11:30 am
Defaults don't work that way. If an explicit NULL value is provided, that NULL value is used for doing the insert instead of the default value for that column. A default value for a column is used only when either that column is omitted from the insert list or the keyword DEFAULT is used for that column.
If the column is NOT NULL and not an identity, then the DTS package will have to handle this by providing a value for that column or if the idea is to have an auto-incremental field, change row_id column to be an identity column and omit it from the list when doing the inserts.
December 18, 2005 at 6:05 am
I said that it looks like the DTS does not provide the values for this column. In this case the default would work. If the NULL is forced down on a column that does not accept NULLs then this is a different story and the DTS load should be analyzed to figure out why does this happen. Since we don't know how the input data is created it might be one of the typical GIGO scenarios.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
December 19, 2005 at 8:14 am
My source table is a summary table, which contains Store_number, TotalAmt, Sales_Month, and Sales_Year. It doesn't have a Row_ID column.
The destination table is from database for a Real Estate application, the columns can't be added or altered. The destination table contains Row_ID, Store_Num, TotalAmt, Sales_Month and Sales_Year.
I beleive that I need to generate this row_id dynamically and have to find the orignal maximum row_id in table first, then insert row_id which starts from the max row_id incrementing by 1........
I don't know how to do it through DTS, please guide me. Thank you.
December 19, 2005 at 8:45 am
Why do you want to use DTS for this? Can you link the servers and the run an insert statement on the destination server. (I am assuming you have this on 2 separate SQL Servers). At least you will have easy access to the current Row_ID from the RealEstate application.
More background info on your setup will help.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
December 19, 2005 at 9:01 am
Infact, I am new to the DTS package.
I need to schedule the task to run for a monthly basis. These 2 servers are linked already.
And actually, I inserted data already. Now the DTS is for updating table.
Can you guide me how to use DTS for updating table?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply