Inserting Null value

Question

Post reply

Inserting Null value

daniness

SSCrazy

Points: 2890
More actions
April 11, 2014 at 6:54 am

#306241

Hi All,
I'm trying to insert multiple records, each containing a null value for one of the fields, but not having much luck. Could someone please adise? This is the code I'm using:
Use InternationalTrade
Go
CREATE TABLE dbo.ACCTING_ADJUST
(
stlmnt_instr_id varchar(20) null,
instr_id varchar(20) not null,
postdate datetime null,
postype char(3) null,
gl_num varchar(14) not null,
acct_num varbinary(68) null,
crncy char(4) null,
usdamt numeric(20,5) null,
cost_cntr char(4) null
)
Go
INSERT INTO dbo.ACCTING_ADJUST (stlmnt_instr_id, instr_id, postdate, postype, gl_num, acct_num, crncy, usdamt,
cost_cntr)
SELECT '', 'SB9104520001', '2010-05-24', 'GLC', '8039581', NULL, 'USD', '40122.00', '4251'
UNION ALL
SELECT '', 'SB9104520001', '2010-05-24', 'GLD', '8058581', NULL 'USD', '40122.00', '4251'
UNION ALL
SELECT '', 'SB9104520001', '2010-05-13', 'GLC', '8058581', NULL, 'USD', '495085.24', '4251'
UNION ALL
SELECT '', 'SB9104520001', '2010-05-13', 'GLD', '8039581', NULL, 'USD', '495085.24', '4251'
UNION ALL
SELECT '', 'SB9026640001', '2010-08-01', 'GLC', '3493641', NULL, 'USD', '800.00', '6253'
UNION ALL
SELECT '', 'SB9026640001', '2010-08-01', 'GLD', '2229244', '15981219', 'USD', '800.00', '6253'
UNION ALL
SELECT '', 'SB9026640001', '2010-04-20', 'GLC', '8058581', NULL, 'USD', '30501.38', '6253'
UNION ALL
SELECT '', 'SB9026640001', '2010-04-20', 'GLD', '8039581', NULL, 'USD', '30501.38', '6253'
UNION ALL
SELECT '', 'BS6004260001', '2010-10-18', 'GLC', '3493640', NULL, 'USD', '1155.63', '6450'
UNION ALL
SELECT '', 'BS6004260001', '2010-10-18', 'GLD', '1964118', NULL, 'USD', '1155.63', '6170'
UNION ALL
SELECT '', 'BS6004260001', '2010-10-13', 'GLC', '3493640', NULL, 'USD', '5943.85', '6450'
UNION ALL
SELECT '', 'BS6004260001', '2010-10-13', 'GLD', '1964118', NULL, 'USD', '5943.85', '6170'
UNION All
SELECT '','BS6004260001', '2010-10-13', 'GLC', '3493640', NULL, 'USD', '16.99', '6450'
UNION ALL
SELECT '','BS6004260001', '2010-10-13', 'GLD', '1964118', NULL, 'USD', '16.99', '6170'
UNION ALL
SELECT '','BS6004260001', '2010-10-13', 'GLC', '3493640', NULL, 'USD', '594.81', '6450'
UNION ALL
SELECT '','BS6004260001', '2010-10-13', 'GLD', '1964118', NULL, 'USD', '594.81', '6170'
UNION ALL
SELECT '','BS6004260001', '2010-10-13', 'GLC', '3493640', NULL, 'USD', '190.68', '6450'
UNION ALL
SELECT '','BS6004260001', '2010-10-13', 'GLD', '1964118', NULL, 'USD', '190.68', '6170'
UNION ALL
SELECT '','BS6004260001', '2010-08-09', 'GLC', '8058582', NULL, 'USD', '1770695.80', '6450'
UNION ALL
SELECT '','BS6004260001', '2010-08-09', 'GLD', '8039582', NULL, 'USD', '1770,695.80', '6450'
Go

Viewing 6 posts - 1 through 5 (of 5 total)

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

Andreas P. Williams SSCrazy Points: 2487 More actions · Answer 1

Hi,

In your second 'SELECT ...' the comma after the NULL is missing.

Neil Burton SSC-Insane Points: 22999 More actions · Answer 2

