Highest scored 'sql+t-sql' 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 ...

jamesmhaley

44.9k

asked Feb 25, 2010 at 14:36

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?

Eddie Groves

34.4k

asked Sep 22, 2008 at 3:31

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

asked Jan 17, 2009 at 5:55

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

Ferdeen

21.6k

asked Mar 17, 2009 at 10:45

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

Eric Labashosky

29.8k

asked Sep 15, 2008 at 14:34

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%'

gruber

29.2k

asked Jan 31, 2011 at 10:12

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

Ant Swift

20.6k

asked Aug 18, 2009 at 11:40

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

tim_wonil

15.4k

asked Oct 19, 2009 at 1:18

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

Oded

495k

asked Sep 3, 2008 at 21:32

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

asked Apr 8, 2009 at 9:15

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

Jeff Meatball Yang

38.4k

asked Jul 16, 2009 at 17:42

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)?

odiseh

26k

asked Jun 28, 2009 at 14:05

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?

Tarik

80.8k

asked Jul 24, 2009 at 19:40

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

chillitom

25.3k

asked Jan 27, 2009 at 12:17

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

Ian Boyd

252k

asked Jan 11, 2010 at 16:44

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.

Bob The Janitor

20.5k

asked Apr 6, 2009 at 20:56

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

Mikeon

10.6k

asked Aug 26, 2009 at 11:45

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

Shyju

217k

asked Jun 11, 2009 at 18:49

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

kazinix

29.7k

asked Jul 27, 2011 at 8:41

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

z-boss

17.4k

asked Apr 21, 2010 at 20:18

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

Stephen Perelson

6,753

asked Jul 24, 2009 at 12:49

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

Werner

6,185

asked Mar 30, 2011 at 14:45

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

somacore

6,504

asked Oct 5, 2009 at 18:19

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

agnieszka

15.2k

asked Sep 9, 2009 at 14:28

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"?

GateKiller

75k

asked Aug 5, 2008 at 18:15

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?

BenB

10.5k

asked Sep 16, 2008 at 10:27

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

Rajesh

6,419

asked Jul 29, 2009 at 6:37

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

Kartik Patel

9,413

asked Apr 5, 2012 at 8:19

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

Daniel

4,777

asked Nov 4, 2010 at 21:49

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

Neeraj

8,255

asked Feb 26, 2011 at 4:29

455 votes

29 answers

805k views

Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I ...

selfawaresoup

15.7k

asked Jun 10, 2010 at 13:44

445 votes

10 answers

1.2m views

How to set variable from a SQL query?

I'm trying to set a variable from a SQL query: declare @ModelID uniqueidentifer Select @ModelID = select modelid from models where areaid = 'South Coast' Obviously I'm not doing this right as it ...

Mr Cricket

5,813

asked Oct 20, 2010 at 4:02

434 votes

17 answers

985k views

Selecting data from two different servers in SQL Server

How can I select data in the same query from two different databases that are on two different servers in SQL Server?

Don Of Qau

asked Jul 17, 2009 at 15:34

429 votes

35 answers

1.4m views

How can I select the first day of a month in SQL?

How can one select the first day of the month of a given DateTime variable? I know it's quite easy to do using this kind of code: select CAST(CAST(YEAR(@mydate) AS VARCHAR(4)) + '/' + CAST(MONTH(@...

Brann

32k

asked Oct 5, 2009 at 15:30

429 votes

9 answers

802k views

Select statement to find duplicates on certain fields

Can you help me with SQL statements to find duplicates on multiple fields? For example, in pseudo code: select count(field1,field2,field3) from table where the combination of field1, field2, ...

JOE SKEET

8,030

asked Dec 13, 2010 at 22:30

417 votes

24 answers

867k views

What is the equivalent of 'describe table' in SQL Server?

I have a SQL Server database and I want to know what columns and types it has. I'd prefer to do this through a query rather than using a GUI like Enterprise Manager. Is there a way to do this?

dbg

asked Nov 26, 2008 at 0:22

396 votes

12 answers

603k views

How to DROP multiple columns with a single ALTER TABLE statement in SQL Server?

