Questions tagged [t-sql]
T-SQL (Transact Structured Query Language) is the extension of SQL functionality supported by Sybase ASE and Microsoft SQL Server. Do not use this tag for MySQL, PostgreSql, Oracle(Pl/SQL) related queries. Please note that SQL code which is being written using LINQ will not also be the part of this tag. This tag specifically created for advanced SQL programming using Microsoft SQL Server.
73,201
questions
4265
votes
40
answers
5.3m
views
How do I UPDATE from a SELECT in SQL Server?
In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement:
INSERT INTO Table (col1, col2, col3)
SELECT col1, col2, col3
FROM other_table
WHERE sql = 'cool'
Is it ...
- 44.9k
2181
votes
47
answers
3.7m
views
How to return only the Date from a SQL Server DateTime datatype
SELECT GETDATE()
Returns: 2008-09-22 15:24:13.790
I want that date part without the time part: 2008-09-22 00:00:00.000
How can I get that?
- 34.4k
2172
votes
33
answers
1.7m
views
How to check if a column exists in a SQL Server table
I need to add a specific column if it does not exist. I have something like the following, but it always returns false:
IF EXISTS(SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE ...
- 22.1k
2067
votes
12
answers
2.1m
views
LEFT JOIN vs. LEFT OUTER JOIN in SQL Server
What is the difference between LEFT JOIN and LEFT OUTER JOIN?
- 21.7k
2016
votes
4
answers
3.4m
views
Inserting multiple rows in a single SQL query? [duplicate]
I have multiple set of data to insert at once, say 4 rows. My table has three columns: Person, Id and Office.
INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office");
INSERT INTO MyTable VALUES ("...
rits
1841
votes
35
answers
2.6m
views
Insert results of a stored procedure into a temporary table
How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]?
Select all data from BusinessLine into tmpBusLine works fine.
select *
into ...
- 21.6k
1792
votes
30
answers
4.6m
views
How do I perform an IF...THEN in an SQL SELECT?
How do I perform an IF...THEN in an SQL SELECT statement?
For example:
SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product
- 29.8k
1754
votes
36
answers
4.0m
views
Find all tables containing column with specified name
Is it possible to query for table names which contain columns being
LIKE '%myName%'
- 29.2k
1694
votes
28
answers
2.2m
views
Get size of all tables in database
I have inherited a fairly large SQL Server database. It seems to take up more space than I would expect, given the data it contains.
Is there an easy way to determine how much space on disk each ...
- 18.8k
1627
votes
19
answers
2.2m
views
How can I do an UPDATE statement with JOIN in SQL Server?
I need to update this table in SQL Server with data from its 'parent' table, see below:
Table: sale
id (int)
udid (int)
assid (int)
Table: ud
id (int)
assid (int)
sale.assid contains the ...
- 20.6k
1433
votes
30
answers
1.9m
views
Check if table exists in SQL Server
I would like this to be the ultimate discussion on how to check if a table exists in SQL Server 2000/2005 using SQL Statements.
Here are two possible ways of doing it. Which one is the standard/best ...
- 22.6k
1378
votes
15
answers
1.8m
views
How do I escape a single quote in SQL Server?
I am trying to insert some text data into a table in SQL Server 9.
The text includes a single quote '.
How do I escape that?
I tried using two single quotes, but it threw me some errors.
eg. insert ...
- 15.4k
1375
votes
43
answers
1.4m
views
How can I remove duplicate rows?
I need to remove duplicate rows from a fairly large SQL Server table (i.e. 300,000+ rows).
The rows, of course, will not be perfect duplicates because of the existence of the RowID identity field.
...
- 69.5k
1373
votes
14
answers
1.6m
views
Altering a column: null to not null
I have a table that has several nullable integer columns. This is undesirable for several reasons, so I am looking to update all nulls to 0 and then set these columns to NOT NULL. Aside from changing ...
- 17.7k
1373
votes
15
answers
1.2m
views
How to get the identity of an inserted row?
How can I get the IDENTITY of an inserted row?
I know about @@IDENTITY and IDENT_CURRENT and SCOPE_IDENTITY, but don't understand the implications or impacts attached to each. How do these differ, and ...
- 495k
1155
votes
47
answers
1.6m
views
Exclude a column using SELECT * [except columnA] FROM tableA?
We all know that to select all columns from a table, we can use
SELECT * FROM tableA
Is there a way to exclude column(s) from a table without specifying all the columns?
SELECT * [except columnA] ...
uuɐɯǝʃǝs
1136
votes
15
answers
989k
views
When should I use CROSS APPLY over INNER JOIN?
What is the main purpose of using CROSS APPLY?
I have read (vaguely, through posts on the Internet) that cross apply can be more efficient when selecting over large data sets if you are partitioning. ...
- 38.4k
1089
votes
20
answers
2.2m
views
How do I get list of all tables in a database using TSQL?
What is the best way to get the names of all of the tables in a specific database on SQL Server?
- 190k
1042
votes
24
answers
3.1m
views
How can I get column names from a table in SQL Server?
I want to query the name of all columns of a table. I found how to do this in:
Oracle
MySQL
PostgreSQL
But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?
- 26k
996
votes
19
answers
788k
views
Function vs. Stored Procedure in SQL Server
When should I use a function rather than a stored procedure in SQL, and vice versa? What is the purpose of each?
- 80.8k
986
votes
32
answers
976k
views
How can I list all foreign keys referencing a given table in SQL Server?
I need to remove a highly referenced table in a SQL Server database. How can I get a list of all the foreign key constraints I will need to remove in order to drop the table?
(SQL answers preferable ...
- 25.3k
982
votes
13
answers
1.1m
views
Update a table using JOIN in SQL Server?
I want to update a column in a table making a join on other table e.g.:
UPDATE table1 a
INNER JOIN table2 b ON a.commonfield = b.[common field]
SET a.CalculatedColumn= b.[Calculated Column]
WHERE
...
- 11.4k
945
votes
10
answers
850k
views
How to Join to first row
I'll use a concrete, but hypothetical, example.
Each Order normally has only one line item:
Orders:
OrderGUID OrderNumber
========= ============
{FFB2...} STL-7442-1
{3EC6...} MPT-...
- 252k
925
votes
17
answers
762k
views
How can foreign key constraints be temporarily disabled using T-SQL?
Are disabling and enabling foreign key constraints supported in SQL Server? Or is my only option to drop and then re-create the constraints?
- 190k
896
votes
14
answers
1.2m
views
Should I use != or <> for not equal in T-SQL?
I have seen SQL that uses both != and <> for not equal. What is the preferred syntax and why?
I like !=, because <> reminds me of Visual Basic.
- 20.5k
891
votes
17
answers
663k
views
DateTime2 vs DateTime in SQL Server
Which one:
datetime
datetime2
is the recommended way to store date and time in SQL Server 2008+?
I'm aware of differences in precision (and storage space probably), but ignoring those for now, is ...
- 10.6k
878
votes
15
answers
1.7m
views
How to drop a table if it exists?
The table name is Scores.
Is it correct to do the following?
IF EXISTS(SELECT *
FROM dbo.Scores)
DROP TABLE dbo.Scores
- 9,505
813
votes
13
answers
1.1m
views
SQL update query using joins
I have to update a field with a value which is returned by a join of 3 tables.
Example:
select
im.itemid
,im.sku as iSku
,gm.SKU as GSKU
,mm.ManufacturerId as ManuId
,mm....
- 217k
785
votes
15
answers
363k
views
How do I create a unique constraint that also allows nulls?
I want to have a unique constraint on a column which I am going to populate with GUIDs. However, my data contains null values for this columns. How do I create the constraint that allows multiple null ...
- 12k
778
votes
19
answers
998k
views
Get top 1 row of each group
I have a table which I want to get the latest entry for each group. Here's the table:
DocumentStatusLogs Table
ID
DocumentID
Status
DateCreated
2
1
S1
7/29/2011
3
1
S2
7/30/2011
6
1
S1
8/02/2011
...
- 29.7k
752
votes
15
answers
1.5m
views
How to select all records from one table that do not exist in another table?
table1 (id, name)
table2 (id, name)
Query:
SELECT name
FROM table2
-- that are not in table1 already
- 17.4k
614
votes
18
answers
875k
views
Insert multiple rows WITHOUT repeating the "INSERT INTO ..." part of the statement?
I know I've done this before years ago, but I can't remember the syntax, and I can't find it anywhere due to pulling up tons of help docs and articles about "bulk imports".
Here's what I want to do, ...
- 31.7k
609
votes
23
answers
998k
views
Best approach to remove time part of datetime in SQL Server
Which method provides the best performance when removing the time portion from a datetime field in SQL Server?
a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
or
b) select cast(convert(char(11)...
- 6,753
607
votes
11
answers
3.1m
views
OR is not supported with CASE Statement in SQL Server
The OR operator in the WHEN clause of a CASE statement is not supported. How can I do this?
CASE ebv.db_no
WHEN 22978 OR 23218 OR 23219 THEN 'WECS 9500'
ELSE 'WECS 9520'
END as wecs_system
- 6,185
598
votes
7
answers
1.6m
views
Selecting COUNT(*) with DISTINCT
In SQL Server 2005 I have a table cm_production that lists all the code that's been put into production. The table has a ticket_number, program_type, program_name and push_number along with some other ...
- 6,504
590
votes
32
answers
1.0m
views
Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
Using MSSQL2005, can I truncate a table with a foreign key constraint if I first truncate the child table (the table with the primary key of the FK relationship)?
I know that I can either
Use a ...
- 27.5k
589
votes
13
answers
849k
views
Is it possible to specify condition in Count()?
Is it possible to specify a condition in Count()? I would like to count only the rows that have, for example, "Manager" in the Position column.
I want to do it in the count statement, not using WHERE;...
- 15.2k
569
votes
19
answers
1.3m
views
Pad a string with leading zeros so it's 3 characters long in SQL Server 2008
I have a string that is up to 3 characters long when it's first created in SQL Server 2008 R2.
I would like to pad it with leading zeros, so if its original value was '1' then the new value would be '...
- 21.1k
551
votes
6
answers
171k
views
What is the significance of 1/1/1753 in SQL Server?
Why 1753? What do they have against 1752? My great great great great great great great grandfather would be very offended.
- 6,908
538
votes
12
answers
441k
views
T-SQL: Selecting rows to delete via joins
Scenario:
Let's say I have two tables, TableA and TableB. TableB's primary key is a single column (BId), and is a foreign key column in TableA.
In my situation, I want to remove all rows in TableA ...
- 17.4k
533
votes
46
answers
954k
views
How do I split a delimited string so I can access individual items?
Using SQL Server, how do I split a string so I can access item x?
Take a string "Hello John Smith". How can I split the string by space and access the item at index 1 which should return "John"?
- 75k
527
votes
24
answers
872k
views
SQL MAX of multiple columns?
How do you return 1 value per row of the max of several columns:
TableName
[Number, Date1, Date2, Date3, Cost]
I need to return something like this:
[Number, Most_Recent_Date, Cost]
Query?
- 10.5k
516
votes
9
answers
610k
views
how can I Update top 100 records in sql server
I want to update the top 100 records in SQL Server. I have a table T1 with fields F1 and F2. T1 has 200 records. I want to update the F1 field in the top 100 records. How can I update based on TOP ...
- 6,419
515
votes
15
answers
801k
views
Replace a newline in TSQL
I would like to replace (or remove) a newline character in a TSQL string.
Any ideas?
The obvious
REPLACE(@string, CHAR(13), '')
just won't do it...
- 48.5k
507
votes
4
answers
456k
views
What is the meaning of the prefix N in T-SQL statements and when should I use it?
I have seen prefix N in some insert T-SQL queries. Many people have used N before inserting the value in a table.
I searched, but I was not able to understand what is the purpose of including the N ...
- 9,413
499
votes
20
answers
770k
views
Select columns from result set of stored procedure
I have a stored procedure that returns 80 columns, and 300 rows. I want to write a select that gets 2 of those columns. Something like
SELECT col1, col2 FROM EXEC MyStoredProc 'param1', 'param2'
...
- 6,009
490
votes
14
answers
439k
views
What is the use of GO in SQL Server Management Studio & Transact SQL?
SQL Server Management Studio always inserts a GO command when I create a query using the right click "Script As" menu. Why? What does GO actually do?
- 529k
469
votes
14
answers
896k
views
How to set a default value for an existing column
This isn't working in SQL Server 2008:
ALTER TABLE Employee ALTER COLUMN CityBorn SET DEFAULT 'SANDNES'
The error is:
Incorrect syntax near the keyword 'SET'.
What am I doing wrong?
- 25.8k
458
votes
7
answers
628k
views
SQL Server SELECT into existing table
I am trying to select some fields from one table and insert them into an existing table from a stored procedure. Here is what I am trying:
SELECT col1, col2
INTO dbo.TableTwo
FROM dbo.TableOne
...
- 4,777
455
votes
25
answers
2.8m
views
SQL query to select dates between two dates
I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query.
select Date,TotalAllowance
from Calculation
where ...
- 8,255