Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.

learn more… | top users | synonyms (1)

0
votes
2answers
30 views

Get Column Value from Subquery in Stored Procedure

In my stored procedure i got code like @Select = '... (Select ...) AS NAME ... AS AGE ... AS CLASS ' @Where = 'CLASS = ...' I can make validation ...
3
votes
2answers
80 views

How to optimize this tricky SQL self-join?

I want to perform a complex self-join on a table. I know that this could in theory be done very efficiently (see below), but I have trouble getting SQL (on Microsoft SQL Server) to do so. My question ...
4
votes
3answers
256 views

The fastest way to get the most recent records

I am looking for the fastest way to return the most recent record for each reference number. I quite liked the solution from BrentOzar.com, but it does not seem to work when I add a third condition ...
0
votes
1answer
18 views

Perform Sum In Update Statement [on hold]

I am trying to run this update statement, but it keeps giving me the error of (3 row(s) affected) Msg 157, Level 15, State 1, Line 9 An aggregate may not appear in the set list of an UPDATE ...
2
votes
0answers
44 views

TSQL Generic Row number theoretical question

To start, We have a pretty complicated system that's been a compilation of over 20 years of code... And the code needs to work with SQL2005 all the way to SQL2014 (can't use the offset Fetch Next) ...
2
votes
1answer
24 views

Getting Dates In Range Into Table

I have a calendar table that shows all dates from 01/01/2015 to 12/31/2016, essentially the calendar shows the date and isworkday. I need to be able to pass a begindate and enddate to a stored ...
0
votes
2answers
49 views

SQL With Multiple Condition and Column

I have 3 tables: Employee +----+--------------+--------------------+----------------+ | Id | EmployeeName | EmployeePermission | Position | ...
4
votes
4answers
548 views

Delete into new table (like select into)

The select into syntax will create a new table to insert data into (I know that types may not be consistent etc. but it gives you a rough copy). Is it possible for a delete output into statement to ...
0
votes
1answer
43 views

Select Into removes IDENTITY property from target table

I am doing an exercise which removes the IDENTITY property from the target table when using a select into statement. source table name: Sales.Orders column name: orderid column property: int ...
0
votes
1answer
26 views

EXEC master.dbo.xp_cmdshell 'The user name or password is incorrect'

I am trying to move a file from a network drive to another server and when I execute this statement. I get an output of The user name or password is incorrect DECLARE @SourceFile AS ...
2
votes
0answers
42 views

What is the correct way to rename/swap a table in production

I have two tables, tblCode and tblCodeAlternative tblCode has about 4100 rows and is used in production. tblCodeAlternative has about 4200 rows and is in the production database but not used ...
3
votes
1answer
77 views

Aggregate data from multiple rows into single row

