I’m new to data warehouse, So I try to follow the best practice, mimicking some implementation details from the Microsoft Demo DB WideWorldImportersDW, One of the things that I have noticed is using Sequence as default value for PK over Identity. Could I ask, If it’s preferable to use Sequence ove…
Tag: sql-server-2019
Extract number before and after character
I need to extract a certain number from my ‘TblA’ from column ‘Input’ in sql. I have the following example input and desired output 1 and 2. Input Desired Output 1 Desired Output 2 20 x 88 nc. 20 88 100 x 300 nc 100 300 200x 88 nc. 200 88 5x 300 nc 5 300 ol (200x 88nc.) 200
SSIS package works in debug mode, but hangs at step 2 when launched from the SQL Server job
Have a bit of a strange question here. We had a job in SQL Server that was being suspended on step 2 of an SSIS package. However whern I run the SSIS package in DEBUG mode it works fine. However if I launch the job from SQL Server it hangs at step 2. Step 2 basically returns about 2 million
SQL – Splitting string in multiple columns
I’d like to split the result from a query and display the values in separate columns. As an example I get the following result I want to split the values by ‘_’ and add them to separate columns. The query result should look something like this So far I can split the result. But for each valu…
SQL Flag Rows Occurring 2 Hours After Previous Flag
I have some messy RFID data due to over sensitive antenna’s. There is a physical process that tracks an RFID tag moving through different stations in a cycle. An item with an RFID tag can move through the cycle more than one time a day, but it is highly unlikely that it could start the cycle within a tw…
Use a common field in two tables with XML in SQL Server
How delete the redundance in the table #XMLItm? My code: In this code I want load information in two tables for a common field ID_LIST. How load the ID_LIST from #XMLLst in the temp table #XMLItm? I want delete the field ID_LIST from XML in the table #XMLItm and take a ID_LIST from temp table #XMLLst; Functio…
SQL – Assign jobs to workers – Alternative to a loop?
In SQL Server 2019, I have a table of unassigned tasks, and another table of workers who are available to work on those tasks. My requirement is to assign the workers evenly over the available tasks …
How to auto generate data types for a SQL table
I have many denormalize tables with 200+ columns. These tables are in SQL Server and they often, if not always, have varchar(100) or nvarchar(100) (string) data types. However, most columns are either ints, floats or other data types. It is not possible for me to go through every table & col and pick and …
Split data in column based on value of in another column
I have a table full of Artist and Track Titles that is imported from two different places. Some are correct with Artist and Title in different columns but then there are other rows where everything is in the Title column separated with ‘-‘ and the Artist column is NULL. Changing it at the source i…
SQL Server 2019 – archive remote DB daily with archive date in each table
I am very new to SQL and was hoping for some help. I have a database that is hosted remotely and I want to take a snapshot of all the views and archive them in a local 2019 database daily with an ‘archived’ date field in each of the local tables. So far I have managed to set up an