Newest tsql Questions

Q&A for professional and enthusiast programmers

Renaming an existing column without downtime

I want to rename a column in a table. I see two approaches Use sp_rename() and modify stored procedures to refer to new name. Create new column, copy data from old column to new column, modify ...

Unable to develop body for email in SQL

I have written a stored procedure where "i have to compare values in two tables and send mail to the users which are not present in second table. I have to automate this report . But issue comes ...

Why do I get errors on ON clause and group by?

I have this code which I have tested by constantly giving error on 2 lines: Msg 156, Level 15, State 1, Procedure GetUserPaymentsPartialRecordByClass, Line 25 Incorrect syntax near the keyword '...

How to write this script without use of temp tables

I am a C# developer learning SQL. I wrote the following scrip which works using temp tables. But, as I understand it, temp tables are to be avoided. Why should they not be used? How should I ...

SQL Server how to create a view in a specific database on a remote server

I am writing a dynamic SQL statement to create view on a remote server in a specific database. The SQL statement I generate gets executed on the master database. If I use use the 3 part naming ...

Drop column with PK constraint in empty table in a script

Scenario A table in SQL Server has two or more columns, but the original column with the primary key constraint is no longer needed. So now you want to write a script to drop the original column w/ a ...

Adding Carriage Return on STUFF()

I'm using TSQL STUFF() syntax (see below) to concatenate Client field into one single row, and the code works like a charm, but now I have another challenge, I need to add a carriage return for each ...

Unable to write exact sql query to get result set

I have below type of data set: Base Col1 Col2 Col3 1000 0 10 1100 1100 0 10 1210 1210 0 10 1331 For deriving col3, I will use formula like col3 = ...

How do I generate random date in specific range for each row in a TSQL Select?

My code is like following How do I generate random date in specific range for each row in a TSQL Select Declare @id int; Declare @randomDate; Declare crs Cursor For Select id from ...

T-SQL: create results in a comma seperated combined single row

Using this code: SELECT * FROM (SELECT EQUITY_DIVISION_25_NAME Broker, COUNT(tbl_user_details.USER_ID) new, DENSE_RANK() OVER (ORDER BY COUNT(tbl_user_details....

SSIS - truncate table when other table contains data with certain value

I have 2 tables on 2 different databases. When Table A from Database A contains data and a certain value is written in a specific column, then I need to truncate table B on database B and copy data ...

How to merge rows with diff values into 1? (SQL Server 2008)

First post here and quite new to SQL - hopefully this isn't too much of a silly question but I would appreciate some help. I have the following query: select t.staffID, t.lessonday, t....

convert query from t-sql to NZSQL

I need to convert a T-SQL query to NZSQL and I don't know any equivalent in netezza for following query SELECT CONVERT(DATETIME,[YEAR_MO]+'01') as TimeJoiner from XYZ Here [YEAR_MO] column has year ...

Generate calculated column with row_number() over partition by

I want to generate a column containing a unique number based on state and number of times that has come up in database It's like Ny_1 Ny_2 St_1 Ny_3 St_2 Up_1 Ny_4 And so on... I want a persisted ...

Transpose fixed length column to dynamic flexible rows based on the group by column [duplicate]

let me be more clear on my question, I know that there is pivot table where we can convert to columns to rows but we have to mention the all columns for rows like: Option1: SELECT *FROM ( SELECT SNO, ...

Why joining same table twice returns different column values?

I have a stored procedure that was not written by me. I cannot understand how they are joining the same table tblUsers twice and it returns different column values: select distinct usr....

Eliminate duplicates based on Order By SQL

I have a table with more than 20 columns and more the 300.000 rows out of which the relevant columns for my question are the following: column1 |column2 |column3 | date 123 | 657 | 2222 | 20 ...

Custom T-SQL Order By (IComparer)

Does T-SQL have any ability to do custom comparison of strings (for sorting), equivalent to .NET's IComparer? Something like the ability to give Order By a user defined function that takes 2 strings ...

convert negative value to zero in T-SQL

How to convert negative values to zero in a calculated column? we have inner join of multiple tables where a column is a calculated column taken from tables,i need to convert negative values present ...

where field = N'value' [duplicate]

Long time lurker, first time poster. Working on project for a non-profit group to help migrate queries from an offsite customer relations management (CRM) tool into an EDW. Full disclosure, I love ...

How to avoid function in the left field side of the predicate?

As per this article, First Rule: You can’t use a function in the left side (the field side) of the predicate. I have a table with a field TOTAL_ARREARS declared as [TOTAL_ARREARS] [nvarchar](50) ...

Network activity to T-SQL linked server during WAITFOR

We have to get data from a SQL Server linked server into another SQL Server database. Our connection to this server is very limited so I'm trying to be considerate and let go of the connection with a ...

SQL Server : most frequent value in each row

How can I find most frequent value in each row in SQL Server? Example: 1 a d a a c a b --> a 2 b a c b b b d --> b 3 h a h h b c d --> h 4 d d c h g p m --> d 5 e e g ...

SQL Server : concatenate values and ignore null or blank values

I am using SQL Server 2014 and I have a table with a list of addresses, with items in separate columns. For example: st number Prefix st name suffix Dir unit number city zip ...

SQL job that runs a SELECT query only returns one row

Long time reader but first time poster here. Hoping you can help me with some SQL im having trouble with. I am no expert so you may have to bear with me :-) Im trying to schedule a SQL job to email a ...

SQL - Is running RECONFIGURE dangerous in production

I need to run the following on my production server: EXEC sp_configure 'xp_cmdshell', 1; GO RECONFIGURE; GO My question is, Is there any danger running this command in a production ...

Naming query ouput columns using a separate query T-sql

Im looking for a way to name columns using the top 1 selection from a separate query. select case when t.test=1 then 1 else 0 end as select top 1 name from #names, case when t.test=2 then 1 ...

Best way to compare two large SQL Server tables row to row [on hold]

I am having two tables A and B in my SQL Server environment which are identical. I would like to compare them row to row and identify the discrepancies in the data. Can someone suggest me what's the ...

Entity Framework Search functionality with Dynamic SQL WHERE clause

hi guys i was using Dynamic SQL for search queries where i used to attach WHERE & AND clause piece by piece and form a statement, i recently came to below alternate for this, and life was amazing ...

Stored Proc returns 2 return values

I have a stored proc that first checks if an entry exists and if so updates it then returns the row ID, if not it inserts a new record then returns the row ID. When executing the stored proc I am ...

CTE partition not returning the correct row number - TSQL

I'm trying to get the latest file in a list of files using CTE. So far I have this: WITH CTE AS ( SELECT ID, TRISPATH, Document, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ...

Azure SQL: Invalid Object Name using Powershell's “Invoke-sqlcmd” on Adventureworks

I'm new to SQL, trying to query an Azure SQL Database (Adventureworks sample) using PowerShell's Invoke-SQLCmd. When I try the following command in Powershell: Invoke-Sqlcmd -Query "SELECT * FROM ...

create temp table and Store result of execute command at the same time

I have a stored procedure where I am running/executing a declared variable which is set to a select query for pivot table, important thing is that that pivot table is transforming column to n number ...

How to remove while loop?

just a quick question, how to simplify following query and also can I replace anything instead of while loop..? Thanks. DECLARE @CNT INT=0 ,@CNTER INT=1 ,@REF VARCHAR(20) ,@...

T-SQL : how to select row with multiple conditions

I have below data set in SQL Server and I need to select the data with conditions in order below: First, check to see if date_end is 1/1/2099, then select the row that has smallest days gap and ...

Insert typed xml to xml typed column from variable

I want to insert nodes to typed xml column from variable. Somethink like this: Declare @Xml as xml(PersonSchemaCollection); Set @Xml = N'<ns:role xmlns:ns="http://freespace.com/PropertiesSchema" ...

TSQL Columns to rows

I have the following table columns (with sample data) [Member_ID] - [GM1] - [GM2] ... [GM12] - [CATEGORY] 165 - 30 - 50 ... 40 - Products 165 - 70 - 60 ... 70 - Service 189 - 50 - 60 ... 50 - ...

SQL Dynamic HTML Body for Dynamic Pivot

I would like to send the the dynamic pivot query results as attach picture via email with SQL using HTML body. Is there anyway to do that ? HTML body has to be dynamic which also produce the columns ...

Get the groups items after a GROUP BY in SQL Server

I have a table that has these columns: UserID1, UserID2, ProductID, PurchaseDate The following query run in a purchases table and return couple of users that have more than a number of interactions ...

Openquery select into VS insert into (SQL Server 2016) Issue

I have the following SQL code to dynamically populate data into the tables but I now need to insert only. I am aware that select into also created the tables whereas insert simply populates existing ...

Find distinct value in table

I have a table like this : DH_doc_number DH_by_default DH_reference DH_your_ref DH_our_ref OF-82-YC 1 OF-82-YC 1 dfwf OF-78-YC 0 there can ...

The MIN function requires 1 argument(s)

Below is the code snippet in which MIN function using. When execute below code it is giving an error. CREATE FUNCTION [dbo].[FN_TempCalcTransportExemp] ( @EmployeeID varchar(20), @...

Why joining same table twice on two different columns returns different column values?

I have a stored procedure that was not written by me. I cannot understand how they are joining the same table tblUsers twice and it returns different column values: select distinct usr....

How I can get the Sum from following Query?

DECLARE @VARIABLE AS BIGINT SET @VARIABLE = Sum((Select Top 5 SUM(Quantity) FROM Orders Group by MenuItemName Order by Quantity Desc)) Print @VARIABLE This Will Return a Error : Cannot ...

TSQL - Query help for generating incremental sequence numbers

Need help on a query to generate a two incremental values on below table. Both the BatchNo and BatchSequenceNo should begin at 1. The BatchSequenceNo should increment by 1, up to 500 rows. At 501 ...

How to append output of each cursor iteration together using T-SQL (Html report designing)

I am trying to generate a HTML report for monitoring using T-SQL. I am not a developer/programmer but started to learn T-SQL. I am using a cursor to check and validate a result using case statement. ...

T-SQL Reset Row number when field changes, including repeated instances

I'm trying to find a way to show how many consecutive times an event types happens before a second event type happens. When I have that I need to show when an event type happens 6 times in a row To ...

Run R-Script to get data from GoogleSheets on SQL Server

This code works fine on Visual Studio OutputDataSet <- read.csv(file = "https://docs.google.com/spreadsheets/d/128qCX1YtvsHr4hERruFx6Ykn72qRkpRGH5brFULl7SY/pub?gid=0&single=true&output=...

How can I update a table by using subquery

I have got table which is "Stocks". There are thousands of records inside of this table and I need to UPDATE it acc. to following Example. (I use SQL Server 2008) STOCKS.stock_name STOCKS.a_id A....

SQL Server 2008 R2 - Recursive SQL - Is this possible?

I have the following table: | Article-Material1 | Article-Material2 | |-------------------|-------------------| | article001 | article002 | | article001 | article003 | | ...
Translating... 0%