Database Fundamentals #13: Data Entry Through T-SQL
In this post, we take a look at how you can leverage MS SQL's T-SQL language to help make data entry easier for you. Come check out these fundamentals!
Join the DZone community and get the full member experience.Join For Free
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 keyword 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
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
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 straightforward. 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 anymore for things like the
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 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 toolbar, 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 keystrokes 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 keyword
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.
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.
Published at DZone with permission of Grant Fritchey, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.