Write Data In-DB (SQL Server) from Data Stream In tool not leveraging default value.

[bhall48]

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: 

Write Data In-DB (8) Executing PreSQL:

"WITH "Tool1_fb4b" AS (SELECT * FROM "##AYXcdab306ea9dfb51d05b53c2ef086a185")  INSERT INTO "CSC_FBI_BH" SELECT "Material", "Ma..." :

Microsoft OLE DB Provider for SQL Server: The statement has been terminated.\01000 = 3621;

Microsoft OLE DB Provider for SQL Server: Cannot insert the value NULL into column 'UUID', table 'XXXXXXXXXX.dbo.CSC_FBI_BH'; column does not allow nulls. INSERT fails.\23000 = 515

However, when I try to perform the same function within SQL itself from a temp table of the n-2 columns (no UUID, no Import Date), this isn't an issue. Generating the UUID in Designer Desktop isn't a problem, but I want to know exactly when data is posted to the table, and generating an Import Date within Alteryx isn't an option for that reason.

How can I get the Write Data In-DB function to leverage the default value set in the target table's design?

Image attached of the specified columns' design

[bhall48]

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:

CREATE TABLE #ayx_check (

    foo nvarchar(50) NULL,

    bar nvarchar(50) NULL,

    sol nvarchar(50) NOT NULL

)

INSERT INTO #ayx_check (foo, bar, sol)

VALUES

     ('foo1', 'bar1','sol1')

    ,('foo2', 'bar2','sol2')

    ,('foo3', 'bar3','sol3')

SELECT foo,bar,sol FROM #ayx_check

INSERT INTO #ayx_check (sol) VALUES ('sol4')

SELECT foo,bar,sol FROM #ayx_check

[bhall48]

Example of expected behavior:

DROP TABLE IF EXISTS #ayx_check

CREATE TABLE #ayx_check (

    foo nvarchar(50) NULL,

    bar nvarchar(50) NULL,

    sol nvarchar(50) NOT NULL DEFAULT 'sol4'

)

INSERT INTO #ayx_check (foo, bar, sol)

VALUES

     ('foo1', 'bar1','sol1')

    ,('foo2', 'bar2','sol2')

    ,('foo3', 'bar3','sol3')

SELECT foo,bar,sol FROM #ayx_check

INSERT INTO #ayx_check (foo, bar) VALUES ('foo4', 'bar4')

SELECT foo,bar,sol FROM #ayx_check

[ntobon]

@bhall48 Workaround: Add a column called "sol" and put the default value "sol4" and pass it into the tool Write Data In-DB tool.