SQL Server LocalDb v11.0
Create Tables and Data using SQL Scripts
To save the table to the database click on Update and
check that your table definition has no errors. For safety
it is always worth saving the Create Table SQL script first by
selecting Generate Script. This generates the
full transact SQL script that can be used to re-create the
table if it is lost later. Save it with a clear name such as
CreateWinesTable.Sql
Update the table design a second
time to execute the script and actualy update the database itself.
For some reason you must generate the script first before updating
the database. Doing it the other way around generates a script
that does not contain a Create Table section!!!
The database may need refreshing in the Server Explorer window in
order to see the newly saved table. To enter data into the table
right click on the new data table and select Show Table Data
As the primary key has been set auto-number, leave the id field blank, and
fill in the other fields. In this example my Grape field is going to be
a lookup field so I have entered integer numbers which represent the id
of the required grape from the grapes table. Bit fields are entered as
True or False, even though they are stored as 0 or 1.
After entering suitable test data into your data tables
be sure to save an SQL Srcipt so that the data can be
re-inserted into your data table if the data is lost,
or you want to reset the data to a fixed starting point.
With the data table open and showing data, click on the
script symbol and save the SQL Sript in
your App_Data folder with a suitable name such as
InsertWineData.sql
ALLWAYS CLOSE YOUR DESIGN AND DATA WINDOWS BEFORE MAKING ANY CHANGES
TO DATABASE TABLES.
To create a lookup table, create a second table with just two
fields the id field and a name field, for example GrapeId and
GrapeName. make the id field auto-number, and then enter some suitable
values.
