I have stored procedure in SQL Server. In this procedure, I delete duplicate records and insert one unique records to table. When I insert new unique record I am using below script. Everthing is okey with this script but in the production SET IDENTITY_INSERT command needs to ALTER permission. Giving this permission should be dangerous so I can’t give this
Tag: tsql
Difference between dm_exec_sql_text vs dm_exec_input_buffer
As per Microsoft docs, dm_exec_sql_text returns the text of the SQL batch that is identified by the specified sql_handle while dm_exec_input_buffer returns information about statements submitted to an instance of SQL Server. I used DBCC INPUTBUFFER for getting the last executed SQL. But it seems both dm_exec_sql_text and dm_exec_input_buffer are replacement DBCC INPUTBUFFER. If this is the case then what
How to divide SQL results into equal parts using where clause and/or case statement?
Context: I am preparing this dataset to be used for an SSRS report, my goal is to split the list into about multiple parts (equally) for employees to receive. It will be used as a “task list” of sorts. I have tried two different ways, both of which are causing issues. Attempt #1 – Error is ‘incorrect syntax’: Attempt #2
Remove overlapping date intervals
I have two tables in my sql server database : PlannedPeriods : containing periods of future events UnavailabledPeriods : list or periods that are not available Here’s an example for structure and data : I would like to remove all unavailable periods from the planned periods in order to obtain this result : Answer In this case you should be
SQL STUFF FOR XML with specific grouping
I’m working in SQL Server 2014, and I have the following simple data, which tracks calling history of users: I need to group the ACTIVITY values into one field, which effortlessly be done using the STUFF FOR XML function: However I need to conditionally group these, on the condition that there was more than a 90 day between the activity
Assign group_id to all linked records in many-to-many table
There are 3 tables in my DB; Table 1 has ‘Collateral’, Table 2 has ‘Loans’, Table 3 is a multi-link table between 1 and 2; let’s call it ‘Loan_Collateral_link’. A collateral can belong to 1 or more Loans, and a Loan can have multiple Collaterals. What I want to achieve, is create a separate result set (or table) in which
T-SQL Calculating Time Quarters
I would like to parse this record across quarter buckets and the length spent in each quarter bucket. Answer Just another option … BRUTE FORCE Example or dbFiddle Results
TSQL – New column value based on other columns with highest level of match
So I’ve got a mapping table with the following information: And another fact table with the following info: Ideally I should add CCode in the fact table with the highest matching value of Number (first) & FCODE (second) So for example the first record: Number: 0123456789 FCode: 12345 should result to have value CCode 6 to be added in the
Sybase select value from XML field
I have a table named data_values stored in SYBASE in the following format: The value field has the following XML format: I want to select from the value field values like value1, value2, etc. but I can’t find a correct query to do this. I have tried: also value is a reserved keyword I think and I need to escape
TSQL “where … group by …” issue that needs solution like “having …”
I have 3 sub-tables of different formats joined together with unions if this affects anything into full-table. There I have columns “location”, “amount” and “time”. Then to keep generality for my later needs I union full-table with location-table that has all possible “location” values and other fields are null into master-table. I query master-table, However some “location” values are dropped