Creating and Saving an INSERT Script

www.altova.com Print this Topic Previous Page Up One Level Next page

Home >  Tutorials > "ZooDB" Database > Inserting Data into the Database >

Creating and Saving an INSERT Script

The context menu that opens when you right-click a table and drag it into an SQL Editor window provides the possibility to generate an INSERT statement based on an existing table.

 

Assuming that the zoo in our tutorial has just hired two new zookeepers, you will create a script that adds these two new employees to the table tblZookeepers, and save that script in your project.

 

 

To create a script that inserts two new zookeepers into tblZookeepers:

1.Click the SQL Editor ic_new-sql-editor icon in the Standard toolbar or select the menu option File | New | SQL Editor (Ctrl+N) to open a new SQL Editor window.
2.Using the right mouse button, drag and drop the tblZookeepers table from the Online Browser into the SQL Editor window. A context-sensitive menu appears.
3.Select Insert from the context menu.

app_online-browser-context-insert

The following statement appears in the SQL window:

INSERT INTO [ZooDB].[dbo].[tblZookeepers] ( [ZookID], [FirstName], [LastName], [Address], [City], [State], [Telephone], [DOB] ) VALUES ( , '', '', '', '', '', '', '' );

4.Repeat steps 2 and 3 to create a second INSERT statement.
5.Alter the first INSERT statement as follows:

 

INSERT INTO [ZooDB].[dbo].[tblZookeepers] ([FirstName], [LastName], [Address], [City], [State], [Telephone], [DOB] ) VALUES ('June', 'Bug', '1345 Gorilla Highway', 'Newark', 'NJ', '(515)555-7217', '1963-11-26');

 

Note:No value is being inserted for the column ZookID, because it is an identity column and its value will be automatically generated. You must therefore delete the [ZookID] entry and the first comma from the statement.

 

6.Alter the second INSERT statement to add the following values:

 

INSERT INTO [ZooDB].[dbo].[tblZookeepers] ([FirstName], [LastName], [Address], [City], [State], [Telephone], [DOB] ) VALUES ('Bruno', 'Katz', '87 Panther Place', 'Trenton', 'NJ', '(515)555-7667', '1969-09-13');

 

7.Click the Execute ic_execute-sql button or press F5.
8.To check the results, open a new SQL Editor window and enter the statement SELECT * FROM tblZookeepers. Execute the statement. Optionally, click the Maximize ic_maximize button to extend the size of the Result window. Notice that two new rows have been inserted into the table.

sql_select-zookeepers

 

 

To save the SQL script and add it to the project:

1.Make the SQL Editor window that contains the INSERT statements the active window and click the Save ic_save button in the Standard toolbar. The Save As dialog box opens.
2.Enter a name for the script, e.g., New_keepers (.sql), select a folder and click the Save button. The Add to Project dialog box opens (if the dialog box does not pop up, check whether the Do not show dialog option is checked in the Newly saved files group box of the General options).

dlg_add-to-project

3.Click the Add to Project button. Alternatively, or if the Add to Project dialog box is not displayed, you can also click the Skip Project button and do one of the following:

 

Click the Project tab, right-click the SQL folder, and select Add Active File to Project from the context menu.
Click the Add the Active File ic_add_active_file icon in the toolbar of the Project window.

app_online-browser-context-add-active-file

Right-click the name tab in the SQL Editor and select Add to Project from the context menu that opens.

app_sql-context-add-active-file

4.Right-click the project's title, ZooDBProject, and select Save Project from the context menu, or click the Save Project ic_save_project icon in the toolbar of the Project window.

© 2019 Altova GmbH