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

learn more… | top users | synonyms (1)

4
votes
2answers
74 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
30 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
69 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
195 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
42 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
80 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
140 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
140 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
231 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
105 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
40 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
30 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
113 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
221 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
88 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
48 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
28 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
76 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
83 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
92 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
98 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 ...
3
votes
1answer
174 views

SQL to Find Partial Duplicates of a Field

I have a table with a field for a member number. The table can contain records with duplicate values for this field and does. I have a SQL query that will obtain any duplicate values for this field, ...
1
vote
1answer
90 views

T-SQL Check another table rows if match delete them

I am creating a script in SSIS where I want table A to check if rows exist in table B. If they exist, they must be deleted. Table A is an archive table named Customer_archive Table B is a table that ...
2
votes
1answer
130 views

Deterministic function for getting today's date

I am trying to create an indexed view using the following code (so that I can publish it to replication it as a table): CREATE VIEW lc.vw_dates WITH SCHEMABINDING AS SELECT DATEADD(day, ...
1
vote
0answers
63 views

Migration script fail and revert on first error

I have a db migration script about 4000 thousand lines. I would like to compose it when it reverts all changes on first error. What is the proper way to do that? Is wraping with try/catch is the ...
3
votes
2answers
101 views

Data extraction with multiple delimiters?

I have a legacy data source column that is delimitted by semicolons and commas. The first semicolon indicates the last name, the second indicates the first and middle name (or initials), and the last ...
1
vote
0answers
45 views

Window function vs group by method: find value for MAX ID

I'm trying to understand the performance issue with Window function when i select value for MAX ID --Old group By method SELECT tj.MainProcedure , tj.StartTime , tj.EndTime , ...
1
vote
1answer
55 views

Stored Procedures execution permissions

So what I am trying to do is to limit the access to a stored procedure so that only another stored procedure may call it. Here is an example: create procedure Database_Deployment_Proc -- This ...
0
votes
0answers
15 views

Updating column as the value selected by user in multivalued parameter

I am creating a report in which I have data from 11 different databases... I have created multi-valued parameter... user selects the state... I want to add a column to my result which should display ...
1
vote
1answer
49 views

Forbid user to query internal SQL server information

I'm trying to forbid database user to get SQL server internal information. Script that creates DB user: CREATE USER [testuser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo] DENY SELECT, INSERT, UPDATE, ...
5
votes
2answers
46 views

SQL Server 2008R2 - MAX Result and Hour per Day

I have a table full of network interface statistics. InterfaceID TimeStamp RCV_bps XMT_bps ----------- ------------------------------ ------------- ------------- 275 ...
0
votes
0answers
45 views

How do you use Extended Events to find port numbers?

I'm migrating some TSQL code from one server to another. The server I'm moving it from is production, so I'm not easily able to adjust settings for obvious reasons. The code runs fine on the ...
0
votes
1answer
36 views

Finding keywords in SQL script

I have a list of 50 columns in a database that are likely to change or be removed within the next few months. I have a task to identify any SQL scripts that reference these tables. Up until now, I ...
2
votes
5answers
292 views

Order By DateTime With 00:00:00 Last

order by datetimecolumn orders a datetime column like this: datetimecolumn 2014-09-04 00:00:00 2014-09-04 00:00:00 2014-09-04 01:00:00 2014-09-05 00:00:00 2014-09-05 01:00:00 2014-09-05 02:00:00 ...
1
vote
3answers
248 views

Need to get identify all tables and columns in a SQL Query

I have a massive task to go through hundreds of SQL scripts and identify the Table and Columns in each query as we are updating our system and some of the columns and tables may be renamed or ...
1
vote
1answer
57 views

Procedure completes prematurely after converting comma-delimited value strings to XML

I have a procedure which executes beautifully within SSMS environment yet, when I hook it up to a web page, instead of seeing a list of packageIDs returned, the return is 'null'. I've checked ...
4
votes
1answer
116 views

Minimum distance of a location; one table of points compared to a set of points SQL 2012

I am trying to find the quickest way to find the minimum distance of one point to a table of points. The only caveat is that the table of the points I'm trying to find the minimum distance of is 150K ...
0
votes
1answer
41 views

How to pivot this unpivot?

This is the pivot I made: SELECT * FROM ( SELECT *-- codigo,ID,Grupo,convert(int,CodOpcao) as CodOpcao FROM ( SELECT * FROM ( SELECT 39 as codigo , ...
1
vote
1answer
101 views

SQL Server database has corruption, but displays no errors

We have an extensive database of tables. I moved over a table from our dev db to our production db using a program called SQL Compare. I just copy paste the SQL it produces into Management Studio and ...
1
vote
3answers
102 views

Copy table structure excluding nullability into local temporary table

How can I copy the structure of a table into a temp table (or table variable) excluding the nullability of each column? Mainly I need to copy the column names and data types, but not nullability, ...
1
vote
0answers
27 views

Changing Action on Delete to Cascade

We have a rather big database (about 400'000'000 records) in roughly 100 tables. The foreign keys in these tables are defined, so that No Action is taken, when deleting data from a table. We now have ...
0
votes
0answers
16 views

build users availability report/Table (using sql)

I got a question from our product team to build a "Table availability of customer service representatives (user)". f For example : user_A was available to accept a call from 12:00 to 12:03, so I have ...
0
votes
1answer
34 views

How to exporting a databse structure (or many tables) for import

In TSQL: When right click a table you have the option to Script table as > Create which creates the table script including the constraints If you want to port the database or large portion of the ...
-1
votes
1answer
94 views

Error with Loop using Cursors - Invalid Object Name with Set = @

Working on a script to Loop through databases to query one table that each Identical Database has with cursors then return result set to table for each database. I think I have the structure for Set ...
0
votes
0answers
17 views

Execute each row using cursor [duplicate]

I have a strange situation. I am trying to change the size of the log file auto growth to 64 MB for all Dev and QA environment. I have close to 200 servers and 3000 DBs. I have run the below command ...
1
vote
3answers
93 views

Alter DB modify log file for all DBs

I can run this command to make changes for 1 DB USE [master] GO ALTER DATABASE [DBName] MODIFY FILE ( NAME = N'Name_log', FILEGROWTH = 10000KB ) GO Is it possible to create a script which changes ...
0
votes
2answers
46 views

Column name or number of supplied values doesn not match table definition

I have created three tables using USE UserDecomAudit GO CREATE TABLE ADUserData ( ADUserID int NOT NULL PRIMARY KEY, Name varchar(50), SamAccountName varchar(10) NOT NULL, DistinguishedName ...