Skip to main content

SQL Server- Tables



CREATE TABLE

Description

The SQL Server (Transact-SQL) CREATE TABLE statement allows you to create and define a table.

Syntax

The syntax for the CREATE TABLE statement in SQL Server (Transact-SQL) is:
CREATE TABLE table_name
( 
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

Parameters or Arguments

table_name
The name of the table that you wish to create.
column1, column2
The columns that you wish to create in the table. Each column must have a datatype. The column should either be defined as NULL or NOT NULL and if this value is left blank, the database assumes NULL as the default.

Example

Let's look at an example of how to use the CREATE TABLE statement in SQL Server (Transact-SQL).
For example:
CREATE TABLE employees
( employee_id INT NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50),
  salary MONEY
);
This SQL Server CREATE TABLE example creates a table called employees which has 4 columns.
  • The first column is called employee which is created as an INT datatype and can not contain NULL values.
  • The second column is called last_name which is a VARCHAR datatype (50 maximum characters in length) and also can not contain NULL values.
  • The third column is called first_name which is a VARCHAR datatype but can contain NULL values.
  • The fourth column is called salary which is a MONEY datatype which can contain NULL values.
Now the only problem with this CREATE TABLE statement is that you have not defined a primary key for the table in SQL Server. We could modify this CREATE TABLE statement and define the employee_id as the primary key as follows:
CREATE TABLE employees
( employee_id INT PRIMARY KEY,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50),
  salary MONEY
);
By specifying the words, PRIMARY KEY, after the employee_id field, SQL Server will create employee_id as the primary key for the employees table.

SELECT INTO STATEMENT 

Description

The SQL Server (Transact-SQL) SELECT INTO statement is used to create a table from an existing table by copying the existing table's columns.
It is important to note that when creating a table in this way, the new table will be populated with the records from the existing table (based on the SELECT Statement).

Syntax

The syntax for the SELECT INTO statement in SQL Server (Transact-SQL) is:
SELECT expressions
INTO new_table
FROM tables
[WHERE conditions];

Parameters or Arguments

expressions
The columns or calculations that you wish to retrieve.
new_table
The new table to create with the selected expressions and their associated definitions (new_table must not exist).
tables
The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
WHERE conditions
Optional. The conditions that must be met for the records to be selected.

Note

  • When using the SELECT INTO statement in SQL Server, the new_table must not already exist. If it does already exist, the SELECT INTO statement will raise an error.

Example

Let's look at an example that shows how to use the SELECT INTO statement in SQL Server (Transact-SQL).
For example:
SELECT employee_id, last_name, first_name
INTO contacts
FROM employees
WHERE employee_id < 1000;

Comments

Post a Comment

Popular posts from this blog

Variables

Variables The main way to store information in the middle of a PHP program is by using a variable. Here are the most important things to know about variables in PHP. All variables in PHP are denoted with a leading dollar sign ($). The value of a variable is the value of its most recent assignment. Variables are assigned with the = operator, with the variable on the left-hand side and the expression to be evaluated on the right. Variables can, but do not need, to be declared before assignment. Variables in PHP do not have intrinsic types - a variable does not know in advance whether it will be used to store a number or a string of characters. Variables used before they are assigned have default values. PHP does a good job of automatically converting types from one to another when necessary. PHP variables are Perl-like. PHP has a total of eight data types which we use to construct our variables − Integers  − are whole numbers, without a decima...
Post a Comment
Read more

SQL Server - INTERSECT Operator

Description The SQL Server (Transact-SQL) INTERSECT operator is used to return the records that are in common between two SELECT statements or data sets. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results. It is the intersection of the two SELECT statements. Intersect Query Explanation:  The INTERSECT query will return the records in the blue shaded area. These are the records that exist in both Dataset1 and Dataset2. Each SELECT statement within the SQL Server INTERSECT must have the same number of columns in the result sets with similar data types. Syntax The syntax for the NTERSECT operator in SQL Server (Transact-SQL) is: SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions] INTERSECT SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions]; Parameters or Arguments expressions The columns or calculations that you wish to compare between the two SELECT ...
Post a Comment
Read more

About Application

The product is supposed to be an open source application, developed under the PRSC(Punjab Remote Sensing Centre)organization, for the usage of PUDA(Punjab Urban Planning and Development Authority ). It is An android based system implementing client-server model. The Po portal System provides simple mechanism for the Planning officer to share  truly verified report. The following are the main features that are included in the application are: Common platform support: Offers operating support for most of the known and commercial operating systems i.e. android operating system. Number of users being supported by the system: Though the number is precisely not mentioned but the system is able to support a large number of online users at a time. Search: search for the pending list of the cases assigned is generally by logging in to the application. Id system: provides each officer with a unique Id which is sent at the time of report submission. Verification  section: Verif...
Post a Comment
Read more