Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [t-sql]

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

0
votes
1answer
22 views

T-SQL - Script all tables creation, contraints, indexes, keys and triggers

I'm using SSMS and I was wondering if there is a script that can generate tables creation, constraints, indexes, keys and triggers for a given table names as a single result and another script that ...
1
vote
3answers
34 views

Setting a parameter from a statement that starts with EXEC

I am trying to get the Server Name from a LinkedServer and assign it to a variable. From this question (Get @@SERVERNAME from linked server), I found that either of these return the Server Name. ...
0
votes
1answer
21 views

Splitting row into multiple records based on row value

I have an Orders table (text customer_id, int total_orders) which contains overview stats of total orders for each customer. What I am trying to do is generate a query output that will represent a ...
1
vote
1answer
34 views

Using LIKE for a Range of Numbers

In my WHERE clause, I'm trying to use LIKE for a range of numbers. For example: WHERE rr.sequence LIKE '05600%' OR rr.sequence LIKE '05601%' OR rr.sequence LIKE '05602%' OR rr.sequence ...
0
votes
1answer
29 views

Combination Identifier

I want to identify in one column those who are present in the data but there is limit to only 10 nests. Is there easier or some other way for me to get the result that I want? I hope you could help ...
0
votes
2answers
46 views

How to reduce the mdf file size in this database which I have 50 GB reserved space

I have a DB with 50 GB of size I need to reduce the size of it. How to reduce the file size of it? I already did this: shrink | files | selected 'Data' as filetype. but still it shows 50 GB Reserved ...
1
vote
1answer
33 views

Instead of Delete trigger

I want to pass one validation DML statement on the trigger. Condition is below DML statement select distinct t1.cid from table1 t1, table t2 where t1.cid=t2.cid and t1.status<>'...
1
vote
3answers
60 views

updating a field with a partial pass parameter

I am working on a table that has 24 columns named pp1, pp2... pp24. I would like to pass in a parameter that would be used to help identify the column to be updated. one parameter is the value (8) ...
6
votes
4answers
595 views

Does LocalDB support temporary tables?

