May 14, 2019 at 7:16 pm
Hello everyone. I am new to SQL Server (and to this forum) and I am having some problems converting some existing Oracle queries over to SQL Server. I work in GIS and I have a table that is sent to me every week from the Tax Assessor with tax information for our parcels. I have a few scripts from Oracle that were used to update this table with new rows. These scripts were written before my time, and I am not completely sure how to edit them to make them work in SQL Server. I think I have the first one figured out, but I am struggling with the second one. I am listing the scripts below. Any help would be greatly appreciated. Thank you!
Kevin
alter table nlcog.gis.caddo_assessor_info add
LOTNO varchar (5),
OWNER varchar (100),
PROP_ADDRESS varchar (75),
MAIL_ADDRESS varchar (100),
LEGAL_DESC varchar (260),
SHORT_GEOG varchar (9);
update nlcog.gis.caddo_assessor_info
lotno=ltrim(substring(geog1,10,4),
owner =(Name||' '||Name2),
prop_address=ltrim(street_number||' '||initcap(street_name),
mail_address=initcap(address1||' '||city||' '||state||' '||zip),
short_geog=substring(geog1,1,9);
May 14, 2019 at 9:50 pm
If the 'second' script is this:
update nlcog.gis.caddo_assessor_info
lotno=ltrim(substring(geog1,10,4),
owner =(Name||' '||Name2),
prop_address=ltrim(street_number||' '||initcap(street_name),
mail_address=initcap(address1||' '||city||' '||state||' '||zip),
short_geog=substring(geog1,1,9);
Then the only changes needed is for string concatenation. In SQL Server you can do either:
owner =(Name + ' ' + Name2),
Or
owner = concat(Name, ' ', Name2),
My preference is to use the concat function - as it takes care of data type conversion and nulls for you.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 14, 2019 at 9:58 pm
Welcome to the forum and to SQL Server!
So looking at your queries, my understanding is the first one is adding columns to an already existing table. That one looks good (a few best practice things missing, but nothing I'd fail your code for). The second one is REALLY close. The syntax for UPDATE is:
UPDATE <table>
SET <column>=<value>
One thing is you are missing a closing bracket when assigning prop_address. I was also not 100% sure what "initcap" is, but I looked it up and that one does not exists in SQL Server. This link has a function ready to rock for that though:
http://www.sql-server-helper.com/functions/initcap.aspx
I think some of the formatting of your query got screwed up by the forum. But if I am wrong, the || should be replaced with a +. so for example:
owner=(Name + ' ' + Name2)
A trick I do when I am working with UPDATE, INSERT and DELETE statements where I am not 100% confident I have the query EXACTLY how I need it is to toss it into a transaction. What I mean by this is:
BEGIN TRANSACTION testQuery
SELECT * FROM <table>
-- Do your insert/update/delete here
SELECT * FROM <table>
ROLLBACK TRANSACTION testQuery
replacing <table> with the name of the table you are working with. This way you can see the before data and the after data and compare them. The rollback will undo the majority of the changes done. For the most part, it undoes all the changes. There are a few exceptions, so if you have a test system, it is ALWAYS recommended to run things on test first. For example, if the table has an identity column on it (a column that auto-increments), the identity value will increment even though nothing got permanently written to the table. In some cases this is acceptable, in some cases this is the exact opposite of what you want. It depends on your system. Where I work, 99% of the time having a gap in the identity value is of no concern and the 1% of the time it is an issue is usually actually not an issue just somebody looking at the data and getting into a panic.
Once you are 100% happy with the "after" results, change the "ROLLBACK" to a "COMMIT".
And welcome to the SQL Server world.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 15, 2019 at 7:51 am
Welcome to SQL Server Central.
It looks like the usual has occurred and you have a couple of good answers to your question. Never hesitate to ask follow-up questions. We like to help out here. If someone answers the question, we'd love to know how things worked out. It's not a requirement or anything, but it's useful to us to understand how things work. Also, since people search the site frequently, they may have the same question as you and which answer worked best would help them.
Since you're new to SQL Server, I'd like to suggest a couple of additional resources in addition to SQL Server Central. For excellent articles on a variety of Data Platform topics, please check out Simple-Talk. For in-person and virtual training, I'd suggest looking at the offerings from PASS.org.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 15, 2019 at 2:12 pm
Thank you guys so much for your input! I updated my queries with the suggestions and it works perfectly! I really like that the concat function takes care of data type conversions. I appreciate the input and I am sure I will be back to the forum frequently for answers to future questions.
Kevin
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply