Inserting a Row of Default Values into a Temporary Table

Question

I'm attempting to select a row of default values from a table. However, I don't want to create a new row in my original table. My original table has non-null default values (all blanks or zeros) defined for all columns (except for the one unique identifier). I begin by creating the temporary table:

SELECT
   TOP 0 *
INTO
   #Table
FROM
   Database.dbo.Table

Then I examine the empty table:

SELECT
   *
FROM
   #Table

Everything looks fine so far. There are no rows, but I can see all the columns from my original table. Then, I attempt to insert a single row into the table with default values for all the columns:

INSERT INTO
   #Table
DEFAULT VALUES

Rather than success, I get the following error:

Cannot insert the value NULL into column 'Column',
   table 'tempdb.dbo.#Table___0001A';
   column does not allow nulls. INSERT fails.

I next tried to insert a row with just one field defined.

INSERT INTO
   #Table
   (Column)
VALUES
   ('Value')

Same results. It appears that the definitions of the column default values from my original table were not included in the creation of the temporary table. Any suggestions?

Answer 1

When you create a temp table via SELECT ... INTO #Table the temp table gets all columns from the main table, but no constraints or indexes.

Obviously, you can explicitly create temp table with all necessary constraints.

One more option is to actually insert a row into the main table, let the engine populate it with default values, then read inserted values and insert them into the temp table. All this in a transaction. Then rollback the transaction, so that the main table remains as it was.

To make it work you need to use table variable instead of temp table, though, because temp tables participate in transactions as normal tables, but table variables don't. It means that you have to define the table variable in advance, so you need to know what columns your original table has. But, at least, you don't have to know the definition of default constraints.

Also, if your main table has an INDENTITY column, this insert-rollback will create a gap in identity values.

Sample table

CREATE TABLE [dbo].[MainTable](
    [Col1] [int] NOT NULL,
    [Col2] [nvarchar](50) NOT NULL,
    [Col3] [date] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MainTable] ADD  CONSTRAINT [DF_MainTable_Col1]  
DEFAULT ((123)) FOR [Col1]
GO

ALTER TABLE [dbo].[MainTable] ADD  CONSTRAINT [DF_MainTable_Col2]  
DEFAULT ('qwerty') FOR [Col2]
GO

ALTER TABLE [dbo].[MainTable] ADD  CONSTRAINT [DF_MainTable_Col3]  
DEFAULT (getdate()) FOR [Col3]
GO

Query

DECLARE @T TABLE (Col1 int, Col2 nvarchar(50), Col3 date);

BEGIN TRANSACTION;

INSERT INTO dbo.MainTable
OUTPUT inserted.Col1, inserted.Col2, inserted.Col3
INTO @T (Col1, Col2, Col3)
DEFAULT VALUES;

ROLLBACK TRANSACTION;

SELECT *
FROM @T;

Result

+------+--------+------------+
| Col1 |  Col2  |    Col3    |
+------+--------+------------+
|  123 | qwerty | 2017-08-29 |
+------+--------+------------+

Answer 2

Build your row from the temptdb catalog?

 SELECT c.name                                                                      AS 'Colunmn'
,      TYPE_NAME(c.user_type_id)                                                    AS 'Type'
,      CASE c.is_nullable WHEN 0 THEN 'No Nulls'
                                 ELSE '' END                                        AS 'Nullable'
,      CASE c.default_object_id WHEN 0 THEN ''
                                       ELSE 'Y' END                                 AS 'Has_Default'
,      dft.definition                                                               as Default_Value
,      CASE ISNULL(c.max_length, -1) WHEN -1 THEN 'Variable'
                                             ELSE CAST(c.max_length AS VARCHAR) END AS 'Length'
,      ISNULL('['+OBJECT_NAME(fkc.referenced_object_id)+'].['+Cref.name+']', ' ')   AS ForeignKeyInto
FROM      tempdb.sys.tables              t   
JOIN      tempdb.sys.columns             c    ON t.object_id = c.object_id
LEFT JOIN tempdb.sys.foreign_key_columns FKC  ON c.object_id = fkc.Parent_object_id
        AND fkc.parent_column_id = c.column_id
LEFT JOIN tempdb.sys.columns             cref ON fkc.referenced_column_id = cref.column_id
        AND fkc.referenced_object_id = cref.object_id
left join tempdb.sys.default_constraints dft  on c.default_object_id = dft.object_id
WHERE t.name like '#temp%'
ORDER BY t.name
,        c.name;

Answer 3

You need to create your temp table and include the default definitions in it.

--Check to see if table exists
--If it does, drop it
IF OBJECT_ID('tempdb.dbo.#Table', 'U') IS NOT NULL
    DROP TABLE #Table

--Create temp table with default value
CREATE TABLE #Table
    (
         columnA INT DEFAULT (1),
         columnB INT DEFAULT (2),
         columnC INT DEFAULT (3)
     )

--Insert a row of default values
INSERT INTO
#Table
DEFAULT VALUES

--See it
SELECT *
FROM #Table

--Drop temp table after you are done
DROP TABLE #Table