Adding records to a table with selected columns
I would like to add records to an existing table but only with data in selected columns leaving the rest un-populated. Of the columns that are not included are ones that are specified as non-null, including some numeric fields.
In the past, I have been able to do this by using the Tasks -> Import Data wizard in SSMS. Suddenly, this has stopped working giving an error message about trying to put NULL data into a non-null column (see the error message in the 2nd image regarding the ADCOST column in the dbo.ADCOSTS table. The columns in green (commented out) are just my notes for which columns to include and are not part of any commands.
Something has changed that now prevents me from doing something that I had been for several years. Either that, or I've changed something, but I can't find what that would be.
I've attached a couple of images that might help. It shows the table to be appended and the columns that I wish to include.
IMG_6288.JPG
IMG_6290.JPG
In the past, I have been able to do this by using the Tasks -> Import Data wizard in SSMS. Suddenly, this has stopped working giving an error message about trying to put NULL data into a non-null column (see the error message in the 2nd image regarding the ADCOST column in the dbo.ADCOSTS table. The columns in green (commented out) are just my notes for which columns to include and are not part of any commands.
Something has changed that now prevents me from doing something that I had been for several years. Either that, or I've changed something, but I can't find what that would be.
I've attached a couple of images that might help. It shows the table to be appended and the columns that I wish to include.
IMG_6288.JPG
IMG_6290.JPG
Manju
can you try changing the selection from Fail to Ignore in "On Error" & "On Truncation" ? refer your 6288.jpg and re-run the package?
If the data you are adding has NULL then check for it before you load
if the data columns you are not adding forces one of those with a not null constraint, then it will error on insert and the row wont be added..
addate seems to be the only nullable column, so, you will have to supply values (apart from identities or have default values) for every other column.
apart from droping constraints, you dont have much choice. you will need to supply a value for every column bound by a 'not null' constraint.
if the data columns you are not adding forces one of those with a not null constraint, then it will error on insert and the row wont be added..
addate seems to be the only nullable column, so, you will have to supply values (apart from identities or have default values) for every other column.
apart from droping constraints, you dont have much choice. you will need to supply a value for every column bound by a 'not null' constraint.
ASKER
Regarding comment #1: I tried 'Ignore' for both settings but got the same result.
Regarding comment #2: The are no NULL data fields in the import file; I've manually retyped a new import file with one row to be sure that the data is clean.
And, I am certain that I've added rows with data columns that I'm not adding that are non null, but now that doesn't work. That is what is perplexing - I am confident that it worked for several years until last week, unless I'm hallucinating. - should it have ever worked?
Regarding comment #2: The are no NULL data fields in the import file; I've manually retyped a new import file with one row to be sure that the data is clean.
And, I am certain that I've added rows with data columns that I'm not adding that are non null, but now that doesn't work. That is what is perplexing - I am confident that it worked for several years until last week, unless I'm hallucinating. - should it have ever worked?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The issue may be related to missing data in the Constraints folder of the table in question; there are none.
I may have used an improper method to restore this table. I had deleted the original table and replaced it with a backup that I may have simply renamed from the backup file name to the original name. I had created the backup file using 'select * into....' which captured the data, including the field names and data types, but did not capture the info in the Constraints folder.
Can the Constraints Folder data be reconstructed?
I may have used an improper method to restore this table. I had deleted the original table and replaced it with a backup that I may have simply renamed from the backup file name to the original name. I had created the backup file using 'select * into....' which captured the data, including the field names and data types, but did not capture the info in the Constraints folder.
Can the Constraints Folder data be reconstructed?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The data types are set by the application and should not need to be changed. What appears to be missing is the data that was in the Constraints Folder, just for this one table, the rest of the database tables have not been effected.
So, my question is, how does one reconstruct the Constraints Folder data for a single table?
So, my question is, how does one reconstruct the Constraints Folder data for a single table?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.