Help with inserting NULL into test table

Question

Post reply

Help with inserting NULL into test table

Paul Morris-1011726

SSCommitted

Points: 1879
More actions
September 14, 2010 at 10:05 am

#223832

I am in the process of creating some test data which will include NULL values. I looked around for some guidance but was not able to find anything that would help.
Anyway, here is what I have inculding where I need the NULL values:
IF OBJECT_ID(N'VC') IS NOT NULL
DROP TABLE VC
GO
CREATE TABLE vc
(
case_sk int NOT NULL,
case_number varchar(15) NULL,
datereceived datetime NULL,
closeddate datetime NULL,
cc_init varchar(3) NULL,
atty_inits varchar(3) NULL
)
GO
INSERT INTO VC (case_sk, case_number, datereceived, closeddate, cc_inits, atty_inits )
SELECT 00001, 000001, '9/1/2006', '9/1/20009', 'AA1', 'BBB' UNION ALL
SELECT 00002, 000002, '9/1/2008', '9/1/20010', NULL, 'EEE' UNION ALL
SELECT 00003, 000003, '10/1/2008', '9/1/2009', 'BB2', 'AAA' UNION ALL
SELECT 00004, 000004, '1/1/2007', '9/1/2010', NULL, 'CCC' UNION ALL
SELECT 00005, 000005, '2/1/2007', NULL, NULL, 'BBB' UNION ALL
SELECT 00006, 000006, '3/1/2009', '9/1/2009', 'AA1', 'CCC' UNION ALL
SELECT 00007, 000007, '2/1/2009', '9/1/2009', NULL, 'DDD' UNION ALL
SELECT 00008, 000008, '2/1/2009', '6/1/2010', 'AA1', 'DDD' UNION ALL
SELECT 00009, 000009, '5/1/2010', '9/1/2010', 'BB2' 'EEE' UNION ALL
SELECT 00010, 000010, '5/1/2010', NULL, NULL, 'EEE' UNION ALL
SELECT 00011, 000011, '6/1/2010', NULL, NULL, 'EEE'
--DROP TABLE VC
SELECT vc.case_sk, vc.datereceived, vc.closeddate, ISNULL(vc.cc_inits, vc.atty_inits) AS Intials,
vc.case_number
FROM vc
GROUP BY vc.case_sk, vc.datereceived, vc.closeddate, ISNULL(vc.cc_inits, vc.atty_inits),
vc.case_number
Thanks in advance!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Paul, i dint quite understand ur requirement.. did u mean, u need to hardcode a column as NULL in the SELECT ?

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 2

Sorry for the confusion...

Where the NULL values are in the Insert, those are supposed to be blank... E.I. no closed date or cc_initials.

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

Then why not include a blank there? I'm still not clear what you are trying to do.

WayneS SSC Guru Points: 95460 More actions · Answer 4

for case_sk = 000009, there is no comma between the cc_init and atty_inits.

Also, in some places you have cc_init, and in others cc_inits.

That should get your test data loaded.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 5

Steve Jones - Editor (9/14/2010)
Then why not include a blank there? I'm still not clear what you are trying to do.

I get syntax errors when I do blanks:

INSERT INTO VC (case_sk, case_number, datereceived, closeddate, cc_inits, atty_inits )

SELECT 00001, 000001, '9/1/2006', '9/1/20009', 'AA1', 'BBB' UNION ALL

SELECT 00002, 000002, '9/1/2008', '9/1/20010', , 'EEE' UNION ALL

SELECT 00003, 000003, '10/1/2008', '9/1/2009', 'BB2', 'AAA' UNION ALL

SELECT 00004, 000004, '1/1/2007', '9/1/2010', , 'CCC' UNION ALL

SELECT 00005, 000005, '2/1/2007', , , 'BBB' UNION ALL

SELECT 00006, 000006, '3/1/2009', '9/1/2009', 'AA1', 'CCC' UNION ALL

SELECT 00007, 000007, '2/1/2009', '9/1/2009', , 'DDD' UNION ALL

SELECT 00008, 000008, '2/1/2009', '6/1/2010', 'AA1', 'DDD' UNION ALL

SELECT 00009, 000009, '5/1/2010', '9/1/2010', 'BB2', 'EEE' UNION ALL

SELECT 00010, 000010, '5/1/2010', , , 'EEE' UNION ALL

SELECT 00011, 000011, '6/1/2010', , , 'EEE'

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 6

Well that explains part of it....

I am still getting an error:

Conversion falied when converting date and/or time for character string

I assume that is the NULL being read in as a string into the datetime column... That is where I am stuck.

WayneS SSC Guru Points: 95460 More actions · Answer 7

Paul Morris-1011726 (9/14/2010)
Well that explains part of it....
I am still getting an error:
Conversion falied when converting date and/or time for character string
I assume that is the NULL being read in as a string into the datetime column... That is where I am stuck.

The first two selects have 5 digit years...

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 8

Paul Morris-1011726

SSCommitted

Points: 1879

September 14, 2010 at 12:02 pm

#1220892

My apologies for being a bonehead....

Thanks WayneS

WayneS SSC Guru Points: 95460 More actions · Answer 9

Paul Morris-1011726 (9/14/2010)
My apologies for being a bonehead....
Thanks WayneS

No problem - we all do it time to time.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2