Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SELECT ... INTO NewTable without nulls Expand / Collapse
Author
Message
Posted Monday, August 05, 2013 12:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 10, 2014 9:03 AM
Points: 103, Visits: 289
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?

Thank you,

Amy

Post #1481028
Posted Monday, August 05, 2013 12:39 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:31 PM
Points: 11,949, Visits: 10,982
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?


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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1481046
Posted Monday, August 05, 2013 4:32 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 2:03 PM
Points: 500, Visits: 2,290
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.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1481117
Posted Monday, August 05, 2013 5:51 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:13 PM
Points: 1,651, Visits: 5,199
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?

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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1481124
    Posted Tuesday, August 06, 2013 2:45 PM
    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 12:59 PM
    Points: 1,734, Visits: 2,534
    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?

    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)
    I'm not fat, I'm gravity challenged.
    Post #1481565
    Posted Tuesday, August 06, 2013 4:11 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 6:13 PM
    Points: 1,651, Visits: 5,199
    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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1481600
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse