SELECT ... INTO NewTable without nulls

  • 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

  • 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/

  • 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

  • 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);

  • 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]

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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);

  • 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]

    Viewing 6 posts - 1 through 5 (of 5 total)

    You must be logged in to reply to this topic. Login to reply