Multi Row Insert

Performing a multi-row insert got easier in SQL 2008. A new syntax was added to accomodate multiple rows when using the VALUES statement. This code will only work in 2008+. In order to accomplish the same thing in earlier versions, you need to use the UNION statement. Here is the SQL 2008 syntax: [cc lang=”sql”] CREATE TABLE #meme ( id INT IDENTITY(1,1), first_name VARCHAR(50), last_name VARCHAR(50) ) INSERT INTO #meme ( first_name, last_name ) VALUES (‘john’, ‘smith’), (‘molly’, ‘jones’), (‘summer’, ‘fitzgerald’) SELECT * FROM #meme [/cc] To perform the same thing in earlier versions of SQL Server, you can use the UNION ALL statement: [cc lang=”sql”] INSERT INTO #meme ( first_name, last_name ) SELECT ‘john’, ‘smith’ UNION ALL SELECT ‘molly’, ‘jones’ UNION ALL SELECT ‘summer’, ‘fitzgerald’ SELECT * FROM #meme [/cc] Remember, you want to use UNION ALL instead of just UNION, otherwise if you specify to rows with the same values to be inserted, it will only insert one — as UNION inherently performs a DISTINCT. The last way is to simply call the insert statement multiple times. [cc lang=”sql”] INSERT INTO #meme ( first_name, last_name ) VALUES (‘john’, ‘smith’); INSERT INTO #meme ( first_name, last_name ) VALUES (‘molly’, ‘jones’); INSERT INTO #meme ( first_name, last_name ) VALUES (‘summer’, ‘fitzgerald’); [/cc]

Continue reading ...

Using WHERE EXISTS

As you may know, the WHERE EXISTS clause is used to constrain records from an outer query with records from an inner query. What does that mean? It’s just another way to say “it limits the result set”. And in SQL, especially in optimization purposes, limiting the result set is the name of the game. USAGE There are basically 6 ways to constrain data. The relationship between the tables and the performance impact will determine which method you need. EXISTS IN JOIN CROSS APPLY LEFT JOIN with WHERE OUTER APPLY with WHERE When you need to limit a result set between two tables that have a 1-1 relationship, it makes little difference which method you use. However if you have a 1-many or many-many relationship you are forced to use either EXISTS or IN because a join will match all records that match forcing you to do the dreaded DISTINCT. Overall, the exists is probably the most performant because it is handles limiting result sets for one-to-many and many-to-many tables most efficiently. This is because it performs a boolean operation where it finds the first instance of existence and returns saying “YES THIS DOES EXIST”. Now, that’s not to say that EXISTS is always more efficient. There are instances I’ve found where a LEFT JOIN WHERE IS NULL is more efficient than using NOT EXISTS but that’s a different article. Also, if there is a chance down the road you will need columns from the table you are constraining against […]

Continue reading ...

Format String to Date

Changing the datatype of a string that represents a date to an actual date datatype is relatively easy using the CAST function. As long as the date string is in a format recognizable to the regional settings defined on your SQL Server, the conversion can take place with a one line command. There are a few different date datatypes you can choose from. The safest best would be to use the datetime datatype. This is one of the larger date datatypes (8 bytes) that can accommodate dates between the range of ‘1753-01-01 00:00:00.000’ AND ‘9999-12-31 23:59:59.997’ The following string representations of all ‘2012-01-01’ all convert the same with my English (US) settings: [cc lang=”sql”] SELECT CAST(‘2012-01-01′ AS DATETIME) SELECT CAST(’01-01-2012’ AS DATETIME) SELECT CAST(‘2012/01/01′ AS DATETIME) SELECT CAST(’01/01/2012’ AS DATETIME) SELECT CAST(‘010112’ AS DATETIME) SELECT CAST(‘010112’ AS DATETIME) [/cc] SQL Server 2008+ introduced additional date datatypes including DATE & TIME. These data types function as you would expect, only keeping the date without the time, or the time without the date. [cc lang=”sql”] SELECT CAST(‘2012-01-01’ AS DATE) [/cc] The most compatible string format you can use to represent both date & time together is the following. This allows for easy conversion back and forth: [cc lang=”sql”] SELECT CAST(‘2012-01-01T23:05:00.000’ as datetime) SELECT CAST(‘2012-01-01T23:05:00.000’ as time) [/cc] That is the letter T separating the date from the time.

Continue reading ...

Delete Using INNER JOIN

The need to join tables in order to determine which records to delete is a common requirement. The syntax can be somewhat tricky because you need to use an alias for the table you want to delete from. First, here is a simple example: [cc lang=”sql”] DELETE o FROM #orders o JOIN #customers c ON c.customer_id = o.customer_id WHERE c.last_name = ‘jones’ AND c.first_name = ‘alexandria’ [/cc] The following query will delete all the order records from the customers who have the name alexandria jones. This will only delete records from the orders table, however I strongly suggest you perform a SELECT statement prior to actually performing the delete using the same query as such: [cc lang=”sql”] –DELETE o SELECT o.* FROM #orders o JOIN #customers c ON c.customer_id = o.customer_id WHERE c.last_name = ‘jones’ AND c.first_name = ‘alexandria’ [/cc] This will show you the exact records you are about to delete. Once you get the count, I actually suggest you take one additional safety measure to ensure you don’t delete more records by using the TOP clause: [cc lang=”sql”] –DELETE o SELECT count(o.*) FROM #orders o JOIN #customers c ON c.customer_id = o.customer_id WHERE c.last_name = ‘jones’ AND c.first_name = ‘alexandria’ — if the above query returns 5 records, set 5 below DELETE TOP (5) o FROM #orders o JOIN #customers c ON c.customer_id = o.customer_id WHERE c.last_name = ‘jones’ AND c.first_name = ‘alexandria’ [/cc] Once again.. test, test, test before you delete!

Continue reading ...

Alter Table Drop Column

Dropping a column is a fairly straightforward operation as long as no dependencies depend on the column. The following code tested on SQL 2008 returns back almost instantly even when tested against tables with over 1 million rows. It seems the drop column statement performs similar to a truncate statement in that it simply moves a pointer and does not actually delete the data from the pages. The syntax is simple, we can pass in a comma delimited list of columns to drop from the table. [cc lang=”sql”] ALTER TABLE ##my_tables DROP COLUMN first_name, last_name [/cc] For the first example, we’ll create a table and then drop one of it’s columns. [cc lang=”sql”] IF OBJECT_ID(‘tempdb..##meme’) IS NOT NULL BEGIN DROP TABLE ##meme END CREATE TABLE ##meme ( first_name VARCHAR(50), last_name VARCHAR(50), ssn VARCHAR(9) CONSTRAINT ssn_unique UNIQUE ) ALTER TABLE ##meme DROP COLUMN first_name [/cc] That was easy, it simply dropped the column. It won’t always happen that way however. If we have a constraint, index, or key that depends on the column, we need to drop or disable that first. [cc lang=”sql”] IF OBJECT_ID(‘tempdb..##meme’) IS NOT NULL BEGIN DROP TABLE ##meme END CREATE TABLE ##meme ( first_name VARCHAR(50), last_name VARCHAR(50), ssn VARCHAR(9) CONSTRAINT ssn_unique UNIQUE ) ALTER TABLE ##meme DROP COLUMN first_name, ssn [/cc] Here we get the error: So we need to drop the constraint in this case prior to dropping the column. [cc lang=”sql”] IF OBJECT_ID(‘tempdb..##meme’) IS NOT NULL BEGIN DROP TABLE ##meme END CREATE TABLE ##meme ( […]

Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!