Voice of the DBA

Tag: T-SQL

  • Insert multiple rows from one INSERT statement

    One of the things that has been annoying for a long time in SQL Server is adding lots of data for testing. Personally I use Data Generator, and I recommend that, but for a quick few rows of data, do you want to do this:

    CREATE TABLE SalesOrders
( OrderID INT IDENTITY(1,1)
, OrderDate DATETIME
, CustomerID INT
, OrderAmount NUMERIC(10, 4)
)
GO
INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
  SELECT '1982-05-19 06:31:48.950', 1,           579040.5070
INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
  SELECT '1994-11-27 17:14:41.790', 2,           348808.5860
INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
  SELECT '1972-11-08 17:40:01.170', 3,           758992.3650
INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
  SELECT '1972-05-31 01:19:05.530', 4,           779853.1990
INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
  SELECT '1994-12-22 10:40:57.410', 5,           666173.8040

    There’s a lot of INSERT typing, even with copy/paste functionality and editing the various rows gets to be cumbersome of you actually get some sample data like this that you want to convert (say from someone’s blog post of results:

    1, 12/1/2011, 3, 123

    2, 12/2/2011, 4, 2

    3, 12/1/2011, 3, 123

    You could do the UNION thing, like this:

    INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
  SELECT '1982-05-19 06:31:48.950', 1,           579040.5070
UNION ALL
  SELECT '1994-11-27 17:14:41.790', 2,           348808.5860
UNION ALL
  SELECT '1972-11-08 17:40:01.170', 3,           758992.3650
UNION ALL
  SELECT '1972-05-31 01:19:05.530', 4,           779853.1990
UNION ALL
  SELECT '1994-12-22 10:40:57.410', 5,           666173.8040

    That works, but it’s still a little cumbersome.

    In SQL Server 2008, there’s a better way. You can now include multiple sets of data in your insert, like this:

    INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
VALUES 
(           '1982-05-19 06:31:48.950', 1,           579040.5070),
(           '1994-11-27 17:14:41.790', 2,           348808.5860),
(           '1972-11-08 17:40:01.170', 3,           758992.3650),
(           '1972-05-31 01:19:05.530', 4,           779853.1990),
(           '1994-12-22 10:40:57.410', 5,           666173.8040)

    Just put brackets around each set of data, and you can easily insert multiple rows.

  • T-SQL Tuesday #26 – Second Changes with Date/Time

    TSQL2sDay150x150

    I missed the very first T-SQL Tuesday, so when this month’s topic of second chances came up, I decided to write that one.

    If you are unsure of what T-SQL Tuesday is, follow the link to this month’s topic to get the rules and description and then write a blog post.

    Date/Time Challenges

    I picked an easy one, but one that I continue to see asked in the forums by people new to SQL Server. I suspect we’ll see less questions over time as more people take advantage of the new DATE and TIME datatypes in SQL Server 2008 and later, but maybe not. Lots of people are still sure that they need to keep those items together.

    In any case, have you ever seen sales data like this:

    OrderID     OrderDate               CustomerID  OrderAmount
    ———– ———————– ———– ————
    1           1982-05-19 06:31:48.950 1           579040.5070
    2           1994-11-27 17:14:41.790 2           348808.5860
    3           1972-11-08 17:40:01.170 3           758992.3650
    4           1972-05-31 01:19:05.530 4           779853.1990
    5           1994-12-22 10:40:57.410 5           666173.8040
    6           1974-04-03 01:42:29.490 6           134218.2330
    7           1976-06-22 15:21:18.910 7           322938.6950
    8           1953-08-05 23:00:34.620 8           14169.7580
    9           1971-08-16 22:33:28.970 9           586057.3820
    10          2002-03-28 13:08:00.420 10          632785.0760

    Here’s some DDL, you can create your own data, but here are a few rows:

    CREATE TABLE SalesOrders
( OrderID INT IDENTITY(1,1) , OrderDate DATETIME , CustomerID INT , OrderAmount NUMERIC(10, 4) ) INSERT SalesOrders (OrderDate, CustomerID, OrderAmount) VALUES ( '1982-05-19 06:31:48.950', 1, 579040.5070), ( '1994-11-27 17:14:41.790', 2, 348808.5860), ( '1972-11-08 17:40:01.170', 3, 758992.3650), ( '1972-05-31 01:19:05.530', 4, 779853.1990), ( '1994-12-22 10:40:57.410', 5, 666173.8040)

    If I want to get all the sales in May of 1972, I get query them all like this:

    SELECT OrderDate
, OrderAmount
 FROM SalesOrders
 WHERE OrderDate > '1972/5/1' AND OrderDate <= '1972/5/31'

    I get 4 rows back. That’s something people often write when they get input from a user. A user has a start and end edit box, they enter “1972/5/1’” in the start box (or use a calendar picker) and then enter “1972/5/31” in the other. I’m using ISO dates to make this clear, though in the US it would normally display like “5/1/1972” and in the UK as “1/5/1972”.

    However, that isn’t quite correct. If I run this query:

    SELECT OrderDate
, OrderAmount
 FROM SalesOrders
 WHERE MONTH(OrderDate) = 5
  AND YEAR(OrderDate) = 1972

    I actually get 6 rows. The data rows for May 1972 are:

    OrderDate               OrderAmount

    ———————– —————————-

    1972-05-31 01:19:05.530 779853.1990

    1972-05-13 09:26:52.590 676848.9700

    1972-05-28 21:05:28.840 923425.0510

    1972-05-07 10:59:09.930 266079.6480

    1972-05-01 04:21:01.250 464241.6480

    1972-05-31 01:19:05.530 779853.1990

    What’s happening?

    If you look at the OrderDates for May 31, you see two values that have a time of 1:19:05am. Those are excluded from the query, which has an end date of “1972/5/31”. Why? This query:

    SELECT CAST( '1972/5/31' AS DATETIME)

    shows why. It returns:

    1972-05-31 00:00:00.000

    That’s midnight between the 30th and 31st, which is before 1:19:05am. When a datetime value is converted in SQL Server, without a time component, it defaults to the beginning of the day. That works great for the start date, not so good for the end date.

    Fixing this

    There are two real fixes here. Well, maybe more. You can query on the month and year, but those functions can disrupt indexes, so I don’t recommend them. The two main fixes are:

    • add a time component
    • add a day

    The first fix is the addition of the last time of the day to your query.

    SELECT OrderDate
, OrderAmount
 FROM SalesOrders
 WHERE OrderDate > '1972/5/1' AND OrderDate <= '1972/5/31 23:59:59.997PM'

    In some type of code, it looks more like this:

    DECLARE @end DATETIME SELECT @end = '1972/5/31' SELECT @end = @end + '23:59:59.997' SELECT OrderDate
, OrderAmount
 FROM SalesOrders
 WHERE OrderDate > '1972/5/1' AND OrderDate <= @end

    Assume the first select is actually coming from the user.

    The second fix is to add a day, and actually query like this, which is what I’d recommend:

     SELECT OrderDate
, OrderAmount
 FROM SalesOrders
 WHERE OrderDate > '1972/5/1' AND OrderDate < '1972/6/1'

    In this case, instead of querying for May 31, we move to June 1 (the next day) and then change the <= to a < only. This gets all orders occurring up until the end of May 31, but before June 1.

    Easy fixes, but so often there’s code that doesn’t allow for the time component. Take a minute and check your reports, and be sure you aren’t underreporting any data to your clients or customers.

    Also be sure to check out the new datatypes in SQL Server 2008 and later:

  • Referencing Remote Data

    Maybe if I searched more, I'd use synonyms

    One of the features added to SQL Server a few years ago were the ability to create synonyms and use those to reference other objects. The ability to create synonyms is something that I had wanted for years in SQL Server, but when they were released, I found them to be a tool that I rarely reached for. Whether it was because this was something I rarely needed to accomplish, or because my habits were too ingrained, I’m not sure, but I have only created synonyms for testing purposes and not for use in any production databases.

    When a developer needs to reference data in another database (or on another server), they have a variety of ways in which they can do this. Some people prefer a three or four part naming convention, others use a view local to the database, and still others might use synonyms. While they all work, from a maintenance standpoint, I think a view or synonym provide a nice layer of abstraction while minimizing the potential maintenance headaches of future changes.

    If you find synonyms more useful than local views, I would be interested in knowing why. They seem to almost operate in the same way to me, but for some reason I find views to be easier to track and manage. Perhaps it’s just an ingrained habit from years of making do with views, or maybe it’s my habit of browsing for objects, instead of using a tool like SQL Search.

    Whichever method you use, I do urge you to always consider a layer of abstraction. That other database you reference might be located on the same instance today, but in the future it might grow and require it’s own instance. If you have three part naming buried in all of your stored procedure or application code, it might not be as simple as a global search and replace to make changes. If it’s not, then you are wasting development time down the road by not having a layer of abstraction implemented at the beginning.

    Steve Jones

    Steve Jones

    The Voice of the DBA Podcasts

  • How Many Calls? Part 2

    I wrote recently about a report that a friend of mine needed from his Access database. In the first post, I used a CTE and an outer join to cull together some data, however since I wasn’t sure Access would support any of that, I came up with another way.

    If you read the last post, you’ll have my setup code and sample data. In this one I decided to move to sub-queries as the first part of my work. I started with this:

    SELECT a.date_sent 'date_sent' , ISNULL( a.five_day_call, 0) 'five_day_calls' , ISNULL( b.ten_day_call, 0) 'ten_day_calls' FROM ( SELECT date_sent
       , COUNT(*) 'five_day_call' FROM calls
       WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 6
       GROUP BY date_sent
) a
inner JOIN ( SELECT date_sent
  , COUNT(*) 'ten_day_call' FROM calls
   WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 11
   GROUP BY date_sent
 ) b
   ON a.date_sent = b.date_sent
 go

    I know this doesn’t work because both queries don’t have all the dates. However there was a better way, and one I should have thought of immediately. A few people noticed this in the comments to the previous post. We are looking to count records, according to some criteria. The SUM function will do this. If I run this, I get a count of calls in my 5 day window.

    SELECT a.date_sent 'date_sent' , SUM( CASE WHEN DATEDIFF(DAY, date_sent, acc_call_date) < 6 THEN 1 ELSE 0)

    FROM calls GROUP BY date_sent

    That worked, and it’s easy to understand. What SQL Server does is look through each row, and if there is a row that matches the DATEDIFF function, meaning the call back date is less than 6 days from the call date, then it returns a 1 for that row. Otherwise a 0 is returned. If I then sum the results for each day, I essentially get a count for the date since each call counts as one (or zero).

    To get the final query, I can then do this, adding a column for each type of row I am looking for.

    SELECT a.date_sent
 , SUM( CASE WHEN DATEDIFF(DAY, date_sent, acc_call_date) < 6 THEN 1 ELSE 0) 'five_day_calls' 
 , SUM( CASE WHEN DATEDIFF(DAY, date_sent, acc_call_date) < 11 THEN 1 ELSE 0) 'ten_day_calls'
    FROM dbo.Calls

    GROUP BY date_sent

    That produced the correct results, and it was easy to convert to Access.

    Again, I’m not sure this is the most efficient way to do this, but it worked and it was an interesting problem. If you have a better way to do it, let me know.