Are SQL Server temporary tables (prefixed with #) supported in LocalDB instances?
0
votes
1answer
92 views

SELECT shows different amount of rows [closed]

I have a job which each day (at 6:01pm) copies 516 rows from a table and seeds them with tomorrow's date INSERT MYDB.MARKET_DATA( MARKET_DATE, CURRENCY_BASE, ...
0
votes
0answers
38 views

sys.login_token doesn't include particular windows groups while they can be listed by sys.database_principals

When I query SELECT principal_id, name, [type] FROM sys.login_token I found some local windows groups were not there. However, when I check by SELECT principal_id, name, [type] FROM sys....
1
vote
1answer
30 views

How to update sys.login_token without restart SQL Server Service

I query sys.login_token to check if the current user is in a particular windows group. SELECT principal_id, name, [type], usage FROM sys.login_token WHERE principal_id > 0 But after I added the ...
0
votes
1answer
25 views

merge back to back rows based on start & end date

I have a table as below, each row has status, from and to ordered by Zone. Where To of a current row always matches From of next row in that zone. Zone Status From To z1 Open 2015-...
-1
votes
2answers
39 views

SSIS Balanced Data Distributor vs T-SQL Insert [closed]

Currently researching performance difference between SSIS Balanced Data Distributor and T-SQL Insert. We want to insert large amounts of data from one table into another; tables are in the same SQL ...
1
vote
1answer
35 views

Query of sys.login_token doesn't include particular windows group while connect within workgroup

I had two SQL Server Logins which correspond to two windows groups: MachineName\MyAppAmdin MachineName\MyAppUser And I use select principal_id, name, [type], usage from sys.login_token where ...
1
vote
0answers
31 views

Openrowset is throwing error for file access as Access Denied

I am getting Access Denied error .. Openrowset is throwing error for file access as Access Denied I tried running these 2 queries as well.. EXEC sp_configure 'show advanced options', 1 RECONFIGURE ...
-2
votes
0answers
46 views

Why variable is assigned NON NULL value in CASE expression

Usually, when I want to assign values to a variable from a lookup table I use MAX with a CASE expression. For instance the below snippet. EDIT:1 The status_name is unique. DECLARE @new_id INT,@...
4
votes
2answers
98 views

Optimizing table valued function SQL Server

I am trying to optimize this table-valued function. If I could, I would change it to the procedure but I cant. The problem is with two update statements. I kept only those two in the function because ...
6
votes
4answers
196 views

Finding the Min value for a subgroup, filtering out preceding Min values within the same parent group

How do I get the minimum value for a group (Acc, TranType), but filtering out any minimum values used in preceding rows for the Acc group. Preceding rows would be defined as Acc asc, TranType asc. ...
2
votes
3answers
189 views

Insert self referencing entry into SQL Server

I have a Person table that has a created_by column that references the primary id of the table itself. So, it could be an employee that adds another employee to the database. It works fine. But ...
2
votes
1answer
45 views

Custom sort before ROW_NUMBER() for paging

I have the following SELECT results.*, coinCat.*, cat.* FROM (SELECT c.*, ROW_NUMBER() OVER (ORDER BY c.CoinId) AS RowNumber FROM coins c) AS results INNER JOIN CoinCategoriesCategories ...
0
votes
1answer
33 views

finding the most recently updated record with multiple date columns

I have recently inherited a SQL Server 2016 database which was converted from an old MS Access db. I have a table that shows various repair dates and test dates for associated serial numbers. I would ...
1
vote
1answer
26 views

Update badly formed XML to allow parsing

I have inherited a production system that needs some fine tuning to enable easy extract of the result set. A record gets added to a table which contains an XML field, with contents similar to the ...
1
vote
2answers
90 views

T-SQL Splitting rows into multiple rows

I have a unique request here. I have to split rows for each ID into separate rows, but moving sets of columns into separate rows. Kind of hard to explain with just that so here is a visual ...
2
votes
1answer
86 views

what does a “/” mean after a sql statement

I have a script that we are using to update our ERP database to a new version, UPDATE ENUM_CODES SET COLUMN_NAME = 'PAY_STATUS' WHERE TABLE_NAME = 'PAYABLE' and COLUMN_NAME = 'STATUS' / but i dont ...
5
votes
2answers
942 views

How many rows can SQL Server process in a single INSERT statement into a table?

To illustrate my question, the following is a query detecting order's id which have not been inserted in a data warehouse and inserts them: With NewOrders As ( Select OrderID From Orders ...
5
votes
5answers
123 views

How to tell which windows group login I used when logging in via windows authentication

I created two SQL Server Logins which correspond to two windows groups: MachineName\MyAppAmdin MachineName\MyAppUser Then in the database, I created two users with the same name and mapped them to ...
2
votes
2answers
26 views

Converting and Subtracting Two Dates

I'm trying to get the numeric difference between two dates. (Ex: 11/30/2018 - 11/05/2018 = 25) One of the dates (@EndOfMonth) represent the last day of the prior month and is being converted into a ...
2
votes
1answer
63 views

Best way to compare multiple non null/non zero fields in a row in T-SQL?

I need to compare figures across a row and show where there is any mismatch, excluding zero and null values. So in the example, ids 1, 4 and 5 would be returned. What is the most painless way of doing ...
1
vote
1answer
29 views

Matching dates with conditions and filtering in a table

So I have a tables like this id | date1 |date2 |value 1 | 1/1/2014|1/1/2004|100 1 | 1/1/2014|1/1/2004|100 1 | 1/1/2014|1/1/2009|200 1 | 1/1/2014|1/1/2009|200 1 | 1/1/2014|1/1/...
1
vote
2answers
54 views

How can I add “in” keyword in this code to accept more than one value in gender and age parameters using SQL Server 2012

I want the parameters in Age and Gender to accept more than one value Create Procedure [Admission].[SelectPlayerData] @MembershipNumber varchar(20), @PlayerName varchar(70), @PlayerNameEnglish ...
3
votes
1answer
80 views

Finding gaps on intersection dates

This is a problem I've been trying to work through on and off for the past month, and haven't made any progress. Essentially, I am working with a HR system that stores two major types of position for ...
1
vote
2answers
42 views

Dynamic SQL - How can you use it to run a SELECT against all schema's and Tables?

Scenario I have a database that contains all of our customer data, which is separated by Schemas. For example: Customer1 = Schema1, Customer2 = Schema2, Customer3 = Schema3, etc... Sometimes we ...
0
votes
1answer
48 views

SQL Insert via mappings table

As part of a SSIS ETL process, I have multiple staging tables. I have used xxxx just to resemble data: e.g. tblStage1 Field_ABC Field_ZXC Field_QWE xxxx xxxxxxxxx xxxxx tblStage2 ...
2
votes
1answer
86 views

Assign a student to a Classroom based on course and scheduled hour

Each year we have to manual assign students to classes that were not included in original assignment either they are new or the class was cancelled etc, we are talking 100s of students where we have ...
2
votes
2answers
56 views

Is there an easy way to have a non-IDENTITY, auto incrementing column based on another column?

+----+-------------+-----------------+-------------+ | ID | ProductName | ProductCategory | ProductCode | +----+-------------+-----------------+-------------+ | 1 | Item 1 | 1 | ...
-2
votes
1answer
47 views

DROP and CREATE versus ALTER [closed]

QUESTIONS: Is is better to ALTER an object definition (i.e. function, stored procedure) in a production environment, rather than IF EXISTS (...) DROP (...) CREATE...? Does SQL Server treat the two ...
0
votes
1answer
65 views

Move Large number of rows

I am trying to move close 88101840 rows from one table to another. To handle log growth on the server looking at doing 10000 rows at time. Please have a look at the query below. Just want to find if ...
0
votes
2answers
89 views

Create a View from Multiple Databases

Database X - tableCust - Column-CustNumber Database Y - tableCust - Column-Custnumber Datbase Z - tableCust - Column-Custnumber I'm trying to create a view in database H from those three databases ...
0
votes
1answer
33 views

How to secure SQL Agent Job that INSERTS to a linked server

What is the proper way secure a T-SQL agent job that moves data between linked servers? Setup: Microsoft SQL Server 2012 (SP3) Enterprise Edition SQL Server Agent running as NT Service\...
1
vote
1answer
138 views

Select data from the previous 3 months [duplicate]

I need help selecting data where the date is in the previous 3 months. (note: I'm not asking for the last 3 months i.e. DATEADD(MONTH, -3, GETDATE()) ) The reason I don't want to use the DATEADD ...
3
votes
1answer
78 views

SELECT Parent-Child with SORT

I have a simple, one level parent child relation table, with following columns: ID_Asset| Parent_ID_Asset | ProductTitle I need output grouped by Parent followed by children, and also sorted by ...
0
votes
1answer
38 views

Forcing view to use certain execution plan

Can I somehow force an execution plan to a view? I came across to parameter sniffing and I cannot add option recompile to a query because it is generated by DevExpres, so the only option is to ...
0
votes
1answer
29 views

Query to retrieve aggregated data using variables

Question: Summarise events using the MIN, MAX and COUNT functions Help: To do this, create variables to hold each of the aggregates: Variable What it will hold eventually --------------- -...
0
votes
0answers
20 views

Create views that depend on each other in any order [duplicate]

Is there a way to turn off the check that a CREATE VIEW does not use a non-existing view? I am creating a sql file containing some CREATE VIEW lines, but I dont want to go through the hassle of ...
-1
votes
3answers
42 views

Restore master database

I want to restore my master database (.dba ). I am not able to restore from PowerShell because the backup of the database is taken from a different version of the SQL Server and the PowerShell or cmd ...
2
votes
2answers
249 views

Create stored procedure that contains newlines with a oneliner sql statement

I want to put code in a single line that will create a stored procedure that contains newlines. Is that possible? Do I need to use sp_executesql? How do I escape newlines in a sql statement? How do I ...
1
vote
1answer
71 views

Don't RESEED tables on Query Store Clean

We are using Query Store. And now we have solved one problem with it by cleaning the Query Store every night. And when you clean the Query Store, the Query Store tables are being RESEEDed :-( Do you ...
-1
votes
1answer
41 views

SSIS Error :Cannot roll back .No transaction or savepoint of that name was found

Hope you are Doing well. I have 1 SSIS Package as you can see below : In the "Insert" part of the Package which is an Execute sql task, I'm executing a Stored procedure . this is the code inside the ...
-1
votes
2answers
63 views

SQL query with GROUPING SETS to get subtotals with a HAVING clause (non additive measure)

Let's say I have a (fictional) table Order with the following columns: OrderId: string Supplier: string Channel: string I want a query that counts the number of distinct suppliers per channel, ...