Tag: tsql

How to distinct with condition

I am working on T-SQL. I got a table like this. x y z t 1 avc 45353 11.02.2019 2 avc 24324 29.05.2019 3 frg 54647 30.06.2019 3 dsc 35678 29.05.2019 4 dec 21445 13.04.2019 4 …

Is branch pruning possible for recursive cte query

common-table-expression recursive-query sql sql-server tsql

This is inspired by question Retrieve a list of lists in one SQL statement – I have come up with a solution, but I have doubts on its efficiency. To restate the problem: we have 2 Tables: Person and …

What is this TSQL V() method/syntax/function?

sql sql-server tsql

I have just come across this syntax for defining an inline view… SELECT myAlias, myAlias1 FROM ( SELECT myCol, myCol1 FROM myTable ) V( myAlias, myAlias1) I can see what the V is doing, but what …

How to delete duplicate rows in a table based on what is supposed to be a unique column referring to a table in another database

sql sql-delete sql-server tsql

In Table A PersonID refers to Table B another database and is supposed to be unique here. For reasons, it isn’t and a stored procedure that uses PersonID and FacilityID as input parameters is returning multiple rows for the instances of that duplicate PersonID. I would like to remove the top record in Table A as it does not belong

Getting Msg 8623, Level 16, State 1, Line 1 error on a simple select query on one table

sql sql-in sql-server subquery tsql

How to optimize a simple query that search on one table for IDes that are not a part of a set. I created the following query Be aware that the list includes bit more than 35000 rows. I get the following database error Msg 8623, Level 16, State 1, Line 1 The query processor ran out of internal resources and

(SQL Server 2017) I get error Msg 8152 and I don’t know why

sql sql-server sql-server-2017 tsql

I have no idea why, but I am getting the error Msg 8152 (in SQL Server 2017) after trying to insert something like into the table The output I get is Msg 8152, Level 16, State 14, Line 1 String or binary data would be truncated. Thanks in advance for your help! Answer You need to define a length for

TSQL – Parent Child (1 to zero/many) Grouping/Aggregation

sql sql-server sql-server-2016 tsql

Code (Sample Data Staging): Description: @Emp is the sample Employee table (Unique Employee records). EId = Employee Id FN = First Name LN = Last Name @EmpPhCont is the sample Employee Phone Contact table (Each Emp from @Emp table can have zero, one, or multiple phone numbers here – unique by Emp/Type). PhType = Phone Type (home, mobile, work, and