Microsoft SharePoint Team Blog
The official blog of the Microsoft SharePoint Product Group

Data Entry With SQL


Introduction



To perform data entry using SQL:

In the Object Explorer, you can right the table, position the mouse on Script Table As -> INSERT To -> New Query Editor Window
Open an empty query window and type your code
In the SQL, data entry is performed using the INSERT combined with the VALUES keywords. The primary statement uses the following syntax:

INSERT TableName VALUES(Column1, Column2, Column_n);
Alternatively, or to be more precise, you can use the INTO keyword between the INSERT keyword and the TableName factor to specify that you are entering data in the table. This is done with the following syntax:

INSERT INTO TableName VALUES(Column1, Column2, Column_n)
The TableName factor must be a valid name of an existing table in the database you are using. If the name is wrong, the SQL interpreter would simply consider that the table you are referring to doesn't exist. Consequently, you would receive an error.

The VALUES keyword indicates that you are ready to list the values of the columns. The values of the columns must be included in parentheses.

If the column is a BIT data type, you must specify one of its values as 0 or 1.

If the column is a numeric type, you should pay attention to the number you type. If the column was configured to receive an integer (int, bigint, smallint), you should provide a valid natural number without the decimal separator.

If the column is for a decimal number (float, real, decimal, numeric), you can type the value with its character separator (the period for US English).

If the column was created for a date data type, make sure you provide a valid date.

If the data type of a column is a string type, you should include its entry between single quotes. For example, a shelf number can be specified as 'HHR-604' and a middle initial can be given as 'D'.

In the previous paragraphs, we were stating "you" as if you will be the one performing data entry. In reality, the user will be performing data entry on your products. Therefore, it is your responsibility to reduce, as much as possible, the likelihood of mistakes. Of course, there are various ways, through a "visual" application such as Borland C++ Builder, Microsoft Visual Basic, C#, or MS Visual C++, etc, that you can take care of this.
Adjacent Data Entry



The most common technique of performing data entry requires that you know the sequence of fields of the table in which you want to enter data. With this subsequent list in mind, enter the value of each field in its correct position.

During data entry on adjacent fields, if you don't have a value for a numeric field, you should type 0 as its value. For a string field whose data you don't have and cannot provide, type two single-quotes '' to specify an empty field.

Practical Learning: Performing Adjacent Data Entry



To open a new query window, press Ctrl + N
In the query window, to create one record, type:
USE WorldStatistics;
GO
INSERT INTO Countries
VALUES('Angola', 1246700, 12127071, 'Luanda','ao');
GO


Press F5 to execute
Delete the top section of the window
To enter various records at the same time, enter the following statement:
USE WorldStatistics;
GO
INSERT INTO Countries
VALUES('Mexico', 1972550, 107449525, 'Mexico City','mx');
GO
INSERT INTO Countries
VALUES('South Africa', 1219912, 44187637, 'Pretoria','za');
GO
INSERT INTO Countries
VALUES('Iraq', 0, 0, 'Baghdad','iq');
GO
INSERT INTO Countries
VALUES('United States', 9826630, 0, '', '');
GO
INSERT INTO Countries
VALUES('Saudi Arabia', 2149690, 0, 'Riyadh', '');
GO


Press F5 to execute the statement
Random Data Entry



The adjacent data entry we have performed requires that you know the position of each column. The SQL provides an alternative that allows you to perform data entry using the name of a column instead of its position. This allows you to provide the values of columns in an order of your choice. We have just seen a few examples where the values of some of the fields were not available during data entry. Instead of remembering to type 0 or NULL for such fields or leaving empty quotes for a field, you can use the fields' names to specify the fields whose data you want to provide.

To perform data entry in an order of your choice, you must provide your list of the fields of the table. You can either use all columns or provide a list of the same columns but in your own order. In the same way, you don't have to provide data for all fields, just those you want, in the order you want.

Practical Learning: Performing Random Data Entry



To perform data entry, type and execute the following statement:
USE WorldStatistics;
GO
INSERT Countries([Country Name],Capital,[Internet Code],Population,Area)
VALUES('China', 'Beijing', 'cn', 1313973713, 9596960)
GO


Press F5 to execute the statement
To perform other entries, type the following statement:
USE WorldStatistics;
GO
INSERT Countries(Capital, [Internet Code], [Country Name])
VALUES('Nouakchott', 'mr', 'Mauritania')
GO
INSERT Countries([Internet Code], Population, [Country Name])
VALUES('ro', 22303552, 'Romania')
GO
INSERT Countries(Area, [Country Name], Population)
VALUES(21040, 'El Salvador', 6822378)
GO
INSERT Countries(Capital, [Country Name])
VALUES('Phnom Penh', 'Cambodia')
GO


To execute the statement, press F5
Close the query window
When asked whether you want to save it, click No



source : http://blogs.msdn.com/sharepoint/default.aspx