• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 491
  • Last Modified:

remove allow nulls from column in SELECT INTO statement

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

  • 2
1 Solution
Daniel WilsonCommented:
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

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.

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.


Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now