Tag: sql-server

Bulk insert csv file with semicolon as delimiter

I’m trying to import data from semicolon separated csv file into a SQL Server database. Here is the table structure The csv file is shown below: There are some columns that I don’t need, so I create a format file to import the data. The format file is shown as below Then I tried both bulk insert a…

How to get record by Date in my case using SQL Server?

sql sql-server

I have 2 tables ProductLog and Product ProductLog Product The select statement RegNo value to be ProductLog.RegNo , which is Product.Date <= ProductLog.TransDate I want to get a result like below Expected Output I tried by ranking order Date by ASC, and where rank =1, it set all to lowest date value. (I un…

Is there an UPDATE equivalent command to SELECT json data

json sql sql-server

Is there a way to update the data retrieved from the below select (in this case, change “MS220” to something else)? It’s difficult enough to do select from JSON in some cases. I’m not sure how to update just a single element. Answer Try JSON_MODIFY() with the path ‘$[0].Name&#821…

How to get data from json column in SQL Server that starts with array element

json sql sql-server

I have a database table that I need to extract data from where the column of interest has json it. What makes this particularly difficult is the most outer elements of the json is ‘[‘ & ‘]’ as in the parent element is an array. I need to get the value associated with key ‘Nam…

How to return the highest value in a sequence for each ID unless one has been skipped, with SQL?

sql sql-server

I want to return the columns listed below but would like it grouped by JobNumber and only return the maximum StageNum so long as it has not skipped any stages. For example, my table currently looks like this: ID Stage StageNum Date 104 Released 10 2022-02-07 104 Slab 20 2022-02-18 104 Frame 30 2022-03-07 104 …

While loop function union function end

function loops sql sql-server union

I want to loop the code shown below. I would like to use the [getpublicholidays] function for a variable number of years and merge them (union). Unfortunately I don’t know how to use the function in a loop with union. Can someone help? Answer If you are looping for X years, each time you loop to a new y…

Select all groups that contain all specified values in a certain column

query-optimization sql sql-server tsql

Say I have the following table: CaseRef NotificationReason NotificationDate 123 SCHEDULED 2022-01-01 234 SCHEDULED 2022-01-02 312 SCHEDULED 2022-01-01 123 RESCHEDULED 2022-01-02 123 DECIDED 2022-01-03 234 DECIDED 2022-01-02 If I want to return only rows that have a CaseRef that has both a SCHEDULED and a DECI…

Validate that a new username doesn’t match a pre-existing username

sql sql-server validation

I wanted to implement a validation in my SQL stored procedure that’s designed to create new users. I wanted to validate that a new user can’t use the same username as another user. This is my current stored procedure: I assume I need to do something using an if statement that takes the value enter…

@@ROWCOUNT shows as 0 when deleting using dynamic query SQL

sql sql-server

I am facing a trouble when using dynamic query and when trying to get the number of deleted records using @@ROWCOUNT Here is my QUery Here after the dyanimic delete query (inside my cursor) I am trying to store the number of deleted records into another table using @@ROWCOUNT. But it shows as 0. I didnt under…