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, ...
