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

3268 votes

45 answers

3.8m views

How to add a column with a default value to an existing table in SQL Server?

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Mathias

33.8k

asked Sep 18, 2008 at 12:30

2418 votes

48 answers

3.3m views

How to concatenate text from multiple rows into a single text string in SQL Server

Consider a database table holding names, with three rows: Peter Paul Mary Is there an easy way to turn this into a single string of Peter, Paul, Mary?

JohnnyM

29.2k

asked Oct 11, 2008 at 23:49

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

1840 votes

41 answers

564k views

Table Naming Dilemma: Singular vs. Plural Names [closed]

Academia has it that table names should be the singular of the entity that they store attributes of. I dislike any T-SQL that requires square brackets around names, but I have renamed a Users table ...

Community wiki

7 revs, 2 users 63%
ProfK

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

1739 votes

14 answers

2.1m views

How can I delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER ...

nettoon493

18k

asked May 10, 2013 at 11:38

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

1435 votes

7 answers

985k views

What is the difference between JOIN and INNER JOIN?

Both these joins will give me the same results: SELECT * FROM table JOIN otherTable ON table.ID = otherTable.FK vs SELECT * FROM table INNER JOIN otherTable ON table.ID = otherTable.FK Is there ...

driis

163k

asked Feb 19, 2009 at 14:47

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

1210 votes

29 answers

2.6m views

SQL Update from One Table to Another Based on a ID Match

I have a database with account numbers and card numbers. I match these to a file to update any card numbers to the account number so that I am only working with account numbers. I created a view ...

Boerseun

asked Oct 22, 2008 at 7:14

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

1107 votes

24 answers

2.5m views

Search text in stored procedure in SQL Server

I want to search a text from all my database stored procedures. I use the below SQL: SELECT DISTINCT o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN ...

DharaPPatel

12.3k

asked Feb 5, 2013 at 9:30

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

1011 votes

25 answers

1.7m views

Reset identity seed after deleting records in SQL Server

I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each ...

xorpower

18.5k

asked Feb 17, 2014 at 8:51

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

879 votes

13 answers

526k views

Count(*) vs Count(1) - SQL Server

Just wondering if any of you people use Count(1) over Count(*) and if there is a noticeable difference in performance or if this is just a legacy habit that has been brought forward from days gone ...

super9

29.7k

asked Aug 3, 2009 at 10:15

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

803 votes

11 answers

1.5m views

Rename column SQL Server 2008

I am using SQL Server 2008 and Navicat. I need to rename a column in a table using SQL. ALTER TABLE table_name RENAME COLUMN old_name to new_name; This statement doesn't work.

Serhio g. Lazin

9,512

asked Apr 30, 2013 at 9:34

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

738 votes

22 answers

930k views

Solutions for INSERT OR UPDATE on SQL Server

Assume a table structure of MyTable(KEY, datafield1, datafield2...). Often I want to either update an existing record, or insert a new record if it doesn't exist. Essentially: IF (key exists) run ...

Chris Cudmore

30k

asked Sep 20, 2008 at 15:00

698 votes

11 answers

1.5m views

How to insert a line break in a SQL Server VARCHAR/NVARCHAR string

I didn't see any similar questions asked on this topic, and I had to research this for something I'm working on right now. Thought I would post the answer for it in case anyone else had the same ...

Mark Struzinski

33.3k

asked Aug 27, 2008 at 19:53

639 votes

31 answers

671k views

Is there a Max function in SQL Server that takes two values like Math.Max in .NET?

I want to write a query like this: SELECT o.OrderId, MAX(o.NegotiatedPrice, o.SuggestedPrice) FROM Order o But this isn't how the MAX function works, right? It is an aggregate function so it ...

skb

30.6k

asked Sep 23, 2008 at 22:52

639 votes

11 answers

1.9m views

NOT IN vs NOT EXISTS

Which of these queries is the faster? NOT EXISTS: SELECT ProductID, ProductName FROM Northwind..Products p WHERE NOT EXISTS ( SELECT 1 FROM Northwind..[Order Details] od WHERE p....

ilitirit

16.3k

asked Oct 6, 2008 at 2:19

613 votes

9 answers

1.3m views

UPDATE and REPLACE part of a string

I've got a table with two columns, ID and Value. I want to change a part of some strings in the second column. Example of Table: ID Value --------------------------------- 1 c:...

aston_zh

6,693

asked Jun 28, 2013 at 12:55

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

605 votes

19 answers

682k views

What is the best way to paginate results in SQL Server

What is the best way (performance wise) to paginate results in SQL Server 2000, 2005, 2008, 2012 if you also want to get the total number of results (before paginating)?

Panagiotis Korros

11k

asked Sep 20, 2008 at 20:16

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

567 votes

21 answers

1.4m views

Adding an identity to an existing column

I need to change the primary key of a table to an identity column, and there's already a number of rows in table. I've got a script to clean up the IDs to ensure they're sequential starting at 1, ...

Kirschstein

14.7k

asked Jun 26, 2009 at 13:41

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

532 votes

33 answers

1.3m views

Cannot insert explicit value for identity column in table 'table' when IDENTITY_INSERT is set to OFF

I have the below error when I execute the following script. What is the error about, and how it can be resolved? Insert table(OperationID,OpDescription,FilterID) values (20,'Hierachy Update',1) ...

Jaison

asked Aug 26, 2009 at 11:19

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

515 votes

3 answers

1.0m views

Nested select statement in SQL Server

Why doesn't the following work? SELECT name FROM (SELECT name FROM agentinformation) I guess my understanding of SQL is wrong, because I would have thought this would return the same thing as ...

Brennan Vincent

11k

asked Jan 7, 2011 at 20:27

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

505 votes

8 answers

896k views

How Stuff and 'For Xml Path' work in SQL Server?

Table is: Id Name 1 aaa 1 bbb 1 ccc 1 ddd 1 eee Required output: Id abc 1 aaa,bbb,ccc,ddd,eee Query: SELECT ID, abc = STUFF( (SELECT ',' + name FROM temp1 FOR XML PATH (...

Puneet Chawla

5,899

asked Jul 3, 2015 at 17:03

503 votes

8 answers

2.0m views

How do I query for all dates greater than a certain date in SQL Server?

I'm trying: SELECT * FROM dbo.March2010 A WHERE A.Date >= 2010-04-01; A.Date looks like: 2010-03-04 00:00:00.000 However, this is not working. Can anyone provide a reference for why?

Eric Francis

23.7k

asked May 17, 2012 at 20:56

500 votes

11 answers

525k views

Exporting data In SQL Server as INSERT INTO

I am using SQL Server 2008 Management Studio and have a table I want to migrate to a different db server. Is there any option to export the data as an insert into SQL script??

Jack Kada

24.9k

asked Oct 4, 2009 at 8:57

496 votes

5 answers

1.4m views

Efficiently convert rows to columns in sql server

I'm looking for an efficient way to convert rows to columns in SQL server, I heard that PIVOT is not very fast, and I need to deal with lot of records. This is my example: Id Value ColumnName 1 John ...

tbag

5,003

asked Apr 1, 2013 at 14:11

Collectives™ on Stack Overflow

All Questions

Related Tags