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
makingitbigAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ManjuIT - Project ManagerCommented:
can you try changing the selection from Fail to Ignore in "On Error" & "On Truncation" ? refer your 6288.jpg and re-run the package?
0
Mark WillsTopic AdvisorCommented:
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.
0
makingitbigAuthor Commented:
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ManjuIT - Project ManagerCommented:
Technically if there's a field with "Not Null", then DTS package should give some data to that field while importing. Else the package will fail. I am not sure when you say it was working earlier. I doubt it would have run when you dont give any data to a "Non Null" field.
0
Arifhusen AnsariBusiness Intelligence Developer and AnalystCommented:
Hello makingitbig.

Manju is correct regarding data necessity for Not null field. You need to provide the data when you want to load column with Not Null.

May be in past it was working, because you might be providing some data like '' for text and 0 for numeric.

Let me know, if you need any other help.

Regards
Arif
0
makingitbigAuthor Commented:
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?
0
Mark WillsTopic AdvisorCommented:
Not easily, not now. And some of your difficulty could well be the result of "an improper method to restore this table."

Still, your table does have its own built-in constraints of NOT NULL.

You can ALTER TABLE yourtable ALTER COLUMN yourcolumn + datadefinition NULL

By way of example only... To change NOTE3 to be nullable...
ALTER TABLE ADCOSTS ALTER COLUMN NOTE3 char(70) NULL

Open in new window


see : https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql#alter_column
0
makingitbigAuthor Commented:
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?
0
Mark WillsTopic AdvisorCommented:
The data in the constraints folder is as a result of altering the table.

There isnt really a seperate "folder" per se. It is a navigation tool in SSMS. Technically, it is called a Node.

There are two ways to modify the table. One way is via T-SQL code - like the example in my previous post. The other is to use the "Table Designer" by right clicking on the table (in the object explorer on left hand side) and going into design.

That is certainly easier for something like allowing NULLs. because when you click on designer, it will open up on the right 'query pane' with all the columns visible. You will also see an entry on the top menu bar "table designer".

When you have finished making changes, close the table designer window and save the changes, back on the left hand side, you will need to right-click and refresh before any of those folders are populated.

There are some tutorials out there, and came across : https://www.quackit.com/sql_server/sql_server_2016/tutorial/create_a_table_in_sql_server_2016.cfm seems to have the basics covered with images...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSMS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.