Suppose, I have a table tblClasses and tblStudents Now each class have multiple student. tblStudents now, I have another server having exact same db and tables and I am copying data from server 1 to server 2 from same tables using the Select and Insert e.g. and for tblStudents now this is ok but the real prob…
Tag: sql-server
Rows that are in table A but not in B
How can I check the rows for columns a, b, c, and d in table A but not in table B? Answer If you want to get full rows from table a you can use exists:
SQL query to CONCAT/list unique column data if rows have identical data in the other columns?
Thanks in advance. I’m basically trying to run a SQL query so that the results are 1:1:1:1:Many for StaffID:Name:Floor:Date:Shifts. Here is my initial query and example results: I then combined the ShiftStart and ShiftEnd columns with the following to get the further below results: What I can’t fi…
Get value for every date in table with date ranges
I’m working with table, its structure looks like this: from_date DATE %Y-%m-%d to_date DATE %Y-%m-%d person VARCHAR tax INT Table shows that in period between date_from and date_to for such person there was such tax The whole point is that these periods can intersect and activities can sum. Roughly spea…
SQL: Getting Missing Date Values and Copy Data to Those New Dates
So this seems somewhat weird, but this use case came up, and I have been somewhat struggling trying to figure out how to come about a solution. Let’s say I have this data set: date value1 value2 2020-01-01 50 2 2020-01-04 23 5 2020-01-07 14 8 My goal is to try and fill in the gap between the two dates
REGEX in stored procedure in “=”/like clause
I’ve created a stored procedure with 3 inner joins (please, don’t judge me :D): TableAnotherTable is the association table between Table and AnotherTable. So, this stored procedure works in some cases (if ET.Value is equal to A.Value). But if: the stored procedure must also work. How can I change …
Searching through different tables?
I have several tables with a Sequence as the primary key. They are unrelated at all, there are no foreign keys or anything, or anything that could link one to another, so join won’t work. Union won’t work either because they are basically different tables. I know that you could assign “null&…
Combining data into one table cell
There is a query that returns a temporary table. TSQL Query: Result: Random sampling of output values Is it possible to group data from the last column, thereby reducing the number of lines in the output. That is, I need it to be like this: Desired result UPD: SQL Server 2016. STRING_AGG is missing Answer Gro…
Does declaring variables inside an exec improve SQL query plans?
I have a bunch of stored procedures that I’m looking to optimize and want to know if changing from having the variables injected into the WHERE condition to having them inject into a variable declaration will help The current statements look like this where the ids are passed into the stored procedure a…
Power function in T SQL
The following returns .81 as expected: But this does not. Its return value is .8: To have it return .81, I have to cast the input: Why is a cast needed here? What am I missing? Answer The power function return type is dictated by the data type of the first parameter. Here, you’re giving it a decimal(1,1…