Voice of the DBA

Tag: SQLNewBlogger

  • Bulk Inserting Build Data–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    One of the common tasks that many DBAs need to do is insert data into a database. Often this comes from various sources, but a CSV (comma separate value) format is common. One could use the data import wizard, but that seems to be very flaky with CSVs, so I’ll show a quick way to use the BULK INSERT command.

    This command is a way to read files and load them into a table, similar to how bcp works. However, this is a T-SQL command, and can be included inside your database.

    The basic format is

    BULK INSERT <table>

    FROM <source>

    WITH <options>

    For most CSV imports, this means we need to pick a table, in my case, the BuildStaging table, and a source file. My statement looks like this:

    BULK INSERT dbo.BuildStaging
    FROM ‘e:\Documents\ssc\BuildList_SQLServer2014.csv’

    I also need some options. The basics for a CSV are:

    WITH
    (   FIELDTERMINATOR = ‘,’,
         ROWTERMINATOR = ‘\n’
    );

    There could be other items you want to enable, and there is quite a list. In my case, my file looks like this:

    2017-08-24 14_26_20-E__Documents_ssc_BuildList_SQLServer2014.csv - Sublime Text

    I have a header row, so let’s get rid of that by adding a FIRSTROW = 2 option.

    Now when I run my command, the data is inserted.

    2017-08-24 14_28_12-SQLQuery1.sql - (local)_SQL2016.SSBuilds_1_Dev (PLATO_Steve (62))_ - Microsoft S

    From here, I need to work with the data and clean it futher for insert into other tables.

    SQLNewBlogger

    This was a quick task I needed to accomplish. I knew most of the syntax, but had to double check the option names, and ended up taking about 2 minutes to import the data and 10-15 to write this post.

    And, I’ll likely remember how to do this import after spending time writing about it.

  • Adding a New Default to a Column

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. This is also a part of a basic series on git and how to use it.

    This is a fairly simple process, but I bet more than a few people don’t know how to do it. I had to double check some syntax the other day, and I thought this is a perfect SQLNewBlogger post, based on something I had to (re)learn.

    If I have a table, there are existing columns and I want to add a constraint to one, I need to alter it. To do that, I’ll use the ALTER TABLE x ADD CONSTRAINT syntax.

    One of the common use cases is to add a default date to a date column. For example, I have a Blogs table and want to ensure the CreatedDate column is always populated. I’d do that with

    ALTER TABLE dbo.Blogs
ADD CONSTRAINT df_SysUTCDate DEFAULT SYSDATETIME() FOR createdate;

    In this case, I use ADD CONSTRAINT and then name the constraint. Using specific names is always good since this means I can be sure that I have matches between development, QA, and production.

    I then use the DEFAULT keyword and follow this with a function name. I then use the FOR and the column name. This means I’ve added a default constraint to the CreateDate column and if no value is included in an insert statement, sysdatetime() used.

    This is a basic idea, but one that few developers think about or include in their design. Learn to use defaults and add them when you start building or adding columns.

  • Using Merge–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    I was playing with some data, loading it into staging tables and then moving it to a real table. I typically have done this with T-SQL, writing efficient upsert code that works well. However, I haven’t used Merge in a long time and thought I should practice a bit with the structure.

    Note: Merge isn’t that efficient and most experts do not recommend it (Aaron Bertrand, Dwain Camps). If you decide to use Merge, make sure you are aware of performance implications with your system. It should be fine with smaller sets, but be aware of potential issues if your data scale grows.

    There’s a nice Simple Talk article on Merge that helps you understand how this structure works. There are lots of tips and tricks with merge, but the basic idea is that I can decide to merge data from one table into another and handle the various cases of rows that exist or don’t exist, and what to do. This is the classic upsert, where we insert new rows and update existing ones.

    A Quick Scenario

    I was actually playing with some SQL Saturday data, so let’s use that and set up a few tables. We’ll set up an Event table and an EventStaging table with some data.

    CREATE TABLE Event
(EventID INT PRIMARY KEY CLUSTERED
, EventName VARCHAR(200)
, City VARCHAR(100)
, EventDate DATE
)
GO

CREATE TABLE EventStaging
(EventID INT PRIMARY KEY
, EventName VARCHAR(200)
, City VARCHAR(100)
, EventDate DATE
)
GO

