I am attempting to write some data from one source to a SQL Server table using a Write Data In-DB tool leveraging an OLE DB connection. The Write Data In-DB tool is writing the data to a temp table and then attempting to append my target table CSC_FBI_BH. However, my target table (n columns) has two not null columns (UUID, Import Date) which usually generate automatically using default values in SQL Server when Inserting the remaining n-2 columns.
When leveraging the Write Data In-DB tool, I'm met with this error:
The issue is that the default Write Data in DB is using this structure:
WITH cte as
(SELECT * FROM #temp_table )
INSERT INTO target (null, null, col1, ..., coln)
There shouldn't be filler values at the beginning of the INSERT INTO statement as SQL will create a NULL value for the nullable columns. By inserting NULL values for the unspecified columns ahead of the columns in the source data -- it is throwing an exception even though there are default values for those unspecified columns in the target table.
This placeholder doesn't make sense because if a column can be NULL and you don't specify it in the INSERT statement, then it will default to NULL anyway. Example below:
Example of expected behavior:
@bhall48 Workaround: Add a column called "sol" and put the default value "sol4" and pass it into the tool Write Data In-DB tool.