CODEWITHSUNDEEP
sql-servert-sql
mattmc3
mattmc3

Reputation: 18315

Create a nullable column using SQL Server SELECT INTO?

When I create a temp table using a select into in SQL Server, is there a way to specify that a column should be nullable? I have a multi-step process where I'm making a temp table by selecting a lot of columns (which is why I'm not doing a create table #tmp (...)). After I make that temp table, I'm updating some columns and some of those updates might null out a field.

I know I could do an alter table alter column statement to achieve what I want, but I'm curious about whether there's a way to specify this in the select itself. I know you can inline cast your columns to get the desired datatype, but I can't see how you specify nullability.

Upvotes: 43

Views: 81651

Answers (5)

wezzix
wezzix

Reputation: 2124

CONVERT will make your columns nullable, and works for literals/constants too. Tested in SQL Server 2005/2008.

SELECT 
    SomeText = CONVERT(varchar(10), 'literal'),
    SomeNumber = CONVERT(int, 0)
INTO SO5465245

INSERT SO5465245 VALUES (null, null)

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'SO5465245'
ORDER BY TABLE_NAME, ORDINAL_POSITION

DROP TABLE SO5465245

Upvotes: 7

SantoshG
SantoshG

Reputation: 11

If you want to inherit nullablity for the destination column irrespective of the source table columns you can follow this query.

SELECT COLUMN1, COLUMN2, COLUMN3 INTO DestinationTable from SourceTable

if this was your query where COLUMN1,COLUMN2,COLUMN3 were not nullable in SourceTable then change the query as

SELECT NULL COLUMN1, NULL COLUMN2, NULL COLUMN3 INTO DestinationTable from SourceTable

so, this will allow you to insert null values in to the Destination table.

Upvotes: 1

Kuba Wyrostek
Kuba Wyrostek

Reputation: 6221

This soulution I've recently come up with and though I should share:

select top 0
  B.*
into
  TargetTable
from
  SourceTable as A
    left join SourceTable as B on 1 = 0

This effectively creates a duplicated structure of SourceTable in TargetTable with all columns nullable (at least in sql2008).

Upvotes: 20

David P Reynevich
David P Reynevich

Reputation: 1

I recently had the same issue - I wanted to use "select into", wanted all columns in the target table to be nullable & a repeatable approach where I didn't have to know the names of the fields in the source table.

select *
into dbo.I_Data
from
  (select 1[Z_1]) A
  full join (select null[Z_2], * from dbo.S_Data) B on A.Z_1 = B.Z_2

where dbo.S_Data is the source data table and [Z_1] & [Z_2] are two dummy columns used for the join

Then to clean up:

(a) Remove the row of nulls

delete dbo.I_Data where [Z_1] = 1

(b) Remove the dummy fields:

alter table dbo.I_Data 
  drop column [Z_1], [Z_2]

Regards.

Upvotes: 0

Cade Roux
Cade Roux

Reputation: 89651

Nullability is inherited from the source column.

You can lose or gain nullability with an expression:

Example (constant literals appear to be problematic - need a good NOOP function which can return NULL):

CREATE TABLE SO5465245_IN
    (
     a INT NOT NULL
    ,b INT NULL
    ) ;
GO

SELECT  COALESCE(a, NULL) AS a
       ,ISNULL(b, 0) AS b
       ,COALESCE(10, NULL) AS c1
       ,COALESCE(ABS(10), NULL) AS c2
       ,CASE WHEN COALESCE(10, NULL) IS NOT NULL THEN COALESCE(10, NULL) ELSE NULL END AS c3
INTO    SO5465245_OUT
FROM    SO5465245_IN ;
GO

SELECT  TABLE_NAME
       ,COLUMN_NAME
       ,IS_NULLABLE
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_NAME LIKE 'SO5465245%'
ORDER BY TABLE_NAME
       ,ORDINAL_POSITION ;
GO

DROP TABLE SO5465245_IN ;
GO

DROP TABLE SO5465245_OUT ;
GO

Upvotes: 32

Related Questions