SQL Server Table Not Using Default Value

Question

I am populating a table using an SSIS package.

The idea is that whenever the package uploads to the table, it will timestamp the value using getdate().

My DDL looks like this when I open it up:

CREATE TABLE [REPORTING].[post_ssis_table_1](
    [validation_key] [int] IDENTITY(1,1) NOT NULL,
    [ssis_ran_date] [datetime] NULL,
    [server_name] [varchar](255) NULL,
    [data_base] [varchar](255) NULL,
--A bunch of other irrelevant columns
    [success_status] [varchar](255) NULL,
    [success_criteria] [varchar](255) NULL,
PRIMARY KEY CLUSTERED 
(
    [validation_key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [REPORTING].[post_ssis_tbl_1] ADD  DEFAULT (getdate()) FOR [ssis_ran_date]
GO

Note: I did not type the alter statement, that's literally what it looks like when I open up the DDL.

Why is the default value not populating for [ssis_ran_date] when I insert rows? It literally just stays null.

May we see the insert statement that causes the difficulty? — HABO
– HABO, Commented Jan 29, 2019 at 21:10
I'm using an SSIS package destination step. Also, someone posted an answer that solved my problem below — Chicken Sandwich No Pickles
– Chicken Sandwich No Pickles, Commented Jan 30, 2019 at 14:24
I'm not sure why someone would vote to mark this closed . . . — Chicken Sandwich No Pickles
– Chicken Sandwich No Pickles, Commented Jan 30, 2019 at 14:24
There is no insert statement. I'm using SSIS. I am not inserting data into that column. It was supposed to populate the date using the default constraint that I had placed and I didn't understand why it wasn't. Therefore, marking to close the ticket was incorrect. — Chicken Sandwich No Pickles
– Chicken Sandwich No Pickles, Commented Jan 30, 2019 at 17:11

Tab Alleman · Accepted Answer · 2019-01-29 20:16:16Z

10

If your INSERT statement specifies NULL for a column with a default, then NULL will get inserted.

The default will only be used when that column is not specified in the INSERT at all.

answered Jan 29, 2019 at 20:16

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Slava Murygin · Accepted Answer · 2019-01-29 20:55:59Z

0

Do not include "ssis_ran_date" column into your insert statement.

answered Jan 29, 2019 at 20:55

Slava Murygin

1,9551 gold badge11 silver badges10 bronze badges

Collectives™ on Stack Overflow

SQL Server Table Not Using Default Value

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related