In my table I have rows, and each row has some data and its version. When column is not-null, it overrides an old value otherwise nothing happens. Pseudocode (in C#) var resultTable = new ...
1
vote
1answer
38 views

Compare Timestamp Difference Between Consecutive Records

How do I compare Timestamp difference between 2 consecutive records? This is my example: DECLARE @x TABLE(ProductID INT, SingleRunFailure bit, SampleDate date); INSERT @x VALUES (101, 1, ...
3
votes
1answer
62 views

Select data divided in groups evenly distributed by value

I would like to select into 4 groups the data from a table having the sum of a values in the groups as evenly distributed possible. I am sure that I am not explaining it clear enough so I will try to ...
3
votes
1answer
68 views

Where clause from OUTER APPLY using LEFT function

What would be a good alternative to improve my where clause that currently uses the LEFT function? Should I create a persisted computed column and an index to it? SELECT --columns-- FROM ...
2
votes
0answers
34 views

Cross database - Table valued Stored Procedure

I have the same table type in two databases (DB1 and DB2), DB1 has an stored proc (sp1) which has the above type as an input parameter. Now, I want invoke this SP from DB2. But, I am getting this ...
2
votes
1answer
32 views

String parsing: Extracting the filename from a URL

This is a link I have in a column of a table: http://earthexplorer.usgs.gov/browse/etm/1/11/2000/LE70010112000130KIS00.jpg There are like 1000's of rows like this. Is it possible to extract the ...
6
votes
3answers
209 views

Updating a view on multiple joined tables

Since MSDN does not say much, what happens exactly if I execute the following query? update claims set status='Awaiting Auth.' where status = 'Approved' Can I use the ClaimStatusName column of the ...
0
votes
2answers
14 views

Create new instance MS SQL Server 2014

I have installed MS SQL Server 2014 , but after installation there is no instance created. There is only the default instance. How make a new instance in existing MS Sql Server ? I found a lot of ...
1
vote
2answers
167 views

How to use SET operation in CASE clause in SQL?

I have the following structure, and I'm doing a SET operation in a CASE clause but it is producing an error: Incorrect syntax near SET Query: DECLARE @tbl_Result TABLE ( RowID INT ...
2
votes
2answers
185 views

Use Distinct With Sum?

I want to sum a few values using case statements. My problem is compiler complains if I do not add a group by to the end of the query, which then that negates my Distinct, hah. For example, in my ...
2
votes
2answers
50 views

DBCC Usage on production system

We have a system running SQL Server 2008 R2 on a Hyper-V VM (64GB RAM and 16 virtual cores). Another VM (the application server) within the system accesses the db server quite intensively for both ...
-2
votes
3answers
57 views

Extract Portion From A Decimal [closed]

I need to extract any value > .5 from a decimal. Here is sample ddl Create Table #b ( fv decimal(16,4) fv1 decimal(16,4) ) Insert Into #b (fv) Values ('.8032') How can I capture the .3032 and ...
3
votes
2answers
96 views

Convert int to RGB or hex color

I'm working with a database where colors are stored as integers. For some unknown reason, they are negative integers actually. Now, I need to use them in the CSS background-color property, which ...
0
votes
0answers
42 views

Query Freezes and SSMS Crashes

I feel like I have the syntax set on my query, but anytime I try to run this SSMS freezes and I have to reboot and once I get my PC back up and running no records have been updated. The table only ...
1
vote
1answer
30 views

How to combine 2 SQL statements into one?

I have these 2 SQL statements. Each year, I need to add the new one for the new DB. How can I write it so it will just work for the new years instead of me manually editing this each year? Is there a ...
2
votes
2answers
46 views

Finding and listing all columns in a SQL Server database with identity [duplicate]

I wanted to RESEED all the identity columns in that database, so I need to create a script so I can find out which column in which table has the identity property.
1
vote
2answers
61 views

SQL Server: Query Efficiency Having vs Subquery

I have the following query where I used a sub query since I can't use aggregates in the where clause. SSMS suggested I use the HAVING clause but then I would need to group by all the other columns ...
2
votes
2answers
39 views

How do I create a SQL Agent Alert on a custom error message in SQL Server 2012?

I want operators to be notified when I throw a custom error message. My understanding is that I need to add a message to sys.messages, then I can either RAISERROR or THROW that error ID. Creating an ...
1
vote
2answers
104 views

Transforming multiple rows into a single row

In my company, I need to join table A to table B: Table A is the sources table Table B is the empty table I want to know that how can I do it and the result like table B? Source table A: ID ...
1
vote
0answers
59 views

reset identity of a column on a new primary key record

I have the following table CREATE TAbLE aTEST_person ( person_id int default (NEXT VALUE FOR [person_id_TEST]) , version_num int, name varchar(255) CONSTRAINT ...
0
votes
0answers
11 views

Why is Echo statement in SQL query not writing in text file? [duplicate]

Following code is used for writing a number in the text file but instead it is writing "Echo is ON" into the text file. SET @CMD = 'ECHO '+@Ver+'>'+@Vercheck Print @CMD EXEC xp_cmdshell ...
1
vote
3answers
356 views

Using Echo statement writes Echo is ON in the text file

Following code is used for write a number in the text file but instead it is writing "Echo is ON" into the text file. SET @CMD = 'ECHO '+@Ver+'>'+@Vercheck Print @CMD EXEC xp_cmdshell ...
3
votes
3answers
62 views

Sum values from child tables and ignore equal rows

I have two child tables with a value that must be summed separately and at the end the totals must be subtracted. The problem is that Child Table A and Child Table B can have 0 or more rows associated ...
3
votes
1answer
27 views

Split Field Every 1K Characters

I have a field that can hold up to 3000 characters. I need to split this field into 3 individual fields split1, split2, split3, I have successfully been able to figure out how to get the beginning, ...
3
votes
0answers
64 views

Query with sp_executesql returns nothing while query without it returns the result [closed]

I'm using MS SQL Server 2008 R2 through ORM in .NET application. This ORM uses ADO.NET, so any query will be wrapped in exec sp_executesql. One of the generated queries returns nothing, but it ...
0
votes
2answers
50 views

Format Phone Numbers In SQL Server

I am using SQL Server 2008 and I found this solution which is only for SQL Server 2012 - what is the equivelant to format a phone number in SQL Server 2008? FORMAT([PhoneField],'###-###-####')
2
votes
1answer
48 views

Check 1 Table If No Data Check Second Table

I am passing into a stored procedure two variables @begindate & @enddate. I need to check firsttabletocheck and get a count of where csdate is between @begindate and @enddate. I then need to ...
-1
votes
1answer
32 views

How can I automate the creation of SQL Server Agent Notifications on job failure?

There are no alerts setup on any of my SQL Server Agent jobs ( for failure,severity code etc.). Is there is a script to help or do I have to create an alert manually for every single job?
3
votes
2answers
59 views

Most recent order from multi join query

Here's the CTE version of the query, but it is returning multiple rows for the same customer. Not just the row containing the most recent open order date: with LatestOPenOrderDate_CTE AS ( Select ...
2
votes
2answers
51 views

RIGHT() works in a SELECT but not in an UPDATE? [closed]

I am trying to run a script which does some work including stripping the leading two characters from the string inside each row of a column. To do this I am using RIGHT() and it works fine when I use ...
4
votes
2answers
103 views

Replace special characters in a column with space

I'm trying to write a query which replaces the special characters with space. Below code helps to identify the rows. (alpha-numeric characters, comma and space is valid): SELECT columnA FROM tableA ...
1
vote
1answer
73 views

How to increase performance of UPDATE of ~ 50.000.000 entries

We have a query that uses a temporary table to update certain columns in the database: CREATE TABLE #NEW_OLD ( NEW nvarchar(20), OLD nvarchar(20) ); INSERT INTO #NEW_OLD VALUES ...
-3
votes
1answer
33 views

Problem with INSERT INTO [closed]

I can't make INSERT INTO Booking work. My code: INSERT INTO Gaest (pasNr ,fuldNavn , adresse, tlf , email ) VALUES ('1234', 'Simon jensen', 'Heimdalsgade 6, 9000','6691612' , ...
1
vote
1answer
38 views

Update Int Field [closed]

Why is my table not getting updated? For example, if I run this statement alone, I have a value of 6 returned Select COUNT(*) from #madeupland where SOapproved IS NULL however, when it is run ...
1
vote
1answer
38 views

Copy database to a remote server on daily basis

I need to replace the copy of my PROD db in a DEV system on a daily basis. The problem is that I haven't enough space in my DEV system to restore the PROD db. Is there any way to create a shrinked ...
0
votes
1answer
44 views

How can copy one column data of one table1 into other table2 column and other clumns table2 empty?

Here is my problem.I have two tables Stdd and result. stdd columns: id fname lname gender phonenum city result columns: physics chem math english urdu bio total id I want that in result table ...
2
votes
2answers
43 views

DayOfYear as per Indian fiscal year

I want to get sequence number according to the Indian fiscal year. I tried to write the function using SQL built-in function "DATEPART(DAYOFYEAR,'2015-04-01')" but at some point I am facing challenge ...
6
votes
4answers
188 views

Multiple column concatenation

How to concatenate multiple columns in to a single row? For example: id name car 1 sam dodge 1 ram maserati 1 john benz 1 NULL mazda 2 kirk lexus 2 Jim ...
6
votes
1answer
101 views

How to better use CLR Function from performance point of view (repeat inside each DB or have general function)?

I asked a question about validation of XML using XSD schema inside SQL Server 2012 (see link). I understand (as I suspected) that I need to use CLR Function. The function will get XSD schema text and ...