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