CODEWITHSUNDEEP
sqlsql-server
SuicideSheep
SuicideSheep

Reputation: 5550

SQL Server possible to create temp table with all columns nullable automatically?

By using the following query, temporary table testingTemp will be created and the definition of columns are depending on oriTable.(I guess)

Select a.ID AS [a], 
       b.ID AS [b], 
       c.ID AS [c]
INTO #testingTemp
FROM
oriTable

And I have my second query as below:

Insert into #testingTemp (c)
Select z.ID AS [c]
FROM
oriTable

Now when I execute second query, SQL Server complaint 

Cannot insert the value NULL into column 'a' , table 'tempdb.dbo.testingTemp...blabla

May I know how to solve this problem without changing oriTable structure?

Upvotes: 1

Views: 2885

Answers (3)

Kishore
Kishore

Reputation: 846

Try This :

During first insert try to convert the columns with constraint (primary key or not null) to their respective data types.

Select convert(int,a.ID) AS [a], 
       b.ID AS [b], 
       c.ID AS [c]
INTO #testingTemp
FROM
oriTable

since we used convert function the constraint structure (primary or not null constraint) is not copied to the temp table. and now you can gently fire the second insert statement and it will execute without any error.

Insert into #testingTemp (c)
Select z.ID AS [c]
FROM
oriTable

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453920

Use

SELECT ID + 0 AS [a],
       ID + 0 AS [b],
       ID + 0 AS [c]
INTO   #testingTemp
FROM   oriTable

The nullability of columns computed via an expression is almost always assumed to be NULL rather than NOT NULL

Upvotes: 5

Marciano.Andrade
Marciano.Andrade

Reputation: 306

try this:

ALTER TABLE #testingTemp ALTER COLUMN [a] int NULL

Upvotes: 0

Related Questions