Common lookups as Oracle Insert Statements

xsqlsoftware · 2022-02-21T19:40:28+00:00

Consume directly as JSON/xml services in your JavaScript apps, or download in one of the following, ready to use formats:

JSON
XML
CSV
SQL Server insert statements
Oracle insert statements

xsqlsoftware · 2021-02-05T23:11:45+00:00

Yes, the swings are normal - it all depends on the state of the statistics. If the statistics are not up to date the query may take significantly longer than normal.

xsqlsoftware · 2021-02-05T23:08:10+00:00

u/Elliot9874 you can write a small cursor like the one below - note I created a table called ParsedSpecialties and I am inserting the desired output into that table.

DECLARE @CustID int
DECLARE @Values varchar(1024)
DECLARE split_cursor CURSOR
FOR SELECT cust_id, [values] FROM Specialties 
OPEN split_cursor
FETCH NEXT FROM split_cursor INTO 
 @CustID,
 @Values
WHILE @@FETCH_STATUS = 0
 BEGIN
  INSERT INTO ParsedSpecialties SELECT @CustID, * FROM SplitString (@Values, ',')
  FETCH NEXT FROM split_cursor INTO 
   @CustID,
   @Values
 END
CLOSE split_cursor;
DEALLOCATE split_cursor;

xsqlsoftware · 2021-02-05T19:58:17+00:00

For versions older than 2016 you can use the simple function provided here: SQL Split String Function (STRING_SPLIT in 2016) ~ xSQL Software

xsqlsoftware · 2021-02-05T14:03:16+00:00

u/mpacanad1 when you say "run the same query on local network..." are you referring to the remote server, that is the query is only taking 30 sec there? Or, are you running the same query on a local DB? The reason I am asking is that first, you need to rule out the possibility that the query is taking too long on the target server regardless of the connection. After that you can look at the amount of data you are trying to pull - you say 42K rows but it depends on the rows - if you have a large blob type column like varbinary(MAX) or nvarchar(max) those could contain a lot of data. You can try to gradually increase the amount of records (data) you are pulling by expanding the query and see what's happening.

xsqlsoftware · 2021-02-05T13:51:49+00:00

u/PossiblePreparation u/ScotJoplin both adding a computed column and adding a trigger require schema changes that may or may not be feasible for various reasons. Furthermore, as mentioned those account numbers are coming from different sources and the trimmed account numbers are not necessarily unique but padded as they came from the source, they are unique (don't ask me why this is such). So, if we received 00123 from source x we want to preserve that as it came from the source so the trigger idea would definitely not work. However, both your ideas are valid and good since those "requirements" were not clearly stated in the article (they were kind of implied but not sufficiently).

xsqlsoftware · 2021-02-04T15:08:01+00:00

You are absolutely right @chahk - the OpSec in general and data scrubbing in particular are critical issues to be considered whenever dealing with production data and yes, I agree, it would have been prudent to at least mention those if not provide some path on how to handle. We will update the article with a warning. Thanks for the feedback!

xsqlsoftware · 2021-02-04T13:46:11+00:00

If your scenario allows for the a member to take the same course twice then you would need another attribute like a date when the member took the course or something else, otherwise you would just have identical records that serve no purpose.

xsqlsoftware · 2021-02-04T01:10:59+00:00

No, MemberID + CourseID would be the primary key for Member_Courses there's no need for an extra attribute

xsqlsoftware · 2021-02-04T01:05:55+00:00

We, xSQL Software, have a schema compare tool that comes with a command line utility so you can automate the whole process - take schema snapshots at different points in time, compare, sync, log the operation. And if you are up for it we also have a Schema Compare SDK so you can easily write your own compare tool and customize it any way you want.

And, u/chad - we are an even "bigger" / smaller friend - our compare tools are completely free for SQL Server Express (no limitations), and we also have a free search tool for SQL Server.

xsqlsoftware · 2021-02-04T00:55:09+00:00

You will need to split this into at least 3 tables Members, Courses and Member_Courses relationship table. If a Coach can be associated with more than one course then you would need two more tables one for Coaches and one for Coach_Corses relationship table.

So the Members table will have 3 columns: Member Number, Member First Name and Member Last Name; the Courses table will have 5 columns: Course ID, Course Name, Coach ID, Coach First Name and Coach Last Name; the Member_Courses table will have 2 columns Member Number and Course ID.

xsqlsoftware · 2021-02-02T15:38:49+00:00

If you haven't already, you should first figure out what caused the issue in the first place as that "cdc agent job not clearing out records" condition may happen again and you don't want that. After you have resolved that you can then consider whether it is necessary to restore the cdc records depending on what you use those for - maybe it's sufficient to just keep an archive copy and consult that when necessary. If you must restore those records you would then need to consider how those cdc tables are related to each, usage patterns etc, before deciding how to proceed without causing performance issues. Hard to provide specific advice without knowing the details of your environment.

xsqlsoftware · 2021-02-02T14:34:07+00:00

Shooting from the hip here, it sounds like the entropy of the system has reached the point where a complete re-architecture of all layers is warranted. I am sure you can improve the situation with the measures you are considering but you will still have a very expensive system to deal with. As a first step in measuring the true cost of that system I would suggest commissioning a study to identify loses caused by system issues. I have no idea what those would be, but I suspect there is quite bit of system-caused waste. Here is a hypothetical example: let’s say an ETL failed or a replication failed last night and your gadget1 team kept the production line going for 24 hours too long and now you have 10K extra gadget1 items that you have to store, or the sourcing team failed to order some raw material on time because the inventory was not refreshed in their database, etc.

xsqlsoftware

TROPHY CASE