SQL query question

mcrmg
mcrmg used Ask the Experts™
on
Hi,

I have an Excel file looks like this:
userid  expense1  expense2  expense3
1           $10
2                                                  $20

I import this into a temp table with MONEY data type, since it is a money type, the value for empty cell would be 0.

I need to update those values into database, only $10 and $20 will be updated, the values for empty cells will be be kept the same as wahtever database has now.

What would the best way to do this?  thx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Not quite sure I understand - if it's a temp table, why are you concerned about the old values? Any why don't you use a different field/column type to hold the data? It would be great if you could share a bit more details about the actual process...

Anyway, I suppose I would create a temp table with a numeric data type instead of money, default values set to null and it should import correctly. Which DB are you using?
G GodwinDatabase Administrator

Commented:
Try this:
Update TableName
set Expense1 = case when Expense1 <> 0 then expense1 + 10 else 0 end,
Expense2 = case when expense2 <> 0 then expense2 - 5 else 0 end ,
Expense3 = case when expense3 <> 0 then expense3 + 15 else 0 end
 
Best Regards
-G
G GodwinDatabase Administrator

Commented:
Clearly I just plugged in some random updates that will affect each whole column if it is not zero at the time of update.
 
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Author

Commented:
I have sql 08, so try to set numeric(18, 0)?  thx

Author

Commented:
I set the default to null with money type, but it is still showing 0 with those empty cells..thx
G GodwinDatabase Administrator

Commented:
O.K., Now I think I see what you are trying to do.  
You have a permanent table that you want to update with non zero values that you have loaded into a temp table. Is that right?
 Try this:
Update TableName
set
Expense1 = case when TMP.Expense1 <> 0 then TMP.Expense1 else tbl.Expense1 end,
Expense2 = case when TMP.Expense2 <> 0 then TMP.Expense2 else tbl.Expense2 end,
Expense3 = case when TMP.Expense3 <> 0 then TMP.Expense3 else tbl.Expense3 end
from TableName TBL
join #tmpTable TMP on tbl.UserID = TMP.UserID
 
G GodwinDatabase Administrator

Commented:
Here is a test script with TEMP tables as an example.  
Best Regards,
-G

-- Drop table #tmpTable
-- Drop table #TableName

create table #tmpTable (userid int, expense1 money, expense2 money, expense3 money)


create table #TableName (userid int, expense1 money, expense2 money, expense3 money)

insert into #TableName values (1, 10, 0, 20)
insert into #TableName values (2, 10, 0, 20)
insert into #TableName values (3, 10, 0, 20)
insert into #TableName values (4, 10, 0, 20)

insert into #tmpTable values (2, 0, 10, 0)
insert into #tmpTable values (3, 30, 20, 0)


select * from #TableName
select * from #tmpTable

-- Update Statement
Update #TableName 
set 
Expense1 = case when TMP.Expense1 <> 0 then TMP.Expense1 else tbl.Expense1 end,
Expense2 = case when TMP.Expense2 <> 0 then TMP.Expense2 else tbl.Expense2 end,
Expense3 = case when TMP.Expense3 <> 0 then TMP.Expense3 else tbl.Expense3 end
from #TableName TBL
join #tmpTable TMP on tbl.UserID = TMP.UserID
--


 select * from #TableName

Open in new window

Author

Commented:
actually, this is what I am looking for:

if the excel cell is empty, then dont update the datatabse

if there is value in Excel cell, update them in datase, even it is 0


thx


G GodwinDatabase Administrator

Commented:
So, the problem is that the temp table is getting zeros for blank cells and for legitimate zero values. Is that correct?
Let's look at how you are populating your temp table.
-G

Author

Commented:
yes, so the empy cell wipe out the existing values..
*****for temp table
For the temp table, I have money type with NULL as default value

*****for excel file
the cells are formatted as currency

*****getting excel into temp table
(I am using classic asp to do the job)
    SET objExcelConn = Server.CreateObject("ADODB.Connection")
    SET objExcelRS = Server.CreateObject("ADODB.RecordSET")
    objExcelConn.Provider = "Microsoft.Jet.OLEDB.4.0"
    objExcelConn.Properties("Extended Properties").Value = "Excel 8.0"
    path = strDatabase    
    objExcelConn.Open path
    wrongSheet = False
 
    SET objExcelRS = objExcelConn.Execute ("SELECT * FROM [Sheet1$]")
    Once I have the record set, I loop though it and insert them into temp table..thx

G GodwinDatabase Administrator

Commented:
O.K., Lets look at the Insert Statements.  
Here's why: You have said you want to update for zeros in the source but not nulls, so we really need to make sure NULLs are going into the TEMP table as NULLs.  (if they go in as empty strings '' they will be converted to zeros, and then will be indistiguishable from real zeros.)
-G

Author

Commented:
man, looks like I found the problem......LOL

I will have to try it tomorrow in the office, can I insert NULL into a money type table?  thx
*****insert
	While Not (objExcelRS.EOF)


		SQLstr = "INSERT INTO mytemp (userid, Date, expense1, expense2, expense3) VALUES (" & objExcelRS("userid") &",'"& objExcelRS("Date") &"',"& "CONVERT(money,isNULL('" & objExcelRS("e1")& "',0))" & ","& "CONVERT(money,isNULL('" & objExcelRS("e2")& "',0))" & ","& "CONVERT(money,isNULL('" & objExcelRS("e3")& "',0)))"
		'response.write SQLstr & "<br><br>"

		On Error Resume next
		oConn.execute(SQLstr)
		If (err.Number<>0) Then
			'Response.Write Err.Number & ":" & Err.Description
			wrongFormat = true
		End If
		objExcelRS.MoveNext 		
	Wend

Open in new window

G GodwinDatabase Administrator

Commented:
There you go...
However, you will want to look at the CASE statements I showed you earlier, because you don't want to update if the value is NULL.  So, once your temp table is loading with NULLs where expected; it would look something like this...
Update TableName
set
Expense1 = case when TMP.Expense1 IS NOT NULL then TMP.Expense1 else tbl.Expense1 end,
Expense2 = case when TMP.Expense2 IS NOT NULL then TMP.Expense2 else tbl.Expense2 end,
Expense3 = case when TMP.Expense3 IS NOT NULL then TMP.Expense3 else tbl.Expense3 end
from TableName TBL
join #tmpTable TMP on tbl.UserID = TMP.UserID
-gg

Author

Commented:
if I type the query manaually like this, it works
insert into table (...) values (NULL,12,20)
it works, but from excel file, it does not..thx

Author

Commented:
temptable is still showing up 0..thanks

Author

Commented:
from the excel, the insert looks like this:

insert into table (...) values (,12,20)
                                             ^
                                             it generates an error...
G GodwinDatabase Administrator

Commented:
Did you remove the ISNULL function from the insert code?
-G

Author

Commented:
yes, it is like this

insert into table (...) values (,12,20)

thx
G GodwinDatabase Administrator

Commented:
I'd like to see you try this:
SQLstr = "INSERT INTO mytemp (userid, Date, expense1, expense2, expense3)
VALUES (" & 
objExcelRS("userid") &",'"&
objExcelRS("Date") &"',"&
"CONVERT(money, " & objExcelRS("e1")& "),"&
"CONVERT(money, " & objExcelRS("e2")& "),"&
"CONVERT(money, " & objExcelRS("e3")& "))"
Then, you should start seeing NULLs vs zeros (where zeros in the temp table are actually zeros in the incoming data.
-G
G GodwinDatabase Administrator

Commented:
Try the code above:  
The insert you tried by hand would errror on syntax.
insert into table (...) values (,12,20)
If you want to test this, you would need to specify NULL values with the "NULL" keyword:
insert into table (...) values (..., NULL, 12, 20).
-G

Author

Commented:
this is what I have now

insert into table (...) values (CONVERT(money,),12,20)
                                                                          ^
with error
-2147217900:Incorrect syntax near ')'.

thx
Database Administrator
Commented:
Looks like the problem is not related to expanding the "e" variables to create the string.  Since they resolve to NULL, the string concatenates nothing there. This leaves you with a syntax error.
Try using no type conversion. The NULL values should come through then, and the conversion to money should be implicit anyway.  
SQLstr = "INSERT INTO mytemp (userid, Date, expense1, expense2, expense3)
VALUES (" & 
objExcelRS("userid") &",'"&
objExcelRS("Date") &"',"&
objExcelRS("e1")& ","&
objExcelRS("e2")& ","&
objExcelRS("e3")& ")"

Let me know how this works.  
-G

Author

Commented:
get the same error

insert into table (...) values (,12,20)


-2147217900:Incorrect syntax near ','.

so strange...thx

Author

Commented:
I tried this:

SQLstr = "INSERT INTO mytemp (userid, Date, expense1, expense2, expense3) VALUES (" & objExcelRS("userid") &",'"& objExcelRS("Date") &"',"& "CONVERT(money,isNULL('" & objExcelRS("e1")& "',NULL))" & ","& "CONVERT(money,isNULL('" & objExcelRS("e2")& "',NULL))" & ","& "CONVERT(money,isNULL('" & objExcelRS("e3")& "',NULL)))"

(money,isNULL('" & objExcelRS("e3")& "',NULL)))"
                                                                  ^^^^^^   instead of 0

strangely enough, it put 0 into temp table ...thx
G GodwinDatabase Administrator

Commented:
Can I see the create Table statement for your temp table?
-G
 

Author

Commented:
it works now, no clue why, but it works.....thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial