Newest tsql Questions

Q&A for professional and enthusiast programmers

How to use COUNT() in more that one column?

Let's say I have this 3 tables Countries ProvOrStates MajorCities -----+------------- -----+----------- -----+------------- Id | CountryName ...

Generate scripts for trigger in SQL Server

I am using the below code get completed code of triggers in the selected database. select [definition] +' ' + 'GO' from sys.sql_modules m inner join sys.objects obj on obj.object_id=m.object_id ...

SQL: How to get the date yyyy/mm/dd based on the year and day number?

I have the following string 2015089 or 2016075, for example. I need to get the result in yyyy/mm/dd format based on the given input. So, based on 2015089, I get, 2015/mm/dd. dd is a 89th day of 2015 ...

Update Stored Procedure - only update certain fields and leave others as is

I'm currently making a stored procedure which will update a products details. I want to make it (if possible) so that only the fields entered under 'values' column when being executed are updated and ...

Updating one table's column in SQL Server from another

I have a table of measurements from weather stations, with station names (in Hebrew): I also have created a table of those weather stations with their latitudes and longitudes: I've written a query ...

Backup SQL database on button click event using c# when using a web application

I am making a web application as part of my college project which uses a database in SQL server. Now, I want to backup the DB using c# code on button click event. I have tried searching, but have only ...

Would like to know how a table is populated

Good morning! I am using TSQL, there is one table that was created years back by another empolyee, I am not sure how this table is populated? I checked the dependencies and it just gives the name of ...

SQL INSTEAD OF Condition not evaluating

I'm building a team roster and trying to prevent an INSERT if it's determined that a given team already has too many members. The trigger fires, but the condition doesn't evaluate. This is the ...

get xml data from differents nodes and tags

Im trying to insert XML data in a temporary table DEPARTMENT name="Administration" Revision="" IsRevision="False" Configuration=""> <FIELD name="Name" value="Jean" type="char" /> <...

Invalid Column Name with INNER JOIN and Multiple Tables

Im currently writing a stored procedure in SQL to print results from multiple tables to find the top ten products purchased, but I am getting the syntax error "Invalid column name 'ProductID'". ...

SQL Query with multiple PIVOT

I have 3 tables as Register, Revision, Issue. table structure and some datas are as follows, CREATE TABLE [dbo].[Issue]( [ID] [int] IDENTITY(1,1) NOT NULL, [ProjectID] [int] NULL, [...

SQL Server : executing table-valued functions strangely

I created the RegEx functions on https://www.simple-talk.com/sql/t-sql-programming/tsql-regular-expression-workbench/ and tried them. When I run dbo.RegexFind on a subset of the data, it works well. ...

Building a sql statement that returns a set of rows then in the same table select a different set of rows and return the first and the second set

so i have a vb6 program that has a drop down that returns a recordset of ALL USERS and it exectutes a query to run against that set and returns the desiren data. the first drop down select is return ...

How to retrieve figures from a leveled data in SQL Server?

below is my table called "temp_table" in SQL, I want to get the data for the transaction no where column_3 is "CASH AED" Current Data: Transaction No Column_1 Column_2 Column_3 Price ...

Stuck doing a common table expression to calculate total orders for each product category using NORTHWND

Guys so as part of my job as a Data Support Analyst I am training up to become a software developer, my mentor gave me a group of test statements and this one seems way more advanced than anything I ...

SQL: How to create columns dynamically

I have a table which is created dynamically. So the number of columns is unknown at the time of creation. I want to create copies of each column in the same table with first column holding the first ...

Updating the id column's Identity Specification on an active table

I have a live (meaning it's used by my users) Table with a column "some_id" that was never set to be "Is Identity" when the table was created. I now have 2000 rows where some_id is NULL. Can I Change ...

Deduplication of records with transposed natural keys

What is the best approach to deduplicate records in which, by a exceptionally bad ETL design approach, two natural keys are transposed as surrogates for the other, or when one of those natural keys ...

how to speed up trigger

I have 800 records in a table, I used trigger to update more than 20 fields from 4 tables but very slow takes up to 25 sec to update changes, i know computed fields are faster but i cant use computed ...

How does one properly create an Link/Bridge/Crosswalk table when there is a need for more than one table to be on one side of it?

https://en.wikipedia.org/wiki/Associative_entity It appears that I am going to need to have an associative entity (also known as a Link Table, Bridge Table, Crosswalk Table, etc.) that will have more ...

SQL Find Records in Another Table Based (Using Like?)

I have been unsuccessfully trying to get this to work for the past 2 days and hope I can find someone who can help. One table contains major events and the other contains minor events based on the ...

in sql how to arrange rows of data into colum using Pivot

Here i have a simple table i want to display all rows respected that year display in single column Year Month Amt 1999 Jan 520 1999 Feb 100 199 Mar 200 2000 Jan ...

C# / TSQL decimal boundary check - Is there a cleaner way?

I wrote up a quick c# extension method, but wasn't sure if there was a cleaner way to accomplish what I want to do. It does work, but feels slightly hacky using the string repeater, and inserting a ...

T-SQL Concat 2 Rows with same Id from Join [duplicate]

I'm currently working on a query in T-SQL on SQL Server 2012. I gather several Titles from a table via a join from another table. My tables look like this: Table Project: ProjectId | -----------+ 1 ...

How to effectively sort nvarchar containing numbers and letters?

I'm having some troubles sorting a table effectively (the table contains a big amount of rows so any optimization would make a big difference). What I currently have that gives the correct result (...

T SQL For XML PATH Group By as Attribute or Element

I have been working on the T-SQL FOR XML with PATH Mode to create a Hierarchy based on the group by field. Below is my query and output. Pls help me with your valuable suggestions. Thank you. Good Day!...

Combining fields of various datatypes into one field

I am combining 6 different fields into 1 large lookup field. 4 of them are varchar, 1 is a double and 1 is a decimal(10,2). I am having an issue with the decimal field. Sometimes the second digit ...

Insert parent records and update linked children

This is such a simple problem, but I cannot figure out the syntax to do this. I'm doing some data migration, and I have some existing records in a table that I want to link to a brand new parent table....

SQL Incorrect syntax near as Assigning output to variable

I have a query like so: SELECT id, @RowNumber = ROW_NUMBER() OVER (ORDER BY id) as 'number' FROM WaitList WHERE email = @Email However I am getting error: Incorrect syntax near the keyword 'as'. ...

Tsql add column using synonyms

I have to add two columns to a table and I have a synonym associated with that table For example: My table name is table_abc and the synonyms is table_1 When i try to add column to the table in ...

If there is only 1 quantity, display the name, not the number

Imagine it like a student quiz. 10 Students get a C. 6 Students get a B. 1 Student gets an A. Rather than showing that 1 student got an A, is there a way to show the name of the student that got ...

Spliting rows into columns when value occurs in SQL Server

I have a a table with this values : ITPROD PROD1 PROD2 Quantity 1 Quantity 2 45842 69640 63908 3 2 70690 91387 90734 1 2 The result table should ...

Inserting data from a field with decimal(39,9) to SQL Server?

I have linked server in SQL Server; when I tried to add a record to SQL Server and store a value into a column with datatype decimal(38,0) using Cast(JrnlRow.Quantity as Decimal(38,0) as Quantity I ...

Sql replace vowels in a string if that string exists in another table

I have a table which contains specific words. Now when I insert a new post I want to replace the vowels in the strings that the word table contains before it is being inserted. So that's why I need ...

How to grant UPDATE STATISTICS to any table to a user

According to SQL Server documentation, to run UPDATE STATISTICS ON a table you need the ALTER TABLE permission. I would like my user to have the permission to update stats on any table (current and ...

Sql query that checks each GROUPBY or PARTITION BY contains a value

I'm trying to write a query that checks to see , for each 'group' in a GROUP BY or PARTITIONS BY, it contains a value. /\___ Not a very good way to describe my problem :( So, I'll give a fake ...

How to create a simple two table schema with TSQL?

I am trying to create a schema with two tables and will later query/add data to them. I am using the StackExchange Data Explorer "T-SQL": http://data.stackexchange.com/stackoverflow/query/new Here ...

the best method for insert bulk data into master/detail tables with stored-procedure?

Suppose, I have a master table the name of Order and detail table called the OrderDetail which the OrderId is identity key and using in OrderDetail as foreign key. Now i want insert bulk data with a ...

is triggers create any performance issues in Production

I am working on project where i am getting logs or data feed from outside in CSV and DAT file we have SSIS Package configured.i just wanted to create Trigger on table to reconcile table count with ...

Execute stored procedure on some event

I have a specific requirement: execute a stored procedure on Add Mode of Form and result of the stored procedure will convert in .txt file and save in specific folder on the network. Giving an ...

Convert int list to string with comma SQL Server

How convert a list of int to string with comma with function in SQL Server? Convert to : "68,74,58,64,67"

SQL Server : rename primary key

I have a table doc.MyTable which I want to deprecate by renaming to doc._MyTable. I then want to create a new doc.MyTable with the same primary key that the old doc.MyTable had. The problem is that ...

Recursive CTE with Offset Row for Metadata From Information_Schema

I have a multi-lingual database set up (it's actually more complicated than that - but I'm keeping it simple). The translateable data is put in a table called Fact. The data is linked to from a table ...

T-SQL CONCATE ROW values from #Temporary Table

I'm currently working on a SQL query in T-SQL on SQL Server 2012 with the objective to concate or merge several tables into 1 table and concate several rows. So far it's working out fine. I use STUFF ...

Getting rid of duplicates in a joined SQL query

I am wanting to know if there is any way to get rid of the duplicate row information that is it creating in order to fill space in the results when there are 2 items in one table, but only 1 in the ...

Using Triggers for Update

People tell me that using triggers is resource intensive and costly. I'm struggling to get my head around why in my particular instance. I've recently had to add "auditing fields" to some 50 tables (...

Join on a view T-SQL

I'm making a view. What i now have is: CREATE VIEW [dbo].[vwEventDetails] AS SELECT ISNULL(ROW_NUMBER() OVER (ORDER BY EventID), 999999999) AS Row, STUFF(DetectorID, len(DetectorID), 1, '0') ...

The precision exceeded the allowable maximum

I have the following Insert from select statement, data from Linked Server ODBC. But I get this error Msg 7354, Level 16, State 1, Line 8 The OLE DB provider "PervasiveOLEDB.11.0" for linked ...

Sql2000 replicate for xml path('')

I am trying to replicate the following code in SQL 2000 which does not have FOR XML PATH(''). select '"Footer",' + stuff((SELECT ',' + '"' + server + '"' FROM servers FOR XML PATH ('')),1,1,'') The ...

How to return function columns with null values

I have a function with about 40 columns with null values and these columns have never been used for the past 5 years. So I want to write a query that confirms that these fields have null values. I ...
Translating... 0%