INSERT Event
  VALUES 
   (1  , 'SQLSaturday #1 - Orlando 2007', 'Orlando', '2007-11-10')
, (4  , 'SQLSaturday #4 Tweener(Sun) - Orlando 2008', 'Orlando', '2008-06-05')
, (2  , 'SQLSaturday #2 - Tampa 2008', 'Tampa', '2008-02-15')
, (3  , 'SQLSaturday #3 - Jacksonville 2008', 'Jacksonville', '2008-05-03')

insert dbo.EventStaging
  values 
   (4  , 'SQLSaturday #4 - Orlando 2008', 'Orlando', '2008-06-07')
, (5  , 'SQLSaturday #5 - Olympia 2008', 'Olympia', '2008-10-11')
, (6  , 'SQLSaturday #6 - Cleveland 2008', 'Cleveland', '2009-02-01')
, (7  , 'SQLSaturday #7 - Birmingham 2009', 'Birmingham', '2009-05-30')

    The data is loaded into EventStaging and then needs to move to Event for the application. If you examinet the data, you’ll see that the events with ID =4 is in both tables with different data. Events 5, 6, 7 are only in the staging table and need to be moved.

    We can see the data here:

    2017-07-07 17_24_11-SQLQuery6.sql - (local)_SQL2014.Sandbox (PLATO_Steve (62))_ - Microsoft SQL Serv

    To move this data, let’s start with the merge header

    MERGE dbo.Event ev
  USING dbo.EventStaging es
  ON ev.EventID = es.EventID

    This opening is looking to merge data into the Event table using the EventStaging table as a data source. The join is included in the ON statement and follows the rules like any other join clause.

    The next part of the statement is similar to a CASE statement, with a series of WHEN MATCHED or WHEN NOT MATCHED statements with THEN clauses that determine what happened.

    MERGE dbo.Event ev
  USING dbo.EventStaging es
  ON ev.EventID = es.EventID
  WHEN MATCHED 
   THEN UPDATE 
    SET ev.EventName = es.EventName
      , ev.City = es.City
      , ev.EventDate = es.EventDate
  WHEN NOT MATCHED
   THEN INSERT (EventID, EventName, City, EventDate)
         VALUES (es.EventID, es.EventName, es.City, es.EventDate);

    The two statements I have listed handle the update and insert. The first says that when we match a row, meaning there is a row in EventStaging that matches Event on the EventID, we will update the Event table (that’s the MERGE target). In this case, the rows 5, 6, 7 will fall into this case.

    The WHEN NOT MATCHED is when there is a row in EventStaging that isn’t in Event, we insert the data. Note again, we don’t need to specify the table name in the INSERT.

    When we run this command, the four rows in EventStaging are processed, with 3 inserts and 1 update. After running this, we can see the results here:

    2017-07-07 17_24_40-SQLQuery6.sql - (local)_SQL2014.Sandbox (PLATO_Steve (62))_ - Microsoft SQL Serv

    The name of the Orlando second event (#4) has changed, as has the date.

    This is a quick look at Merge, and a handy command that you should consider using with smaller sets of data.

  • Checking Your Database Properties–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    I was reading Grant’s Database Fundamental Series on Database Properties, and it got me thinking. I think this is a good set of knowledge to have, but building on the properties, can you check them programmatically?

    You can, and here’s how.

    There is a function, DatabasePropertyEX(), that provides you a way to check properties.  You can use this with two parameters to check your database. These parameters are:

    database name – The name of the database, where you can use dbname() for the current database.

    Property name – These are a series of items to check a value for.

    As an example, one of the items Grant mentions is the recovery model. I can check that with this code:

    SELECT DATABASEPROPERTYEX(DB_NAME(), ‘Recovery’)

    In the current database, I get this:

    2017-07-27 14_31_24-SQLQuery8.sql - (local)_SQL2016.TestingTSQL (PLATO_Steve (52))_ - Microsoft SQL

    There are many properties I can check, and I can see a nice list here from SQL Prompt, or I can check the BOL page.

    2017-07-27 14_31_59-SQLQuery8.sql - (local)_SQL2016.TestingTSQL (PLATO_Steve (52))_ - Microsoft SQL

    As nice as it can be to pop open SSMS and look at dialogs, learn to check things programmatically. Once you can do that, you can start to let the system check and alerts you to changes.