mister.magoo (8/5/2013)
Amy.G (8/5/2013)
I am creating a table by using theSELECT 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".
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);
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply