Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

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

bhall48
6 - Meteoroid

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
 
3 REPLIES 3
bhall48
6 - Meteoroid

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
6 - Meteoroid

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
Alteryx
Alteryx

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

Labels