Questions tagged [t-sql]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
19 views

declare table variable inside function

In my stored procedure, I have table variable with a lot of columns. Because I want to simplify the stored procedure I thought i could declare table variable in function like CREATE FUNCTION [dbo].[...
-1
votes
0answers
24 views

Insert into select 2 million row takes 1 hour sql server [on hold]

I am trying to insert into a table with select which returns around 2 million rows, this insert takes an hour, the query has just one where clause which is a date. nothing else.The table has 84 ...
5
votes
2answers
420 views

Infinite loop in CURSOR

I'm trying to use a cursor to clean up temp tables when they are no longer needed. I have a small table which has the names of the temp tables along with an identifier. The cursor is stuck in an ...
4
votes
1answer
89 views

Get the full text of a long request

How to get the full text of the request from the following request: SELECT t.[text] FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t Here, with a sufficiently long ...
1
vote
3answers
102 views

Slow query in production, bad execution plan or bad index?

I've just fixed a production performance issue by dropping an index and recreate it. I suspect dropping the index also dropped executions plans that used it and one of them happen to be bad. ...
-1
votes
0answers
18 views

Seaching for T-SQL books [on hold]

Ever heard of a T-SQL book author named Mirrock (phonetically) or Merrock or Mirock?
-2
votes
2answers
29 views

Insert multiple columns into one column sql [on hold]

Table A Name (String) Surname (Sring) Address (String) Employed (String) Non-Employed (String) Retired (String) Table B Name (String) Surname (Sring) Address (String) Employed ...
-1
votes
1answer
37 views

How to loop data month wise for the targeted dates [closed]

I need to load data from a primary table to an archive table by month or week. As an example, I have a table called transactions with a column named return_datetime where I have 5 years or data. I ...
0
votes
1answer
62 views

Two conversions of practically the same number giving two different results

To my surprise, I run the following queries in SQL Server 2014 and each returns a different result, as shown in this db<>fiddle... This Select convert(numeric(19,2),convert(float,9....
1
vote
3answers
90 views

remove leading and trailing quotes

I have a field that may start and end with quotes. I only want to remove the quotes if the string starts and ends with quotes. If quotes exists inside the string then I wish to retain them. I have ...
1
vote
2answers
37 views

Equivalent of Oracle RR date format in SQL Server

Having a bunch of SQL insert scripts generated in Oracle, I have the requirement to adjust them to be usable with T-SQL in Microsoft SQL Server. The Oracle export has date formats like this: to_date(...
-1
votes
0answers
18 views

Automate Weekly Restores, backups have different names each time

I'm trying to automate restores of weekly backups we are getting from a vendor. The issue is the database name changes every time and is postfixed with the date so how do I supply the full path in the ...
1
vote
2answers
37 views

SQL Server Update Statement joining on index column

Using SQL Server 2012, I have a stored procedure that updates data by doing a delete and insert similar to this: DELETE FROM MyTable WHERE EXISTS ( SELECT 1 FROM ...
0
votes
1answer
32 views

Casting fraction number to decimal throws Arithmetic overflow error

I have a table called AssignmentMarks that stores Student's assignment marks for different subjects. This table has a column called Marks varchar(10) which stores the marks. The marks can be like 1, ...
0
votes
1answer
29 views

Assign value based on table and ID

I wish to assign a value in my row which should be based on the country as: Only US: Should be assigned USA only Other countries: Should be assigned International US + Other countries: Should ...
0
votes
1answer
34 views

Is it possible to restore a database from corrupted .mdf file?

I know that the answer of the question may vary depending on what exactly is broken in each .mdf file - only looking for advice if there is another way to approach this. So, I have received some bad ...
0
votes
1answer
24 views

How do I return an index for each nvarchar col in a table?

Imagine a simple table as follows Create table test ( c1 nvarchar(10), c2 nvarchar(10), i1 int, c3 nvarchar(10) ); Is there a way I can query and get The index for the nvarchar columns? The index ...
0
votes
1answer
42 views

T-SQL query to merge all rows into one row [duplicate]

I have a T-SQL query, which pulls details from several locations (Something like below): IF OBJECT_ID('tempdb..#mytable') IS NOT NULL DROP TABLE #mytable; CREATE table #mytable ([Locno] varchar(...
1
vote
1answer
33 views

T-SQL Query to calculate Settele and withdraw amount in MSSQL- SERVER

I have a table with below structure : FactSatnaEvent(OPRDATE date,OPRCOD int, voucherno int, SATNAPAYATYPE int, CFCIFNO int , AMOUNT int) based on this table I wrote this query SELECT ...
0
votes
0answers
30 views

SSIS not able to run package in SSMS

It's very simple package, running fine when executed via visual studio. From the report I get: Visual Studio 2019 Community SQL2019CTP3.1 In the connection manager I am using windows authentication. ...
0
votes
1answer
33 views

Query to get the physical file name of the database

Im trying to get the physical file name of a database, and when i query sys.database_files, under the physical file name column, you get the whole path (ie. S:\Program Files\Microsoft SQL Server\...
0
votes
1answer
30 views

Sql Server - Get database constraints of all tables ( default and check constraints)

I need to get constraints of all columns, tables in my database result like this : DatabaseName | SchemaName | TableName | ColumnName | ColumnType | ConstraintName | - ConstraintType | Definition | ...
0
votes
1answer
25 views

TSQL - use columns as date range

How can I retrieve the cto column using an @variable? For example, for @Variable = '2019-03-02' the expected output is'MAR 1 - MAR 7'.
1
vote
2answers
56 views

Setting a date dynamically

I would like to set a date dynamically in the where clause of my query but I can't figure out how to do it. The query is doing a count of the number of rows with an entered on date >= 1st August ...
1
vote
3answers
87 views

T-SQLRevoke permissions to all tables in the sql server in one go

How can we revoke a certain permission to all tables? I've tried (T-SQL): REVOKE SELECT on schema::DBO TO [public] AS [dbo] But nothing happens.. All tables still have Select permission. I can ...
1
vote
3answers
52 views

Int for identity/primary key and Guid for public identifier, best practices?

I almost always use an int for identity/primary (auto incrementing) clustered key for my tables. They're easy to query and debug, bounce around in SSMS, and plenty of other reasons. I have a case ...
9
votes
3answers
1k views

How is a T-SQL Trigger that only fires on real changes made?

I have a table trigger on UPDATE and INSERT that adds a row to another table. It only needs to add a row if one of four columns are changed. I tried using IF UPDATE(col) to test for changes but it ...
0
votes
1answer
65 views

Rusty SQL = Heavily scratched head [closed]

I am trying to figure out the best way to sort this solution. I am trying to produce a SP (or series) to generate invoices so that the contractors know how much they will be paid. I have created two ...
0
votes
2answers
23 views

Migrating SQL Server tables data from one application to another totally different application tables

We are migrating a few tables' data from one application to another application. These two applications are totally different tables and schema. We have created general T-sql scripts and temporary ...
1
vote
2answers
126 views

Obtain a “list” of stored procedures which include INSERTs without naming the columns involved

As part of some enhancement, a new column has been added to over 750 tables. Now my problem is within almost 3000+ of stored procedures, some old INSERTs do not name columns. Is there any way I can ...
0
votes
3answers
60 views

How to use REPLACE in a dynamic way

How can I make the REPLACE function dynamic, by dynamic I mean that if for example one entry has 3 quotes and an other on has 5, I want it to cater for all situations BEGIN TRAN update ...
0
votes
3answers
61 views

Must declare the scalar variable question

That is my first post here, need helps: in a stored procedure I had some following code CREATE PROCEDURE [dbo].[SP_getAvg] ( @projectId INT ,@carrierId INT ,@fetchType VARCHAR(20) ) ...
2
votes
1answer
25 views

Facing permission error while trying to execute a stored procedure published through CLR

I've created a stored procedure using SQL CLR c# stored procedure in Visual Studio 2017 and I've published it in MSSQL Server. Now I can see my stored procedure, but I face this error when I want to ...
0
votes
2answers
22 views

Get matching part from conditional pattern

I'm having a table like this: |id|key |value | ------------------------ | 0|name |Homer | | 1|surname|Simpson | | 2|town |Springfield| I'm running this query: SELECT * FROM ...
1
vote
1answer
69 views

Run a stored procedure only the last 3 days in a month

How can I run a stored procedure only the last 3 days of a month using a T-SQL script?
0
votes
2answers
38 views

How to select top 3 vendors without duplicate entries?

Here is my tables I want to select Top 3 lowest quoted rate vendors without duplicate Entry. Expected output will be V1, V4, V3 without using while loop. is this possible? Table details CREATE ...
0
votes
1answer
35 views

T-SQL is not working on DBeaver on Linux [closed]

I am trying to run a basic T-SQL script DECLARE @temptable TABLE( abilityTypeId BIGINT NOT NULL, companyId BIGINT NOT NULL ); INSERT INTO @temptable(abilityTypeId, companyId) ...
0
votes
1answer
43 views

TRY .. CATCH sometimes fails to catch PRIMARY KEY VIOLATION

I wrote code to upsert records to the database. The database is Azure DB, S4 tier. Updates and inserts are sometimes are highly concurrent since it's coming from the application that doing too many ...
0
votes
1answer
143 views

Show table records based on user condition in SQL Server

I have a web application backed by SQL Server database. It has a report, that combines students of selected semesters and a specific way. the following image show the interface for selecting semester ...
0
votes
3answers
80 views

Order by of records of a table in SQL Server

I have the following tables: Customer (ID, Name, CountryID) Country (ID, Name, Code) From the web application, user selects the number of Customers and the Countries then based on these parameters ...
0
votes
2answers
128 views

split one column into two columns based on it's value using t/sql

Hope you're doing well. I have a table as you can see below : ACNTHEADER(DDATE DATE ,ACNTCODE INT,VALUE NUMERIC(17,2)) the data in the table : DDATE ACNTCODE VALUE 6/1/2013 1174 ...
-1
votes
1answer
27 views

Select one value from each row where the SUM of all selected values equals known number X

I have a known number, x and several named columns (ix, col1, col2, col3...) and need to select a single value from either col1, col2, or col3 from each row (not necessarily the same column) where the ...
2
votes
1answer
40 views

How do I separate the values of one column into multiple columns In MS SQL-Server

ID Contact No Ext Type -- ---------- ---- ---- 0001 75865558 123 work 0001 207586558 NULL home 0001 207586559 NULL cell 0001 746655558 321 work 0002 ...
1
vote
1answer
67 views

How to replace multiple unicode characters

I have identified a couple of unicode characters in my database by means of the following query: SELECT name FROM table WHERE name NOT LIKE '%'+CAST(name AS VARCHAR(1000))+'%' ...with which I get ...
0
votes
0answers
30 views

How configure security to query second same server database

I have a database which holds all my permissions and a central database which holds all the logins for all my application. I need to create view that queries both database which reside on the same ...
1
vote
1answer
51 views

Sargability question - order of variables

I have a stored procedure that has a few lines: AND ( @StartDate IS NULL OR @StartDate <= r.ReferralDate ) AND ( @EndDate IS NULL OR @EndDate >= r.ReferralDate ) Should it be re-written as: ...
0
votes
1answer
34 views

Get INSERT/UPDATE/DELETE statements executed at specified time

We are investigating some issues with our production database where some suspicious records inserted/updated in our database. I just wanna know how we will be able to get these TSQL statements ...
0
votes
2answers
46 views

How to avoid conditional join in order to write a tsql view

I have a scenario where I have Roles, Set, DefaultSet, and CompanySet tables Roles table RoleName --------- Admin Viewer User Set table Id Name --- ---- 1 Employee Data 2 Help ...
0
votes
1answer
61 views

How SQL Server transaction works with log records

My textbook says: Starting when you begin a transaction, your changes are isolated from other users. What you’re doing is visible to you only,and isn’t really done until you COMMIT — although it ...
3
votes
2answers
537 views

Dynamic SQL query - how do I add an int to the code?

Consider: Declare @stringsvar varchar(1000) Declare @Emp_id int DECLARE @strvar SYSNAME = 'Employee_test' SET @stringsvar = ('select * from' + ' ' + @strvar + ' where emp_id' + ' =' + @Emp_id) ...