Allow Nulls with "SELECT .. INTO"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Allow Nulls with "SELECT .. INTO"

Author

Topic

SHardy
Starting Member

35 Posts

Posted - 2006-04-20 : 08:15:03

Hi,

I have a SQL statement that is of the format "SELECT ... INTO", in order to create a new table. There will then be another SQL statement to append some more data on to the end of the table.

As an example, the field "columnX" may contain a value in ALL records returned by the first statement, but some nulls may be returned by the second statement.

The table created by the 1st statement only sets the fields to "ALLOW NULLS" if there are nulls in the "SELECT ... INTO" results. Therefore I get an error when trying to run the 2nd statement because nulls are not allowed.

Within the "SELECT ... INTO" statement, how can I force a field to "allow nulls", even if there are no nulls returned in that statement's results?

Thanks,
Simon

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-20 : 08:29:46

Hi Simon,

I guess you can insert a null row the first time and then delete it, but surely it's better just to create the table you want before you insert any rows. Well, anyway, see the examples below...

--try 1 - doesn't work (as you've seen)
if object_id('tempdb.dbo.#t1') is not null drop table #t1
select 'a' as c1, 'b' as c2 into #t1
insert #t1 select 'c', null

select * from #t1

--try 2 - works but is not pretty
if object_id('tempdb.dbo.#t2') is not null drop table #t2
select * into #t2 from (select 'a' as c1, 'b' as c2 union all select null, null) a 
delete from #t2 where c1 is null and c2 is null
insert #t2 select 'c', null

select * from #t2

--try 3 - surely preferable?
if object_id('tempdb.dbo.#t3') is not null drop table #t3
create table #t3 (c1 varchar(10), c2 varchar(10))
insert #t3 select 'a', 'b'
insert #t3 select 'c', null

select * from #t3

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-20 : 08:35:24

U mean u want to change the attribute of a column when it is created by Select ... Into ... ?
I doubt it!!

I can give u a work around:

If ur intention is to create the table & add the records only, u can create the table as u wish
(with whatever the conditions for columns as allow null or not ...)
U may done so by using either of the following ways

Select ... Into Statement with ISNULL() to create the table
Alter table with appropriate needs
Use the Query
Insert Into ... Select .... From

Or else give the shortened version (if the real one is too complicated) of ur queries with some sample data?

Srinika

Kristen
Test

22859 Posts

Posted - 2006-04-20 : 09:01:49

This maybe?

SELECT Column1, CONVERT(varchar(2000), NULL) AS Column2, Column3, ... etc ...
INTO MyTargetTable
FROM MySourceTable
WHERE 1=0

That will create the table for you. Then do

INSERT INTO MyTargetTable
SELECT *
FROM MySourceTable
WHERE ... some criteria ...

Kristen

SHardy
Starting Member

35 Posts

Posted - 2006-04-20 : 09:28:31

Obviously I'm incapable of thinking today. So the common consensus is to create the table first. This should have been soooo obvious. OK, thanks. I will just have to choose which way to go about it, but it is all straightforward.

Thanks for helping a dunce. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources