Newest tsql Questions

Q&A for professional and enthusiast programmers

The difference between SELECT * and SELECT A.* [on hold]

I am new to SQL can someone help me understand why do we use Select .* in sql server ? And why is it different from just select * from table name In some cases we use statements like select .*, ...

Convert big varbinary field to string

I have the table, that contains only two fields: [HelpFileName] [nvarchar](50) NOT NULL, [HelpFileData] [varbinary](max) NOT NULL That table stores different help files. There is one file which size ...

How to group and count number of data where each group's time range is within a constant?

I have a table which has following fields: ID (PK, int, not null) UpdatedTime(DateTime, not null) What I am trying to do is to group the records by UpdatedTime, and count the number for each group (...

SQL Server: how to select values NOT IN the table?

My table look like this : ID | NAME 1 | name1 2 | name2 NULL | name3 3 | name4 For a list of customers I want those who are not in the list, something like this: SELECT c.* FROM ...

Using a permutation table to fill in gaps

I have a table of permutations that I expect to exist. Then I have a table of actual data. I am trying to join the permutation table in such a way that all permutations exist and if actual data exists ...

How to generate a dataset dynamically with a parameter in “from” clause of “select”

select * from (select 0 as hour union select 2 union select 4 union select 6 union select 8 union select 10 union select 12 union select 14 union select 16 union select 18 union select 20 union ...

Error while creating a table from the values of two tables

I am new to sql. I was just trying to create and store data into a table from the values of two tables 1 st table has UserName. 2nd table has password. So, I have written a sql query with gets both ...

Using WHERE with stored procedures

I'm getting a syntax error for the WHERE clause in the following code. Whats the problem? CREATE PROC spIncreaseHours (@ID int, @HoursPerWeek DEC, @HourlyRate INT) AS INSERT INTO Payments Values (@...

SQL - Get all dates between Current day and start and end of week

I am trying to set the start day to Saturday SET DATEFIRST 6 -- Sets start day to Saturday And then get all the GameDate between the start of the week and the end of the week. But it needs to be ...

In TSQL, why does an UPDATE using += on a column not add subsequent values?

When creating an UPDATE statement, I thought using += would add columns on either side and save it to the column on the left. This seems to work with a SELECT statement and a variable but not with ...

Print the position of commas in a given string in T-SQL

I want to print the position of commas in a given comma separated string, but i'm just getting only zeros. Here is the code I wrote: declare @begin int=0 declare @temp int=1 declare @count int=0 ...

Dropping view error

Why this returns error: DROP VIEW vTest; GO Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'GO'. And this works fine: DROP VIEW vTest; GO And how to adjust first statement so ...

T-SQL check to see if date in one table is between two dates in another table then set value

I have two tables shown below. I want to create a new variable (VALUE) based on the logic below and show results in a 3rd table? How can I do this in T SQL? TABLE_1 ID, DATE TABLE_2 ID, DATE1, ...

UPDATE column using COUNT

I want the parameter 'HoursPerWeek' to Equal the number of pupils with a specific Teachers ID. I'm really lost on how to go about this. CREATE PROC spUpdateTeachingHoursAndWages AS update Payments....

Transact Sql Column not null

Right now, I have a view that looks like this: CREATE VIEW [dbo].[vwContaminationHistory] AS SELECT ISNULL(ROW_NUMBER() OVER (ORDER BY DetectorID), 9999) AS Row, MAX(DetectorID) ...

select statement in a variable I need to out put to a temp table

I am sure this is easy but I have a select statement stored as a variable e.g. @statement contains a statement "select count(*) from table1" I need to execute this variable e.g. sp_executesql @...

Need an expert in T-Sql SQL to solve the averages per week day issue

I have decided to write a post because I run out of idea how can modify the query I wrote to pull the average number of shipments and pieces per day. The query basically picks all the shipment and ...

TSQL - left join different rows of a table uniquely

I am recording the connection and disconnection time of a device in a table. The final purpose is to define the maximum disconnection time. My table looks like this Before proceeding with the ...

Increment value based on another column

I want to increment a column and then stop and start again based on a value in another column. For e.g: I have a table: CustomerID Reportdate True_False 9001 2013-01-01 0 9001 2013-...

t-SQL: select statement inside of geography POINT string

This is a crap-shoot, but I figured I would pick the brains of the more experienced anyway. As you probably know, you can use the below query to find the distance between two sets of coordinates. ...

updated statement throw String or binary data would be truncated error

update give me the following error Msg 8152, Level 16, State 13, Procedure BOM WHEEL, Line 8 String or binary data would be truncated. The statement has been terminated. my statement below. [...

T-SQL query trying to sp_send_dbmail

I have the below script (all using System Databases). Below is my script. It's basically a script to identify SQL Server Agent jobs which are running longer than 240 minutes. --Step 1: Decide if ...

TSQL ORDER BY with nulls first or last (at bottom or top)

I have a date column which has some NULL. I want to order by the date column ASC, but I need the NULL s to be at the bottom. How to do it on TSQL?

T-SQL select and update with lock - transaction or table hint

How to achieve following transaction lock? In big simplification - I have a table of "Tasks" with statuses (Created, Started, Completed). I want to create stored procedure GetNext to get top 1 task ...

Improving the query speed on orphan record search

I have three large tables (plus 1 million rows) Big1 has a Bigger1ID Big2 has a Bigger1ID Big3 has a Bigger1ID That hold an ID to another table (plus 10 million rows) Bigger1 has a Biggest1ID ...

Storing java and c# projects in MS DB and compiling them with JOB [on hold]

Im thinking about .Net platform that will provide uploading java and C# (For now) projects to database. I wonder if there is any way to compile those using sript or smth in T-sql JOB, so it will run ...

Calculate same day start/end dates as 0 days if another occurrence already exists

I have a query where there are instances where a "phase" starts and ends on the same day - this is calculated as 1 day. If, however, another "phase" starts and ends on the same day against the same ...

T-SQL Stored Procedure Join Table Valued Parameter Twice

I have a table valued parameter that I want to join, not just once, but twice. Once to get a row, and the second time to get the parent row. I am almost certain this can be done with normal tables. ...

SQL Server Bulk Insert - columns delimited by quotes and containing quotes

I'm trying to import a text file into SQL Server using BULK INSERT. Here's a sample line containing the exception I'm having issues with:: "AB123","Sample Data ""B"" Test",7/12/2007,"ABC","More ...

JOIN Files using first three alpha numeric?

I am trying to create a JOIN Account Numbers. One file has full act# whereas other has first 2 or 3 Alpha Numeric. (These represents Countries) Example: file 1 - 136xxxxxx 802xxxxxx X1xxxxxxx ...

Get default value from another table based on column name

In the following, the first row are headings and all the values are | delimited. I have a table called MemberInfo, as follows: ColumnName|Value AccountCode|FredsDiscounts Id|1 Name|Joe Bloggs ...

Get all transactions prior to 3 minutes ago with SQL

I have a transaction table, with a primary key of transcationId, a string transactionType, and a datetime stamp in the style of 2017-03-09 20:49:13.870 I want to select all transactions of ...

TSQL Error converting data type varchar to float. Warning: Null value is eliminated by an aggregate or other SET operation

I am getting the above error, and it hits on my Insert on line 95: "INSERT INTO @total" I added the columns topic_name varchar(100), samplegroup_name varchar (50), subtopic varchar(50) from a working ...

Count returns 0 if no rows found

This is the code on sqlfiddle - http://sqlfiddle.com/#!6/9596d/8 create table tbla (uid nvarchar(10), name nvarchar(50)); create table tblb (uid nvarchar(10), cd nvarchar(5)); insert into tbla ...

Create a “final” filter in an UNION SQL

I have several queries united for UNION clauses I want to filter the total result from one field. One idea is to filter every query, but it could be "hard" work for nothing. SELECT 'GROUP1' AS GROUP, ...

Sitecore TSQL Script to Query Content Items With a Layout

I need to generate a report of all Sitecore content items based on when they were created and include only the ones that have a layout. I have tried using Sitecore Powershell, but the my Powershell ...

Efficiency UPDATE WHERE … vs CASE … WHEN

In the case of a big table (~5 millions lines) what is more efficient to update all lines that matches a condition (circa 1000 rows): 1. A simple update statement ? UPDATE table SET ...

T-SQL trigger if only 2 columns are updated

I'm trying make a trigger that will record the previous data of 2 columns (address and postcode) when a table is updated, including the user and time stamp of the update. I also need the update to ...

Specific column — Error converting data type nvarchar to bigint

This is part of the T-SQL.I am getting below error. Can anyone guide me.The issue is because of the value column which is of nvarchar datatype SELECT RuleID, SourceID, DataFileID, ChildCount, ...

SQL Server 2012 : get difference between two times

The background: I am trying to identify the length of time a Customer spends in a particular room, each Customer is identifiable by a CustomerID when they visit they are assigned a VisitNumber, for ...

T-SQL IF CLAUSE SYNTAX

I'm trying to build up a sp for scheduling a mailing list depending on various parameters, and it is working fine up to this point: DECLARE @out_desc VARCHAR(1000),@out_mesg VARCHAR(10),@tableHTML ...

Trying to write a query that will display duplicates results as null

I have a table that looks like the first example. I'm trying to write a MSSQL2012 statement that that will display results like the second example. Basically I want null values instead of duplicate ...

Can removing non-alphanumeric characters prevent SQL Injection

Imagine a simple query: Declare @sql varchar(100) Declare @table varchar(20) Set @table = 'foo' select @sql = 'select * from ' + @table exec(@sql) This would work okay until someone changes @table ...

Group up rows based on date overlapping

In a same id, if any of row's effective date and enddate overlaps then we need group it up in a unique id In below image dategroup is the desired output column Data is sorted in order by ID asc, ...

create a stored procedure for column (contains comma separated string) in a table

Here is the simple table which I want pass table name and column name as parameters to stored procedure .. Here is the table content CREATE TABLE [dbo].[test]( [ID] [int] IDENTITY(1,1) NOT NULL, ...

Using DELETE and joins in stored procedures

I'm trying to delete any record where the FeeDueDate is greater than 3 weeks but Im not sure how to go about implementing this. The problem seems to be with the join syntax. CREATE PROC ...

How to determine a stored procedure name in Azure Datawarehouse?

I'm migrating a SQL Server database to Azure parallel data warehouse, and I'm having an issue with adapting the stored procedures to Azures limitations. Part of the procedures is logging to a log ...

T-SQL query to fetch row with multiple aggregate functions

I have the following problem. I need to fetch the row for each distinct user_id with the min rank, max end date, max begin date, and max sequence number in that order. Essentially, I want a user's ...

SQL performance issues, need help optimizing this query if possible

I am not a SQL expert and need some input. I have the query below which takes over 2 hours to run and consequently times out. Company policy is we cannot alter the timeout period of a SQL query to be ...

sql drop primary key from temp table

I want to create e temp table using select into syntax. Like: select top 0 * into #AffectedRecord from MyTable Mytable has a primary key. When I insert record using merge into syntax primary key be ...
Translating... 0%