Problem in creating nullable columns using SELECT INTO in SQL Serv

Hi Everyone!
I have a problem that is basically a database issue in SQL Server 2000
compared to Sybase 12. I am working on a application that should support bot
h
SYbase and SQL Server as backend. My code works fine in Sybase but not in SQ
L
Server. Following is the query that will give a an idea of the issue.
Query:
--
SELECT Null as c1,
isnull( NULL,0) as c2
INTO #temp_tbl
Above query creates both c1 and c2 as nullable columns in Sybase. The same
query in SQL Server creates C1 as nullable and C2 as not null. There are
situation that result in null data and cause inserting NULL into not columns
in SQL Server. Using SET ANSI_NULL_DFLT_ON ON also did not give the required
results. I am looking for a solution which would create c1 and c2 as nullabl
e
columns in SQL Server.
Please help in finding me a solution.
Thanks in advance.
SomeshSRV wrote:
> Hi Everyone!
> I have a problem that is basically a database issue in SQL Server 2000
> compared to Sybase 12. I am working on a application that should support b
oth
> SYbase and SQL Server as backend. My code works fine in Sybase but not in
SQL
> Server. Following is the query that will give a an idea of the issue.
> Query:
> --
> SELECT Null as c1,
> isnull( NULL,0) as c2
> INTO #temp_tbl
> Above query creates both c1 and c2 as nullable columns in Sybase. The same
> query in SQL Server creates C1 as nullable and C2 as not null. There are
> situation that result in null data and cause inserting NULL into not colum
ns
> in SQL Server. Using SET ANSI_NULL_DFLT_ON ON also did not give the requir
ed
> results. I am looking for a solution which would create c1 and c2 as nulla
ble
> columns in SQL Server.
> Please help in finding me a solution.
> Thanks in advance.
> Somesh
Like this:
CREATE TABLE #temp_tbl (c1 INTEGER NULL, c2 INTEGER NULL /* !!! NO
PRIMARY KEY !!! */)
INSERT INTO #temp_tbl (c1, c2)
SELECT ...
Curiously enough, the following seems to give your desired result in
SQL Server 2000 but not in 2005. Which just goes to show the folly of
such proprietary "tricks" as this.
SELECT c1, COALESCE(c2,0) AS c2
INTO T1
FROM (SELECT NULL, NULL) AS T(c1,c2);
David Portas
SQL Server MVP
--|||David Portas wrote:
> Curiously enough, the following seems to give your desired result in
> SQL Server 2000 but not in 2005. Which just goes to show the folly of
> such proprietary "tricks" as this.
> SELECT c1, COALESCE(c2,0) AS c2
> INTO T1
> FROM (SELECT NULL, NULL) AS T(c1,c2);
>
That was just a type coercian issue. The following tests OK on both
2000 and 2005 (8.00.760 and 9.00.1399.06)
SELECT c1, COALESCE(c2,0) AS c2
INTO T1
FROM (SELECT CAST(NULL AS INTEGER),
CAST(NULL AS INTEGER)) AS T(c1,c2);
David Portas
SQL Server MVP
--