Questions tagged [t-sql]
Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.
2,422
questions
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)
...
