How can I ignore default value on SQL insert statement?

Question

I've already set default value for some of my columns.

CREATE TABLE [dbo].[TEST]
(
    [id] [INT] NULL,
    [fname] [VARCHAR](50) NULL,
    [lname] [VARCHAR](50) NULL,
    [default1] [VARCHAR](50) NULL,
    [default2] [VARCHAR](50) NULL,
    [default3] [VARCHAR](50) NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[TEST] 
    ADD CONSTRAINT [DF_TEST_job] DEFAULT ('test1') FOR [default1]

ALTER TABLE [dbo].[TEST] 
    ADD CONSTRAINT [DF_TEST_default2] DEFAULT ('test2') FOR [default2]

ALTER TABLE [dbo].[TEST] 
    ADD CONSTRAINT [DF_TEST_default3] DEFAULT ('test3') FOR [default3]

Now I'm going to set all columns with default values to null in my INSERT statement sometimes.

I know that if I provide values for the columns in the INSERT, their defaults will be ignored. But I'm looking for a way to set default values off on some inserts. I know that it might be impossible, but I want to learn it's way (if there is.

Answer 1

I don't fully understand what you're trying to ask here....

If you provide a value for a column - even if it's NULL, in your INSERT statement, then SQL Server will use that provided value and skip any default value defined on the column.

So by just providing NULL, you're basically doing what your title asks: ignore the default value of the column.... the default value of a column is only used if you omit that column entirely from your INSERT statement....