T-SQL provides lots of functions that help to make data entry through T-SQL much more powerful. Over time you won’t be typing everything into T-SQL directly as we’ll do here. You’ll be able to use stored procedures and parameters to automate the use of scripts. These will also be generated or used by applications.

To  start using T-SQL, you need to open a query window. You can do this by right clicking on a database and selecting the “New Query” command from the context menu. This will open a new query window in the main window on your screen. This is basically just a big, open text box into which you can type commands.

The INSERT Statement

To add rows using T-SQL, the principal statement is the INSERT statement. The way it works is that you have to define where you’re inserting to. This means you have to name a table. Then you must list the columns that you are going to insert data into. This will be a comma delimited list within a set of parenthesis. Then you define the information that you want to insert into each column. You can do this one of two ways. The first, and most common, is to use the VALUES key word and then, again in a set of parenthesis, define a comma delimited list of values. The second method we won’t be using yet. This is to write a SELECT statement to pull data from other locations into your insert statement. This is an example of an INSERT script for the Personnel.Person table.

INSERT  Personnel.Person
(FirstName,
LastName,
DateOfBirth,
CreateDate,
GovernmentID,
CityOfBirth
)
VALUES  ('Albrecht',
'von Wallenstein',
'20 July, 1965',
'8/8/2011 6:16PM',
NULL,
NULL
);

You can see that everything here, while the values are different, is performing the same actions as were done using the GUI up above. I’ve listed all the columns I want to insert into, except for the PersonID column, because that one will be populated automatically. I’ve shown two ways you can enter dates in a string that SQL Server can understand. The last two columns, I did type the value NULL, but note that it’s not within the single quote marks that define a string to SQL Server. This is how you differentiate between a NULL value and the value ‘NULL.’

You can execute the T-SQL by clicking on the Execute button with the mouse, or typing CTL-E from the keyboard. You should see a message in the message window that says (1 row(s) affected). This means that SQL Server successfully added one row.

If I thought that you would have to type all this by hand every time you needed to insert data, I’d suggest using the GUI. But as I said before, I know that you’ll be able to use stored procedures and parameters to make this much easier. However, there are a number of things you can do to make this easier now.

First, if you have a column that accepts NULL values, you don’t have to define that column in the INSERT list. You can leave it off. Just be sure to not provide a value for it. Your INSERT column list should have the exact same number of columns as the VALUES list. This means that this piece of code will insert into the Personnel.Person table just as well:

INSERT  Personnel.Person
(FirstName,
LastName,
DateOfBirth,
CreateDate
)
VALUES  ('Charles',
'Hapsburg',
'18 March, 1951',
GETDATE()
);

If you look at the script, you’ll notice that instead of supplying a date and time again, I’ve taken advantage of the first function you’ve seen, GETDATE(). This function is pretty straight forward. It gets the date and time for the current system and supplies that to SQL Server. Using this, you don’t have to type in the date and time values any more for things like the CreateDate column.

Constantly typing the INSERT column list for every set of data that you want to add to the table reduces efficiency. Instead, you can type those columns once and then add multiple VALUES sets. You just have to put a comma in between each one:

INSERT  Personnel.Person
(FirstName,
LastName,
DateOfBirth,
CreateDate
)
VALUES  ('Elizabeth',
'Stuart',
'7 December, 1981',
GETDATE()
),
('Ernest',
'von Bayern',
'22 February, 1980',
GETDATE()
),
('John',
'William',
'3 August, 1972',
GETDATE()
);

If you run that query in the query window, instead of one row, you’ll see that three (3) rows were affected. It inserted all the rows at once .

The last thing that can help you when you’re typing into a query window is the Intellisense, also known as code completion. If you look at the buttons for the Query Window tool bar, you’ll see one that looks like two rectangles divided in half. If that button is highlighted, then Intellisense is enabled. If it’s not, then Intellisense is turned off. You can toggle Intellisense by clicking the button. You can also toggle Intellisense on or off using the key strokes CTL-Q, CTL-I, one after the other, holding down the Control key the whole time.

With Intellisense enabled, you will see pick lists providing you with database objects as you type in certain parts of the query window. Make sure Intellisense is enabled on your screen and then type INSERT P (that’s the key word INSERT followed by a space and the letter P). Now type hit the keys CTL-SPACE at the same time. A window will pop up that will look something like this:

You can see that Intellisense is fairly smart and is already assuming, based on the commands provided, that you’re typing the schema name for the Personnel schema. Intellisense makes it possible to type out long names quickly because you’re not typing them, you’re selecting them from a targeted list after only typing a few letters. This is a very handy, though somewhat limited, tool to assist you when typing out queries.

Conclusion

As you can see, there’s quite a lot to the T-SQL language just from how much functionality was introduced for the one statement INSERT. This coding method of adding data is far superior to using the SSMS GUI. It becomes even more so as you add stored procedures and additional functionality.

The post Database Fundamentals #13: Data Entry Through T-SQL appeared first on Grant Fritchey.