I would like to write a single SQL command to drop multiple columns from a single table in one ALTER TABLE statement. From MSDN's ALTER TABLE documentation... DROP { [CONSTRAINT] constraint_name | ...

Jesse Webb

44.3k

asked Jun 14, 2011 at 15:32

392 votes

6 answers

495k views

What is the difference between SQL, PL-SQL and T-SQL?

What is the difference between SQL, PL-SQL and T-SQL? Can anyone explain what the differences between these three are, and provide scenarios where each would be relevantly used?

Goober

13.4k

asked Jun 25, 2009 at 10:54

387 votes

8 answers

495k views

What represents a double in sql server?

I have a couple of properties in C# which are double and I want to store these in a table in SQL Server, but noticed there is no double type, so what is best to use, decimal or float? This will store ...

Xaisoft

46.1k

asked Jul 30, 2009 at 20:34

386 votes

10 answers

1.1m views

Altering column size in SQL Server

How to change the column size of the salary column in the employee table from numeric(18,0) to numeric(22,5)

Sreedhar Danturthi

7,299

asked Apr 13, 2012 at 9:09

383 votes

7 answers

168k views

T-SQL Cast versus Convert

What is the general guidance on when you should use CAST versus CONVERT? Is there any performance issues related to choosing one versus the other? Is one closer to ANSI-SQL?

BuddyJoe

70.5k

asked Apr 1, 2009 at 21:05

382 votes

10 answers

586k views

Not equal <> != operator on NULL

Could someone please explain the following behavior in SQL? SELECT * FROM MyTable WHERE MyColumn != NULL (0 Results) SELECT * FROM MyTable WHERE MyColumn <> NULL (0 Results) SELECT * FROM ...

Maxim Gershkovich

46.6k

asked Apr 14, 2011 at 4:15

379 votes

13 answers

507k views

How do I obtain a Query Execution Plan in SQL Server?

In Microsoft SQL Server how can I get a query execution plan for a query / stored procedure?

Justin

85.8k

asked Sep 9, 2011 at 9:37

366 votes

17 answers

318k views

SET NOCOUNT ON usage

Inspired by this question where there are differing views on SET NOCOUNT... Should we use SET NOCOUNT ON for SQL Server? If not, why not? What it does Edit 6, on 22 Jul 2011 It suppresses the "xx ...

Community wiki

15 revs, 3 users 96%
gbn

355 votes

3 answers

411k views

Can I use multiple "with"?

Just for example: With DependencedIncidents AS ( SELECT INC.[RecTime],INC.[SQL] AS [str] FROM ( SELECT A.[RecTime] As [RecTime],X.[SQL] As [SQL] FROM [EventView] AS A CROSS ...

cnd

33.2k

asked Mar 21, 2011 at 9:13

347 votes

16 answers

704k views

How to check if a stored procedure exists before creating it

I have a SQL script that has to be run every time a client executes the "database management" functionality. The script includes creating stored procedures on the client database. Some of these ...

The Shaper

3,555

asked Jan 15, 2010 at 14:13

344 votes

20 answers

746k views

Counting DISTINCT over multiple columns

Is there a better way of doing a query like this: SELECT COUNT(*) FROM (SELECT DISTINCT DocumentId, DocumentSessionId FROM DocumentOutputItems) AS internalQuery I need to count the number of ...

Novitzky

4,836

asked Sep 24, 2009 at 12:03

337 votes

7 answers

1.0m views

SQL Server SELECT INTO @variable?

I have the following code in one of my Sql (2008) Stored Procs which executes perfectly fine: CREATE PROCEDURE [dbo].[Item_AddItem] @CustomerId uniqueidentifier, @Description ...

bleepzter

9,837

asked Jan 28, 2011 at 1:22

331 votes

10 answers

910k views

Syntax of for-loop in SQL Server

What is the syntax of a for loop in TSQL?

Macho

3,437

asked May 20, 2011 at 7:55

331 votes

5 answers

445k views

SET versus SELECT when assigning variables?

What are the differences between the SET and SELECT statements when assigning variables in T-SQL?

juur

5,703

asked Oct 15, 2010 at 19:17

Collectives™ on Stack Overflow

All Questions

Related Tags