SELECT ... INTO NewTable without nulls

Question

SELECT ... INTO NewTable without nulls

Amy.G

SSCrazy

Points: 2305
More actions
August 5, 2013 at 12:05 pm

#297368

I am creating a table by using the
SELECT column1, column2
INTO NewTable
FROM OldTable
method. Is there a way of creating a table in this manner and saying if the columns should be null or not null? Currently I am just using an alter statement to change a column from null to NOT NULL, but was wondering if there is a way to eliminate this step.
Relatedly, can anyone think of a way for me to stop getting obsessed with ways of cutting out 1-2 lines of code and wasting all this time figuring out things I've already found solutions for? :crazy:
Thank you,
Amy
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Amy.G (8/5/2013)
I am creating a table by using the
SELECT column1, column2
INTO NewTable
FROM OldTable
method. Is there a way of creating a table in this manner and saying if the columns should be null or not null? Currently I am just using an alter statement to change a column from null to NOT NULL, but was wondering if there is a way to eliminate this step.

It will pick up the nullability of the table it is selecting from.

Relatedly, can anyone think of a way for me to stop getting obsessed with ways of cutting out 1-2 lines of code and wasting all this time figuring out things I've already found solutions for? :crazy:

All I can say there is that the least number of lines of code does not mean it is the best way to do something. Maybe you can change your OCD temperament to be worried about performance instead of line count. 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

You could do something like this:

USE tempdb;

DECLARE @source_table varchar(100)='sys.all_columns',--Source Table

@dest_table varchar(100)='new_table',--destination table (created by SELECT INTO)

@column varchar(100)='is_column_set',--column to swich to NOT NULL

@sql_prep varchar(1000),

@insert_sql varchar(1000),

@alter_sql varchar(1000),

@data_type varchar(100);

SET @sql_prep='IF OBJECT_ID('''+DB_NAME()+'..'+@dest_table+''')'+' IS NOT NULL DROP TABLE '+@dest_table;

EXEC(@sql_prep);

SET @insert_sql='SELECT * INTO '+@dest_table+' FROM '+@source_table;

EXEC(@insert_sql);

SELECT @data_type=DATA_TYPE+

CASE

WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN ''

ELSE '('+CAST(CHARACTER_MAXIMUM_LENGTH AS varchar(5))+')'

END

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME=@dest_table

AND COLUMN_NAME=@column;

SET @alter_sql='ALTER TABLE '+@dest_table+' ALTER COLUMN '+@column+' '+@data_type+' NOT NULL'

EXEC(@alter_sql);

You would supply the values for @source_table, @dest_table & @column. Obviously this script, as is, will only work for one column but you could easily update it to handle more.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

Amy.G (8/5/2013)
I am creating a table by using the
SELECT column1, column2
INTO NewTable
FROM OldTable
method. Is there a way of creating a table in this manner and saying if the columns should be null or not null? Currently I am just using an alter statement to change a column from null to NOT NULL, but was wondering if there is a way to eliminate this step.
Relatedly, can anyone think of a way for me to stop getting obsessed with ways of cutting out 1-2 lines of code and wasting all this time figuring out things I've already found solutions for? :crazy:
Thank you,
Amy

Firstly,

SELECT ISNULL(column1,'') as column1, column2

INTO NewTable

FROM OldTable

column2 is now nullable, column1 is not.

Second question: Yes, spend some quality time answering other peoples obsessive work avoidance questions, then you can at least pretend you are being useful in some way 😀

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

ScottPletcher SSC Guru Points: 101097 More actions · Answer 4

mister.magoo (8/5/2013)
Amy.G (8/5/2013)
I am creating a table by using the
SELECT column1, column2
INTO NewTable
FROM OldTable
method. Is there a way of creating a table in this manner and saying if the columns should be null or not null? Currently I am just using an alter statement to change a column from null to NOT NULL, but was wondering if there is a way to eliminate this step.
Relatedly, can anyone think of a way for me to stop getting obsessed with ways of cutting out 1-2 lines of code and wasting all this time figuring out things I've already found solutions for? :crazy:
Thank you,
Amy
Firstly,
SELECT ISNULL(column1,'') as column1, column2
INTO NewTable
FROM OldTable
column2 is now nullable, column1 is not.
Second question: Yes, spend some quality time answering other peoples obsessive work avoidance questions, then you can at least pretend you are being useful in some way 😀

Column2 is not nullable if it was not nullable in the original table.

I don't know of any way to make a column nullable that was "not nullable" in the source table for a SELECT ... INTO.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

ScottPletcher (8/6/2013)
Column2 is not nullable if it was not nullable in the original table.

Actually, yes you are right - that was imprecise of me - thanks for picking up on that 🙂

I don't know of any way to make a column nullable that was "not nullable" in the source table for a SELECT ... INTO.

use tempdb;

create table test(id int identity(1,1) not null, col1 varchar(10) null,col2 datetime not null);

select id,col1,nullif(col2,0) col2

into test2

from test;

select name,is_nullable

from sys.columns

where object_id = object_id('test2');

drop table test2;

drop table test;

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);