1. Ensure that you have a Query Editor window open, connected to our ApressFinancial database, and that you are logged in with an account that has insert permissions on the ShareDetails.Shares table (this will be any member of the administrator’s or database owner’s role).

2. Right-click against the ShareDetails.Shares table, select Script Table As ➤ INSERT To ➤ New Query Editor Window.

3. This will bring up the following code. SQL Server covers itself concerning the use of reserved words, spaces in names, etc., by surrounding every object name with square brackets. It also fully qualifies the table name with the database name and schema owner, in this case, ShareDetails. Moving to the values, you can see the column name repeated so that when altering the values, if the table has a large number of columns, you know which column you are working with. The final part in the jigsaw is an indication to the data type and length to aid you as well.

INSERT INTO [ApressFinancial].[ShareDetails].[Shares]
([ShareDesc]
,[ShareTickerId]
,[CurrentPrice])
VALUES
(<ShareDesc, nvarchar(50),>
,<ShareTickerId, nvarchar(50),>
,<CurrentPrice, numeric,>)

4. We need to place a modification at the top of this code, just to ensure that Query Editor has a setting to allow double quotes to be used to surround strings. This was covered in Chapter 5 when discussing database options. To cover yourself though, you can always place the following code at the start of queries where quotation marks will be used. There is one hidden downfall that will be covered at the end. Notice as well that a GO command is included at the end of the SET command. This is because this command must take place in its own transaction.

SET QUOTED_IDENTIFIER OFF
GO

5. By altering the code within the Query Editor pane, you will see that the next section of code actually inserts the data into the ShareDetails.Shares table. Notice that no GO statement is included at the end of this code. It is not necessary because there is only one INSERT and no other commands that need to form part of this same transaction.

SET QUOTED_IDENTIFIER OFF
GO
INSERT INTO [ApressFinancial].[ShareDetails].[Shares]
([ShareDesc]
,[ShareTickerId]
,[CurrentPrice])
VALUES
("ACME'S HOMEBAKE COOKIES INC",
'AHCI',
2.34125)

6. Now that all the information has been entered into the Query Editor pane, it is time to execute the code. Press F5 or Ctrl+E, or click the execute button on the toolbar. You should then see the following result, which indicates that there has been one row of data inserted into the table.

(1 row(s) affected)

This now sees the first record of information placed into the database in the ShareDetails.Shares table. It is simple and straightforward. All the columns have been listed and a value has been inserted. Because the name had a single quotation mark within it, it is simpler to surround the name with double quotation marks. However, to make sure that this string was not seen as an identifier, we have to switch that option off. SQL Server Management Studio has the ability to create template scripts for several T-SQL commands.

When using the scripting options within Query Editor, it is possible to build the script as you have just seen for inserting a record into the ShareDetails.Shares table, and save the T-SQL within a new Query Editor pane, to a file, or even to a clipboard. This would then allow the data to be reinserted instantaneously should the table be deleted. To an extent, scripting to files or a clipboard is not as useful as scripting to a Query Editor pane.

By scripting to files or a clipboard, you would need to move back into these files to make the necessary changes for data insertion. As you saw, when the script is placed in the Query Editor pane, the table and the columns are listed, but obviously
the values need to be altered. This would have to be completed in a file or a clipboard by reopening these contents and making modifications after the event.

The scripting template does build the whole INSERT command and lists all the columns as well as—in the VALUES section of the command—the name of the column and its data type definition. From there, it is easier to know what value is expected within the INSERT command line.

The example mentions that using SET QUOTED_IDENTIFIER OFF does have one hidden downfall: In many cases, when using T-SQL commands, it is possible to surround reserved words with double quotation marks, rather than square brackets; however, with the QUOTED_IDENTIFIER set to OFF, you will only be able to surround reserved words with square brackets. If you had QUOTED_IDENTIFIER set to ON, then you could not have put ACME'S in the name; the code would have to have been written with two single quotation marks. Therefore, the code would have had to look like the following:

INSERT INTO [ApressFinancial].[ShareDetails].[Shares]
([ShareDesc]
,[ShareTickerId]
,[CurrentPrice])
VALUES
('ACME''S HOMEBAKE COOKIES INC',
'AHCI',
2.34125)

Now that you know how to construct an INSERT statement .....