January 26, 2006 at 9:43 am
I'm trying to write a select... into statement. It's actually just selecting a bunch of hard coded varchar values which will end up being column headers in an .xls. when I actually try to populate the table with values, it throws an error whenever a null is encountered.
How can I allow nulls in any/all of the columns in the table? My code is below
SELECT 'MEMPF_gateway', AS MEMPF_gateway,
'MEM_ID', AS MEM_ID,
'CountOfMEM_ID', AS CountOfMEM_ID,
'CareerStage', AS CareerStage,
'Title1', AS Title1,
'Job1_Level', AS Job1_Level,
'Job1_FuncCode', AS Job1_FuncCode,
'Job1_FuncName', AS Job1_FuncName,
'Job1_IndustryCode', AS Job1_IndustryCode,
'Job1_IndustryName', AS Job1_IndustryName,
'EMktg_Gateway', AS EMktg,
'Gateway', AS Gateway,
'ContactDate', AS ContactDate,
'JoinDate', AS JoinDate,
'ReJoinDate', AS ReJoinDate,
'Status', AS Status,
'PaidThru', AS PaidThru,
'Length', AS Length,
'PromoCode', AS PromoCode,
'EPOStartDate', AS EPOStartDate,
'EPOEndDate', AS EPOEndDate,
'PaidAmt', AS PaidAmt,
'OrigRefSource', AS OrigRefSource,
'OrigRefOther', AS OrigRefOther,
'JoinRefSource', AS JoinRefSource,
'JoinRefOther' AS JoinRefOther
INTO [KS_YahooNonId-Output]
INSERT INTO [KS_YahooNonId-Output] (MEMPF_gateway,MEM_ID,CountOfMEM_ID,
CareerStage,Title1,Job1_Level,Job1_FuncCode,Job1_FuncName,
Job1_IndustryCode,Job1_IndustryName,EMktg,Gateway,ContactDate,
JoinDate,ReJoinDate,Status,PaidThru,Length,PromoCode,EPOStartDate,
EPOEndDate,PaidAmt,OrigRefSource,OrigRefOther,JoinRefSource,
JoinRefOther)
SELECT MemberFirstTime.MEMPF_gateway,
convert(varchar(8), MemberFirstTime.MEM_ID),
convert(varchar(8), Count(MemberFirstTime.MEM_ID)),
[V-exec].CareerStage,
[V-exec].Title1,
[V-exec].Job1_Level,
[V-exec].Job1_FuncCode,
[V-exec].Job1_FuncName,
[V-exec].Job1_IndustryCode,
[V-exec].Job1_IndustryName,
convert(varchar(1),[V-exec].EMktg),
[V-exec].Gateway,
convert(varchar(12),[V-exec].ContactDate,112),
convert(varchar(12),[V-exec].JoinDate,112),
convert(varchar(12),[V-exec].ReJoinDate,112),
[V-exec].Status,
convert(varchar(12),[V-exec].PaidThru,112),
convert(varchar(5),[V-exec].Length),
[V-exec].PromoCode,
convert(varchar(12),[V-exec].EPOStartDate,112),
convert(varchar(12),[V-exec].EPOEndDate,112),
convert(varchar(12),[V-exec].PaidAmt),
[V-exec].OrigRefSource,
[V-exec].OrigRefOther,
[V-exec].JoinRefSource,
[V-exec].JoinRefOther
--INTO [KS_YahooNonId-Output]
FROM execunet_clientview_etrack..MemberFirstTime memberfirsttime
INNER JOIN execunet_clientview..[V-exec] [v-exec]
ON MemberFirstTime.MEM_ID = [V-exec].MemberID
January 26, 2006 at 9:48 am
If you want to allow NULLS in a table then you need to alter the table definition to allow this. If you want to ensure the SELECT is not outputing any NULLS then wrap ISNULL or COALESCE around your SELECT columns.
January 26, 2006 at 9:51 am
Please, repost your message with a shorter query. thus focusing the error.
Read BOL about ISNULL function
January 26, 2006 at 11:47 am
Your choices are either
A) Build the schema prior to inserting your records which includes declaring datatype, and nullability.
B) Create table as you are, then editing the table to allow nulls.
January 27, 2006 at 4:04 am
I would suggest to use CREATE TABLE statement followed by INSERT INTO instead of SELECT INTO. However, if you don't want to do that, there is one way to allow nulls in column using SELECT INTO:
SELECT 'abc' as some_code, CAST(NULL AS datetime) as Time_created, CAST(NULL AS varchar(999)) as Description
The problem is, that you can either allow NULLs, or insert a value into the column. Your requirement is to create table AND insert some descriptive value in every column. That means, no matter what, it will require 2 steps -
either
1. create table (using CREATE TABLE or SELECT INTO)
2. insert data (INSERT INTO)
or
1. SELECT INTO
2. modify table structure (ALTER) to allow NULLs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply