Tag: tsql

Create and execute stored procedure in SQL Server

I have four tables: dbo.Projects (id, ProjectName, Areas, PaymentSystem, Districts.id, purpose.id, types.id, etc) dbo.Districts(id, DistrictsName) dbo.Purpose (id, PurposeName) – has residential & commercial dbo.Types (id, typName) I want to select DistrictsName where PurposeName = ‘residential’ I tried this procedure : CREATE PROCEDURE [dbo].[SearchResidentialProjects] AS SELECT dbo.Projects.ID, dbo.Districts.DistrictName, dbo.Purpose.PurposeName FROM dbo.Projects INNER JOIN dbo.Purpose ON dbo.Projects.PurposeID […]

sql Optimize table valued function

I’m working with functions in SQL for the first time and I need a help with optimization. I am using SQL Server 2016. My function returns a table variable with a comparison of planned and done hours of employees at different projects in every month of a year. But the problem is that the query […]

Trying to pivot multiple columns in T-SQL

I have a query which dynamically generates different number of rows, with varying ID column values. I need to be able to PIVOT this into a columnar result. My current data result is below. ID Caption FieldName FieldType — ——— ———— ———— 10 Caption 1 Field Name 1 Field Type 1 11 Caption 2 Field […]

MS SQL temporal table update failure

I cant find anything to explain why when calling a SP that does a insert or update depending on if record already exists on a temporal table i get Data modification failed on system-versioned table ‘MYDB.dbo.TemporalExample’ because transaction time was earlier than period start time for affected records. what dose that mean ? It only […]

TSQL CODE to set a value for a variable

I have a situation where I have a table with two dates lke startdate and enddate and a procedure in which there is a variable @offset. I need to write a tsql code for set the offset value as x when the current date falls in between the start date and enddate from the table. […]

Newly created CSV file has error during BULK INSERT

I would like to ask for your help. I created a program that would retrieve a data from a CSV file, read selected rows, and then create a CSV file with the data that was selected. After that, the system would then upload the data from the newly created file to the database, which ends […]

SQL Server – Using JSON to return Column Names

I have the following test query I’m needing to make dynamic. Basically a stored procedure will be passed @json and it needs to return the column names that are passed in that variable. Is this possible and how could I do it? declare @json varchar(max) set @json = ‘[“FirstName”, “LastName”,”DOB”]’; select * from OPENJSON( @json […]

User account audit for licensing purposes

Using SQL Server 2008R2. We are trying to audit user logins to see both how long user accounts have been inactive and to see how many licenses we will need coming up. I am currently pulling information from the following job: Insert into [User_Login_Audit] Select login_name, max (login_time) as last_login_time, last_successful_logon, (select @@servername) as server_instance, […]

Merge two tables and insert/update respectively

I have two tables and I have to merge the records. source: ID seq name designation company 001 1 aaa Developer YYY 001 2 aaa lead yyy 002 1 mmm consultant bbb 003 1 ppp developer yyy 003 2 ppp lead yyy 003 3 ppp manager yyy target: ID seq name designation company 001 1 […]

How can I get the 3 most frequently-appearing companions here

Table 1 – tblCompanion: companionid | companionname | who played ————————————————— 1 | River Song | Alex Kingston 2 | Rory Williams | Arthur Darvill 3 | Wilfred Mott | Bernard Cribbins 4 | Rose Tyler | Billie Piper 5 | Adam Mitchell | Bruno Langley 6 | Donna Noble | Catherine Tate 7 | […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.