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

learn more… | top users | synonyms (1)

5
votes
2answers
112 views

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

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 ...
5
votes
2answers
3k 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
82 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 ('DOMAIN\...
-3
votes
1answer
56 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' , 'simon-30@hotmail.com');...
1
vote
1answer
44 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
44 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
49 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 ...
4
votes
2answers
173 views

DayOfYear as per Indian fiscal year

I want to get a sequence number according to the Indian fiscal year. I tried to write a function using SQL built-in function: DATEPART(DAYOFYEAR,'2015-04-01') but I am facing a challenge to get day ...
6
votes
4answers
493 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 ...
5
votes
1answer
241 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 ...
1
vote
1answer
51 views

Two If's And An Else [closed]

I am trying to run this t-sql query but I keep getting an error of Incorrect syntax near the keyword 'END' How should this be re-written in order to execute properly? What I want to achieve is ...
2
votes
1answer
34 views

Find Difference In Time

I am attempting to write a stored procedure to determine the difference in seconds between two times. If I were to hardcode the values I know I can use CAST(datediff(second, [start], [end])/3600.0 ...
2
votes
3answers
215 views

Return the uniqueidentifier generated by a default on insert

Goal Retrieve the latest guid value in real time after you have inserted the value in the table Problem Don't know how to do it Info The code should only specify new values for address and ...
0
votes
3answers
139 views

How to read and increment a number in text file using TSQL

I have a text file C:\abc.txt with a number 5 in the first line. I need to increment it into 6. Please help me with the T_SQL syntax. I have tried the following query to read the value but not able ...
12
votes
2answers
1k views

What does the position of the ON clause actually mean?

The normal JOIN ... ON ... syntax is well known. But it is also possible to position the ON clause separately from the JOIN that it corresponds to. This is something that is rarely seen in practice, ...
2
votes
2answers
95 views

T-SQL SELECT use multiple indexes for no reason

I have a question about indexer and execution plans in T-SQL. My database is SQL Server 2008. I have a simple three-table db schema: The InverterData table is very large and partitioned (28,880,...
0
votes
1answer
152 views

update statements shows update count but not updating the table

I am facing a strange issue which I have never faced before. I have a update query like below Update A Set A.Col10 = E.Col10, A.Col11 = E.Col11 FROM TableA A INNER JOIN TableB B on A.Col1 = B.Col1 ...
4
votes
1answer
175 views

Validate XML using XSD inside TSQL function in SQL Server 2012?

I need to validate XML using XSD inside TSQL. To make this task semi automated I need to do this inside TSQL function. To make validation I have to set value to XML and if it is not valid I will ...
-1
votes
1answer
58 views

Verify this TSQL to find the indexes fragmented over 50%

select OBJECT_SCHEMA_NAME(IPS.object_id) 'schema', OBJECT_NAME(IPS.object_id) as table_name,index_type_desc, round(avg_fragmentation_in_percent,2) 'avg_fragmentation_in_percent', si.name ...
0
votes
2answers
64 views

Insert Data in seconds between 30 minutes time interval [duplicate]

I want to divide my time interval from the start time to end time. The elapsed time should be divided across 30 minute time intervals so that for this input 08:05:13 12:09:49 I should get ...
8
votes
3answers
435 views

Updating a WHERE clause to check if a value is NOT in a separate table

I have a query which uses a WHERE clause, and I happen to use the exact same WHERE clause in many queries on this table (et al). The query is: SELECT DATENAME(DW, [AtDateTime]) AS [Day of Week] ...
7
votes
1answer
119 views

Unique identifier field with a conditional

I have a database that's not in production, so the main table being CustodyDetails, this table has a ID int IDENTITY(1,1) PRIMARY KEY column and I'm looking for a way of adding another unique ...
4
votes
2answers
92 views

Update fields that contains certain prefix by lookup table

I have a temporary table #NAMEOLD_NAMENEW which contains a column NAMEOLD and a column NAMENEW where every row contains the old and new value of a value to be updated. This table consists of roughly 2....
6
votes
1answer
93 views

Query tuning issues

I'm in trouble trying to tune this following query. I used to be 100% focused in the infrastructure side of the stuff, but since August this year I'm working for a processing card company that is ...
3
votes
1answer
52 views

Easiest way to find the larger version number?

I have a table that stores version information in multi-dotted strings, ie '4.1.345.08', '5.0.1.100', etc. I am looking for the quickest way to find out which is larger. Is there a faster, or easier ...
1
vote
2answers
80 views

Problem executing multiple procedures [closed]

I'm really new to SQL Server development and I learn everyday. Today I encountered a problem while executing multiple stored procedures in a query. I saw only one of both start. After a long research ...
4
votes
2answers
75 views

Are temp tables automatically created when referencing to them?

I can across the following script and I am trying to understand it. select columnA, columnB, columnC into #tmpTable from tableA The #tmpTable is referenced after that query, but not before it. ...
1
vote
0answers
32 views

Populate ID Field by Converting Group Name Values

This is a bit tricky to explain, but here goes. We have a System Rights database that loads system users and system rights or access levels into two different tables. The database is already built ...
4
votes
1answer
70 views

NOT IN (together with join) query

I have two tables : Part, OrderLine: Part (PartNum PK, PartDesc, Price) OrderLine (OrderNum PK, PartNum PK, QtyOrdered) And I want to find out the parts that were not ordered. I think I have a ...
7
votes
2answers
376 views

DBCC CHECKIDENT ('table name', RESEED, 20000) not working

When I run the script DBCC CHECKIDENT ('table name', RESEED, 20000) the output of the message is Checking identity information: current identity value '200000', current column value '...
0
votes
1answer
34 views

Why the result is ranked by both amount and date instead of amount only? [duplicate]

Declare @ranking_term char set @ranking_term ='Amount' select Dense_rank() over (order by case(@ranking_term) when 'Amount' then 1 else 0 end, amount, case(@ranking_term) when '...
-4
votes
1answer
46 views

Why all ranking is 1 for this query [closed]

Declare @ranking_term char set @ranking_term ='Amount' select case (@ranking_term)when'Amount' then ( select Dense_rank() over (order by Amount desc) ) else( select ...
4
votes
1answer
45 views

Is is possible to combine local variables in a case statement?

DECLARE @CalcQuery varchar(340) SET @CalcQuery = (SELECT sum(T0.[LineTotal]) FROM RDR1 T0 INNER JOIN ORDR T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN [@SES_SSNSBRG] T2 ON ...
0
votes
1answer
152 views

Update if not exists statement

If I have a simple table with 2 columns and a composite primary key consisting of both columns, how do I rename one of the columns but only if it doesn't cause a primary conflict? Here's a simplified ...
5
votes
3answers
156 views

What are the SQL Server 2012 versions of FIRST() and LAST()?

I have a table with a value column. I want to calculate the last row minus the first row, as shown here: id value 1 10 2 45 3 65 4 95 . . . . ....
2
votes
3answers
209 views

Explicit transaction left open after try/catch

I recently wrote a T-SQL script to perform some updates and inserts on 3 different tables. I wanted it done in a single transaction, so I read up on the Microsoft documentation on how to use explicit ...
5
votes
2answers
243 views

How to name table-variable function unique constraint?

I am renaming some unique constraints to match our database objects naming convention. Strangely, there are several multi-line table valued function which returned table has unique constraints as ...
7
votes
2answers
106 views

Enforce Data integrity on an Email column in a database

I want to put a constraint on an email column like so: xxxx@xxxx.yyy Obviously "x" is of various lengths and x is some piece of string data and .yyy is a domain type of .com, .gov etc
0
votes
1answer
44 views

Trigger to drop table only if there are no records

How do I write a DDL DROP_TABLE trigger that will only drop the table if there are no records?
0
votes
0answers
32 views

The best way to raise error from mass insert query

I have a following sproc: CREATE TABLE Table1 (val INT) GO CREATE TABLE Table2 (val INT) GO ALTER TABLE Table1 ADD CONSTRAINT chk1 CHECK (val > 0 ) GO INSERT INTO Table2 SELECT 1 UNION ALL SELECT ...
1
vote
0answers
141 views

xp_delete_file not working

I am running a query window under my own account and the following command: DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0, N'\\backup\sql', 'bak', '2015-11-24T23:30:02' ...
4
votes
2answers
223 views

Executing the same SQL statement for different tables

I have a stored procedure that deletes data from multiple tables. The SQL is all the same, except for the name of the table. UPDATE desttable SET desttable.reportarea = esz2reportarea.reportarea ...
1
vote
2answers
110 views

How to keep row ordering with DISTINCT command or how to add row number to cross apply

I have problem that after distinct, order direction of Variable column change. Here is example Data Source DECLARE @Table TABLE ( FirstName NVARCHAR(255), LastName NVARCHAR(255), ...
-7
votes
1answer
49 views

Need Immediate Help To Develope T-SQL

Hi DBAs, I want to Develop a T-SQL For Fetch data from following Table. But Condition is : I want to select only those data which status has all three status. suppose for id 1 : It has Only two ...
3
votes
1answer
30 views

Crosstab Pivot or not?

I am have difficulty wrapping my head around the following issue. I currently have a set of data in one table as shown below: As you can see, there is an invoice ref number along with a date, and ...
1
vote
0answers
84 views

Powershell To Run SQL Case Update [closed]

This is my syntax which throws an error of incorrect syntax near abcdefg. Can someone who is more experienced in the world of powershell take a gander and let me know how to remedy my issue? ...
3
votes
1answer
101 views

Unpivot with rows converted to columns as well as columns converted to rows

I've tried to understand unpivot examples on the web but they don't quite result in what I'm trying to do, which is to have specific rows turned into columns and columns turned into rows. To try and ...
2
votes
1answer
101 views

Updating table based on conditions (overlapping dates)

Say I have a table name CustomerBooking which contains Customers, checkInDate and checkOutDate as follows. I would like to achieve the outcome as shown in condition 1 and condition 2 with update ...
0
votes
0answers
20 views

SQL Server 2012 process (Stored procedure in SSIS Package via SQL Agent Job) stays running where it normally ends within 10 minutes

[] SPID 70 : SSISDB.catalog.start_execution;1 SPID 113 : EXECUTE ETL.uspLoadDataStoreData'dbo', 'Project', 0
1
vote
0answers
101 views

How Can I Increase Performance of This MERGE Statement?

I have a INSERT INTO/MERGE statement that is part of a stored procedure that needs to be really efficient. The execution plan shows that 90% of the procedure is due to the following statement shown ...