remove allow nulls from column in SELECT INTO statement

Posted on 2011-10-03
Last Modified: 2012-05-12
I am working with SQL Server 2008 and am trying to create a table from another table using a SELECT INTO statement.  The only problem I am having is, I need to make sure the column does not allow null values.  As it is, when I run my query, all the columns allow nulls.  Is there a way to create the columns so that they do not allow nulls?

SELECT CAST(REPLACE(id,'"','') as INT) as id
INTO newtable
FROM oldtable

Open in new window

Question by:nickgross19
    LVL 32

    Accepted Solution

    I'm 95% sure you're going to have to follow it up with an ALTER TABLE statement:
    Alter Table newtable Alter Column id INT NOT NULL

    Open in new window

    LVL 8

    Expert Comment

    the problem here is: if in the old table exist null values and you dont want put in the new the you can filter

    select id into newtable from oldtable where id not is null

    or you can change the property of the oldtable to not allow null, but if the table contains data,can be a problem.

    LVL 8

    Expert Comment

    this is the behaviour

    other think you can specify identity

    Using select into with IDENTITY columns

    This section describes special rules for using the select into command with tables containing IDENTITY columns.
    Selecting an IDENTITY column into a new table

    To select an existing IDENTITY column into a new table, include the column name (or the syb_identity keyword) in the select statement's column_list:

    select column_list
         into table_name
         from table_name

    The following example creates a new table, stores_cal_pay30, based on columns from the stores_cal table:

    select record_id, stor_id, stor_name
    into stores_cal_pay30
    from stores_cal
    where payterms = "Net 30"

    The new column inherits the IDENTITY property, unless any of the following conditions is true:

        The IDENTITY column is selected more than once.

        The IDENTITY column is selected as part of an expression.

        The select statement contains a group by clause, aggregate function, union operator, or join.

    Selecting the IDENTITY column more than once

    A table cannot have more than one IDENTITY column. If an IDENTITY column is selected more than once, it is defined as NOT NULL in the new table. It does not inherit the IDENTITY property.

    In the following example, the record_id column, which is selected once by name and once by the syb_identity keyword, is defined as NOT NULL in stores_cal_pay60:

    select syb_identity, record_id, stor_id, stor_name
    into stores_cal_pay60
    from stores_cal
    where payterms = "Net 60"

    Adding a new IDENTITY column with select into

    To define a new IDENTITY column in a select into statement, add the column definition before the into clause. The definition includes the column's precision but not its scale:

    select column_list
         identity_column_name = identity(precision)
         into table_name
         from table_name

    The following example creates a new table, new_discounts, from the discounts table and adds a new IDENTITY column, id_col:

    select *, id_col=identity(5)
    into new_discounts
    from discounts

    If the column_list includes an existing IDENTITY column, and you add a description of a new IDENTITY column, the select into statement fails.
    Defining a column whose value must be computed

    IDENTITY column values are generated by Adaptive Server. New columns that are based on IDENTITY columns, but whose values must be computed rather than generated, cannot inherit the IDENTITY property.

    If a table's select statement includes an IDENTITY column as part of an expression, the resulting column value must be computed. The new column is created as NULL if any column in the expression allows a NULL value. Otherwise, it is NOT NULL.

    In the following example, the new_id column, which is computed by adding 1000 to the value of record_id, is created NOT NULL:

    select new_id = record_id + 1000, stor_name
    into new_stores
    from stores_cal

    Column values are also computed if the select statement contains a group by clause or aggregate function. If the IDENTITY column is the argument of the aggregate function, the resulting column is created NULL. Otherwise, it is NOT NULL.
    IDENTITY columns selected into tables with unions or joins

    The value of the IDENTITY column uniquely identifies each row in a table. However, if a table's select statement contains a union or join, individual rows can appear multiple times in the result set. An IDENTITY column that is selected into a table with a union or join does not retain the IDENTITY property. If the table contains the union of the IDENTITY column and a NULL column, the new column is defined as NULL. Otherwise, it is NOT NULL.;pt=22568

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now