INSERT INTO dbo.ACCTING_ADJUST (stlmnt_instr_id, instr_id, postdate, postype, gl_num, acct_num, crncy, usdamt,
cost_cntr)
SELECT '', 'SB9104520001', '2010-05-24', 'GLC', '8039581', NULL, 'USD', '40122.00', '4251'
UNION ALL
SELECT '', 'SB9104520001', '2010-05-24', 'GLD', '8058581', NULL There's a comma missing here 'USD', '40122.00', '4251'
UNION ALL

There's a comma missing between the acct_num and crncy values on the second row. Is that a typo in your sample data. If not, that'll explain it.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

gbritton1 SSCertifiable Points: 6522 More actions · Answer 3

Your second select is missing a comma:

SELECT '', 'SB9104520001', '2010-05-24', 'GLD', '8058581', NULL 'USD', '40122.00', '4251'

There should be a comma between NULL and 'USD'

Also, your ddl specifis that the column acct_num is varbinary. I have a feeling that is not what you want. (It would be highly unusual). Should it not be int or possibly varchar?

Also, your last select:

SELECT '','BS6004260001', '2010-08-09', 'GLD', '8039582', NULL, 'USD', '1770,695.80', '6450'

has a comma in the data for the usdamt field. That will throw a conversion error message.

Finally, you can do this sort of thing simpler (IMHO) using the VALUES keyword. e.g.

INSERT INTO #ACCTING_ADJUST (stlmnt_instr_id, instr_id, postdate, postype, gl_num, acct_num, crncy, usdamt,

cost_cntr) VALUES

( '', 'SB9104520001', '2010-05-24', 'GLC', '8039581', NULL, 'USD', '40122.00', '4251'),

( '', 'SB9104520001', '2010-05-24', 'GLD', '8058581', NULL, 'USD', '40122.00', '4251'),

( '', 'SB9104520001', '2010-05-13', 'GLC', '8058581', NULL, 'USD', '495085.24', '4251'),

( '', 'SB9104520001', '2010-05-13', 'GLD', '8039581', NULL, 'USD', '495085.24', '4251'),

( '', 'SB9026640001', '2010-08-01', 'GLC', '3493641', NULL, 'USD', '800.00', '6253'),

( '', 'SB9026640001', '2010-08-01', 'GLD', '2229244', '15981219', 'USD', '800.00', '6253'),

( '', 'SB9026640001', '2010-04-20', 'GLC', '8058581', NULL, 'USD', '30501.38', '6253'),

( '', 'SB9026640001', '2010-04-20', 'GLD', '8039581', NULL, 'USD', '30501.38', '6253'),

( '', 'BS6004260001', '2010-10-18', 'GLC', '3493640', NULL, 'USD', '1155.63', '6450'),

( '', 'BS6004260001', '2010-10-18', 'GLD', '1964118', NULL, 'USD', '1155.63', '6170'),

( '', 'BS6004260001', '2010-10-13', 'GLC', '3493640', NULL, 'USD', '5943.85', '6450'),

( '', 'BS6004260001', '2010-10-13', 'GLD', '1964118', NULL, 'USD', '5943.85', '6170'),

( '','BS6004260001', '2010-10-13', 'GLC', '3493640', NULL, 'USD', '16.99', '6450'),

( '','BS6004260001', '2010-10-13', 'GLD', '1964118', NULL, 'USD', '16.99', '6170'),

( '','BS6004260001', '2010-10-13', 'GLC', '3493640', NULL, 'USD', '594.81', '6450'),

( '','BS6004260001', '2010-10-13', 'GLD', '1964118', NULL, 'USD', '594.81', '6170'),

( '','BS6004260001', '2010-10-13', 'GLC', '3493640', NULL, 'USD', '190.68', '6450'),

( '','BS6004260001', '2010-10-13', 'GLD', '1964118', NULL, 'USD', '190.68', '6170'),

( '','BS6004260001', '2010-08-09', 'GLC', '8058582', NULL, 'USD', '1770695.80', '6450'),

( '','BS6004260001', '2010-08-09', 'GLD', '8039582', NULL, 'USD', '1770695.80', '6450')

daniness SSCrazy Points: 2890 More actions · Answer 4

Thank you all for the valuable insight. I totally missed that comma...guess I had been looking at it too long :-).

Have a good day!

Neil Burton SSC-Insane Points: 22999 More actions · Answer 5

daniness (4/11/2014)
Thank you all for the valuable insight. I totally missed that comma...guess I had been looking at it too long :-).
Have a good day!

You're welcome. I think we've all been there and done that 🙂

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537