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
38 views
How to export SQL Server database as single .sql query file
How to export a SQL Server database into single .sql file, so that I can execute the query any system and SSMS, without carrying the .mdf file
1
vote
1answer
27 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
46 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 ...
2
votes
1answer
72 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
41 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
47 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
2answers
81 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
52 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
46 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
75 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
30 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
37 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
62 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
28 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
19 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
34 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
241 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
59 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
26 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
62 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, ...
0
votes
1answer
30 views
Linking tables to extract value based on closest previous date
I'm going to use an example that was previously posted but modify it to match my problem:
TOCs bu Date:
Location StatDate TOC
WC1 9/1/1900 5600
WC1 10/1/2010 5605
WC2 8/2/1980 4300
WC2 9/...
1
vote
2answers
45 views
MAX DATE With NULL VALUE
Trying to get the max date from my table, i can't retrieve the value when the date is null.
Sample :
the following table :
+----+--------+------+------------------------+
| id | parent | Date ...
2
votes
2answers
132 views
How to join global temp tables in SQL Server?
I have four global temp tables (##A,##B,##C,##D) on which I want to perform a left join in SQL Server 2008. By global temp tables I mean I created them using a code like this:
SELECT *
INTO ##A
...
2
votes
2answers
272 views
Pass data from a table row into a create login statement using dynamicSQL [closed]
I have a lab exercise that involves creating a script that uses dynamic SQL and a cursor to loop through each row of a table and then create a loginID and more.
We've done NO work concerning cursors/...
0
votes
2answers
54 views
How to get last 12 months values when some months have no records in the table
I have a table which has PartNumberKey and Depot_ID as composite primary key. It returns the following data -
The requirement is to add the month columns for which there is no row with 0 as Total ...
2
votes
2answers
45 views
Is UPDLOCK required?
Given table A and table B, such that rows in B reference a row in A...
CREATE TABLE A (Id INT, column1 INT)
CREATE TABLE B (Id INT, AId INT)
Rows in B must be consistent with one another. So, when ...
3
votes
2answers
80 views
Trying to check if a string contains a number exclusively
I have been trying to write a function to check if a string contains a number without that number being part of a bigger number (in other words if the number being searched for is '6' and the string ...
3
votes
0answers
42 views
Does SQL Server allow user to create in-memory database for testing? [closed]
Does SQL Server T-SQL allow in-memory database testing? So if we have multiple qa testers running the same test, every one gets an in-memory database, and results will not collide?
I know C# Entity ...
2
votes
2answers
39 views
Return values as 1 or 0 together with percent
I need a SELECT that will result in a percentage and columns with 1 and 0 for exist or not exist.
The goal is to generate data for a usage meter (Percentage 0.0-1.0) and each measurable value as 1 or ...
1
vote
1answer
34 views
How to index of filter extended event files in Sql Server
I am collecting errors data using the error_reported event and saving the data on XEL files. The problem is that there are hundreds of thousands of errors over there and querying them can take a lot ...
12
votes
3answers
2k views
Why use master to create a database?
I have a short question, why do I use use master; to create a database?
Here is the example from the Microsoft documentation
USE master ;
GO
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
FILENAME =...
-2
votes
2answers
50 views
Problem in formatting result set in desired format
I have devices which are installed in a different location with different area ID I am returning the device activity status of different areas on an hourly basis in the following format.
AreaId | ...
1
vote
1answer
32 views
SQL query with GROUPING SETS to get subtotals with a HAVING clause
Let's say I have a (fictional) table City with the following columns:
Country: string
State: string
City: string
Population: integer
I want a query that shows the total population by State, by ...
0
votes
1answer
40 views
Problem with database migrated from SQL Server 2005 to SQL Server 2014
First of all I want to apologize for my English, I hope you understand me correctly.
I have a database migrated from a SQL Server 2005 in a SQL Server 2014.
Well, in addition, I have a .NET process ...
0
votes
2answers
77 views
Joining 3 Tables with Left Join
I have 2 Tables namely Area_ Table and PingData_Table
Area Table has the list of the areas along with the Area ID and Area Name
AreaId | AreaName | isActive
1 |Jumbo House| 1
2 | DCC ...
2
votes
1answer
40 views
Converting a string to an object reference
Sometimes I end up in a position where I have a string version of my object reference like so:
'server.database.schema.table'
And I need to use it as an actual object reference:
'server.database....
0
votes
2answers
46 views
Insert into a table selecting data from a sub query [closed]
I would like to insert a few data into a table selecting data from a subquery having multiple joins.
I tried the following way:
insert into mytable (columns)
select * from
(select columns
from ...
2
votes
0answers
50 views
Best Way to Index Multiple Effective Date Columns
I've got a table that is primarily to represent relationships between entities (i.e. mostly composed of foreign keys). These relationships change over time, and so the table has a StartDate and an ...
1
vote
1answer
56 views
Last character in column shift to next column
I have a unique situation I have to fix. Our vendor gave us a data map and things are not mapping out as they said it should. My situation is, starting with a particular column, I need to drop the ...
0
votes
1answer
44 views
Trigger to decrypt the Encrypted data in a column while it's being inserted [closed]
I have a table
TestTableDestination(SerialNumber (varchar) , Name(varchar))
When I want to insert in this table , I'm inserting the encrypted data. What I want to do is to write a trigger with ...
2
votes
1answer
26 views
Created Check Constraint WITH NOCHECK - still trusted
I needed to alter the conditions of existing constraint - so I dropped it and recreated with the new conditions.
This failed, because there were 800k+ rows, that didn't meet the condition. That ...
1
vote
1answer
34 views
Log bad call to stored procedure
I have an external application making a stored procedure call into a SQL Server 2017 database, via a normal database connection (PHP library (PDO?) of some form using the server, database, user, ...
0
votes
2answers
68 views
SQL Work Hours Only
I am working on a report, one of the requirements is that I find the turnaround time between two dates (columns) - I have done that using a DATEPART function, however, the hours are exaggerated, I ...
0
votes
1answer
37 views
Query performance diffrence between equals and between
I need to know why the correct answer is D ? what is the diffrence between D and A.
Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores ...
1
vote
1answer
31 views
Database Reader Role with the ability to Create, View and execute stored procedure's and view's in sql server
I will need to allow report writers on the Subscription database to be able to view the table, create alter and delete stored procedure and views. Since it is a transactional replication they cannot ...
1
vote
2answers
70 views
Syntax error in SQL Server while making Dynamic SQL
Scenario:
I have a table with 100M+ rows of data so I have created an index with something as:
CREATE NONCLUSTERED INDEX FIBaseVoterWithDeletedAt
ON dbo.base_voter (name_voter,...
0
votes
2answers
127 views
string_split into multiple columns
Sorry if this question is very basic, but I just can't figure it out, and couldn't find any good answers.
I have a very long list of files, folders and Sizes. I need to Seperate the folders to ...
0
votes
1answer
46 views
DBCC CLONEDATABASE missing schemas
I have used DBCC CLONEDATABASE to create a copy of a database without any data. I have now found that some schemas were not created, thus some objects have no schema. Its hard to reproduce this as it ...
