Tag: tsql

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 | […]

SQL Server SQL Statement – Updating record

I have a data as below: I need to update Matching_id and Matching_Type by using column id, region, company, dept, subdept and amountsepend. The logic is: Sum AmountSepend by Region, Company, Dept and SubDept. If the sum amount is 0 then Matching_Type is ‘Match’ and Matching_id is the combination of the id for the matched […]

Execute SQL Task in SSIS – OLEDB Vs Ado.Net connection which one is better?

I am going to run a stored procedure which updates 3 tables of 60 million records each and all in same server. I am currently using OLEDB connection instead of ADO.Net connection. Points: Currently It is running for 21 minutes in management studio. If i use OLEDB connection it runs in 32 minutes If is […]

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