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
1answer
264 views

Microsoft SQL Server creating an Inline function

Hey guys I'm completely new to SQL where I have to write a user-defined function (UDF) that calculates a student's GPA for a given time frame. The Inputs are StudentId int, ClassStartDateStart ...
2
votes
4answers
79 views

Query To Return Data From MAX(ID)

I need to return only the 1 row of the MAX(ID), but my query is not returning such. This is the result set that I get returned id bbid indate st val 200817 16543 01/01/16 1 ...
0
votes
1answer
42 views

Insert into matching on a WHERE clause

I'm sure this is simple but can't get it work. I have two tables, A and B, where one only A has ID and they both have Number. I'd like to copy all the ID's from A into B where their Number matches (...
0
votes
1answer
37 views

Unique key constraint violation not escalated to parent stored procedure

I have a stored procedure which calls 2 nested sub procedures. For Example, MyProc BEGIN BEGIN TRY BEGIN TRAN EXEC UpdateProc EXEC InsertProc COMMIT TRAN END TRY ...
3
votes
1answer
68 views

How to script XML Schema collection in SQL Server 2012?

I need to make script of XML schema collections in my Database and then insert each xml inside a table field as string. I need to do this to compare current schema collections with the new schema ...
1
vote
2answers
92 views

XML column performance in TSQL

I'm finding very limited reading on how a large xml column affects table performance in sql server 2014/6. For reference, it is a 36gig table and minus one xml column, it is 36megs. Some queries ...
1
vote
1answer
68 views

Convert hierarchy in an ordered list of rows

I tried to make a simple list that represents hierarchy of items. Tags have simple structure like [Id] ,[Name] ,[ParentTagId] I try to make the follwing result table: ID, ParentID, Name, Lev,...
5
votes
3answers
570 views

use of coalesce to check for all variables being null

How can I use coalesce to check for all variables with different type precedence to all be NULL without encountering errors. DECLARE @StartDate DATETIME = NULL, @EndDate DATETIME = NULL, @StateCode ...
0
votes
0answers
32 views

Azure invalid SID - is SID even necessary?

I'm migrating a database from SQL Server to Azure Database Server. I'm doing this manually, because many SQLs have to be rewritten (sp_addextendedproperty, XML Open commands etc). So I grab the ...
4
votes
1answer
124 views

INSERT INTO inserting multiple times

I have a table that contains all information about user called 'DataSource'. I have columns called 'First Name', 'Surname', and 'Address' (there's more but as an example). I have two tables called '...
2
votes
0answers
62 views

SSDT Syntax Error 80001 on every Select statement starting with “(”

Visual Studio / SSDT marks the following script line as wrong giving "SQL80001 Wrong Syntax next to SELECT" and also "SQL80001 Wrong syntax next to ')'" error. (select a from [tab1]) union (Select a ...
2
votes
1answer
68 views

How to merge multiple rows into one row with SQL? [duplicate]

Originally, I have one table, storing UserID and the corresponding value based on Year, Month, as shown below: Now, I want to 'merge' the rows according to the Year and Month value and to store ...
3
votes
2answers
51 views

Alter the letters after “ACCOUNTS\” to lower case?

I have a database table, users, with different username cases (see the example). How can I update the letters after ACCOUNTS\ to lower case? ACCOUNTS\James.McAvoy ACCOUNTS\michael.fassbender ACCOUNTS\...
0
votes
1answer
73 views

T-SQL insert with output expression [closed]

insert Table1 OUTPUT inserted.ID, s.[Id] INTO @Inserted select s.[Field1], s.[Field2], from @TableVariable s why is s.[Id] marked by red line ? Is it non-correct syntax to save value in @...
2
votes
2answers
124 views

Concatenating rows into a single string query running for 5 hrs and counting

I have a table with 2.6m records. It looks like this: email prject_name rafael.nadal@xyz.com lab1 rafael.nadal@xyz.com lab2 rafael.nadal@xyz.com ...
3
votes
3answers
110 views

Update Null Values With Other Values

I am new to the SQL Server world. In my example below, I have two companies: Building Blocks Red Cement Only one entry for each company has a value for the columns power1, power2, power3. I need ...
7
votes
3answers
165 views

T-SQL: If parameter1 is null, use parameter2

This is a simple one but I can't seem to figure it out. I have two parameters, 1 and 2. If one parameter is passed a null value, use the other parameter in there WHERE clause. Ex: SELECT ... ...
2
votes
1answer
76 views

Disable auto stats in SQL Server

I have couple questions about auto stats in SQL Server: I know I can use the following statement to check if the database auto stats is disabled, but is there a statement to check if a specific ...
2
votes
2answers
180 views

SQL server is there recursive reserve word?

Is there any reserve word 'RECURSIVE' while using CTE query. For example, WITH RECURSIVE node_rec AS ( (SELECT 1 AS depth, ARRAY[node] AS path, * FROM nodes WHERE parent IS NULL ...
0
votes
0answers
66 views

Running internal.cleanup_server_retention_window on SSISDB causes t-log to fill up

I have a SQL Agent job that runs internal.cleanup_server_retention_window on my SSISDB to keep it's size in check. The retention period is one day and it's scheduled to run each day. The problem is ...
0
votes
2answers
32 views

Id column based on other columns

I have a simple database with 2000 rows create table [TableName]( [variable1] [varchar](50) not null, [state] [varchar](50) not null, [date] [date] not null ) That look like this [...
0
votes
1answer
29 views

SQL Server to MySQL migration, replace '\xA0' with a regular space

I am having some trouble while migrating data from SQL Server to MySQL--it seems some data does not want to place nice. I am recieving the error: Incorrect string value: '\xA0\xA0' for column '...
1
vote
1answer
53 views

Group by other table with sum function

I have a query that pulls in a delivered count of a campaign. Each run is a new row in the runs table. To retrieve a campaign I need to join the related table in. SELECT c.Company, r.RunId, r....
6
votes
1answer
187 views

Create a function to generate a column value in a specific format

I am new to databases or, in fact, programming. I want to create a function or maybe a stored procedure (if it works) to insert/create default values for a column of the users table. In this table, ...
1
vote
1answer
29 views

Combining multiple-row data into a single row

I have a table that has columns like following ID Number Value1 User1 Value2 User2 Value3 User3 -- ------ ------ ----- ------ ----- ------ ------ 1 123 0 Jack 1 123 ...
7
votes
3answers
319 views

Should I add an auto-increment / IDENTITY field to a cross-reference table just for PK purposes?

I'm adding the following cross-reference table to my SQL Server-hosted DB: company_id bigint not null (FK) org_path nvarchar (2048) not null The company_id field refers to the id field in another ...
5
votes
1answer
76 views

Count instances of value in multiple tables

I'm attempting to write a query that returns the count of a given value across multiple tables. Right now, I'm using the following query SELECT COUNT(*) AS SL, ( SELECT COUNT(*) FROM [Sales Line ...
5
votes
1answer
232 views

How do I set a SQL Server Unicode / NVARCHAR string to an emoji or Supplementary Character?

I want to set a Unicode string variable to particular character based on its Unicode code point. I want to use a code point beyond 65535, but the SQL Server 2008 R2 database has a collation of ...
3
votes
1answer
61 views

Create a hierarchy chain (reverse path) for each node in a tree

Given a typical closure table, how would I write a query to get a list of ids back to the root? Is there a way to do that for all the [unique] ids? I'm using Microsoft SQL Server. Given: parent ...
0
votes
1answer
42 views

dropping SYNONYM is blocking in sql server?

I have synonyms which points to a TestDB1, by the next refresh synonyms point to TestDB2. while re-pointing from TestDB1 to TestDB2 it will drop all synonyms and re-creates them for TestDB2. Now my ...
-4
votes
2answers
52 views

Extract string from varchar

How I can extract the substring of a string from end to beginning? I have the following text, and I need extract the last "Modificado Por" and the "Creado Por" /* CREADO POR : Wxxwww ...
3
votes
2answers
177 views

select distinct one column with all instances of another column in a second column [duplicate]

If I have a table with the following information: ParentID | ChildID ---------+-------- 6 | 6 6 | 7 6 | 8 6 | 9 6 | 10 I need to ...
1
vote
1answer
42 views

Split databases into groups of roughly equal datasize

I am trying to split all databases from sys.databases into 7 roughly equally sized Groups based on data ROWS size. I want a table that contains every database and these databases are in 7 different ...
8
votes
2answers
280 views

Same code in multiple stored procedures

I recently joined a company, and I just noticed that many of the stored procedures have the same section of code repeated throughout. I noticed because I was tasked with changing a small section of ...
6
votes
2answers
265 views

Is there a T-SQL equivalent for punctuation as [0-9] is for numbers and [a-z] is for letters?

Is there a T-SQL equivalent of the [0-9] and [a-z] patterns that will let me pull values from a column that contains punctuation? For example: Create Table #Test ( Value VarChar(10) ) Insert Into ...
1
vote
2answers
43 views

Read TSQL variable from Temp Table

I am hacking around trying to write a script to restore a backup from a known folder location. The location will only ever contain one backup file. I have come up with this so far, but I don't know ...
0
votes
0answers
60 views

How do I code Apriori Algorithm in SQL?

Can I get an implementation of apriori Algorithm in SQL ?
3
votes
2answers
79 views

howto get the current line number from an executing stored procedure

Is there a function or keyword that will allow me to get the current line number in a stored procedure? I know there's an undocumented LineNo function that allows you to set the line number, and ...
1
vote
2answers
45 views

Use SQL Script Parameter to Map Drive

I am trying to map a drive to a UNC path where the UNC Path is a variable, but I keep getting an error saying: Incorrect syntax near '+' I execute this script: DECLARE @UNCSourcePath nvarchar(...
3
votes
1answer
121 views

Find the number of rows in a table from metadata

I want to write a single select statement to determine the number of rows in a set of tables in Sybase IQ. In SAS, I write this as CREATE TABLE dsnobs as SELECT libname,memname,nlobs, datetime() ...
2
votes
1answer
51 views

RedGate SQL Prompt - Cursor Snippet mistake

Using RedGate's SQL Prompt & loving it. But I used the "curff" snippet for the first time today ("Fast-forward read-only cursor"), and it produced the following TSQL (which I've filled in to make ...
11
votes
2answers
163 views

Recursive CTE to find Total for all children

Here is an assembly tree that I want to search using a recursive T-SQL Query (presumably CTE) with the expected results below. I want to know the total amount per assembly given any part. Meaning if ...
1
vote
2answers
82 views

Update table using values from another table in SQL Server

I have 2 table in my database. Table #1 ------------------------------------------------------------------------- | name | family | phone | email | gender | phone2 | address | birthdate | -----------...
5
votes
1answer
89 views

Benefit or use-case for empty variable / parameter / temporary table / temporary procedure names?

I just discovered, through sheer brilliance accidentally, that SQL Server allows you to create variables, parameters, table variables, temporary tables (local and global), and temporary stored ...
10
votes
5answers
1k views

How to hide/disable tables without dropping them to check redundancy?

I have to maintain and extend an old legacy system which contains webservice methods and database tables that are no longer used. Since I’m not entirely sure that the tables are really redundant, I’m ...
1
vote
1answer
40 views

SQL Server - Create Additional Database File for New Schema

I'm trying to create a change script for some upcoming changes. We're adding a new schema to the database and our client wants the new schema to make use of a new database file. How do I, in T-SQL, ...
1
vote
0answers
34 views

Resolve “stuck” system process of dropping indexes

I am re-vamping some indexes, and have decided to drop 3 on a certain table. The process has been running for almost 2 hours now. The table does have quite a few million rows, but I still feel that is ...
4
votes
1answer
94 views

How to check for Non-Ascii Characters

What is the best way to check if a VARCHAR field has Non-Ascii Characters? CHAR(1) through CHAR(31) and CHAR(127) through CHAR(255). I tried using PATINDEX and have run into the following issue. ...
0
votes
2answers
37 views

Compare possible null values in one query

Question is pretty short and simple: I have following query: SELECT * FROM CTE Now I want to get all rows with only maximum value of some field. SELECT * FROM CTE WHERE NodeId = (SELECT MAX(...
0
votes
1answer
36 views

Capture the Query Text of a DML Change

It may not be possible, but without doing a JOIN from the inserted/updated from the records coming in and adding them to an audit table column-by-column, is it possible to just capture the full DML ...