insert into select from

Another method to insert data record into a table in addition on previous post,

We still use table ProductList.


select * from ProductList

If results:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'ProductList'.

Create table ProductList first, use query below.

CREATE TABLE ProductList
(
    ProductId nvarchar(6),
    ProductName nvarchar(50),
    UnitPrice float,
    Quantity int
)

insert into ProductList select 'CPU001','CPU','300',15

select * from ProductList

#Results:
ProductId    ProductName    UnitPrice    Quantity
CPU001       CPU            300          15

sql insert into

On SQL Server, to add data record into a table use INSERT INTO Statement.

INSERT INTO (FieldName1,FieldName2,FieldName3, ...)TableName VALUES (value1, value2, value3, ...)

Example 1:
We will create a table first, its name ProductList.

--Create Table ProductList
CREATE TABLE ProductList
(
    ProductId nvarchar(6),
    ProductName nvarchar(50),
    UnitPrice float,
    Quantity int
)

After ProductList created, remark above query, cause table name is unique on a database.

select * from ProductList

#Results:
ProductId    ProductName    UnitPrice    Quantity

Empty, cause we not yet inserted  anyhing into ProductList, now insert with statement below

insert into ProductList (ProductId,ProductName,UnitPrice,Quantity) values ('LCD001','LCD Monitor','150',5)

select * from ProductList

#Results:
ProductId    ProductName    UnitPrice    Quantity
LCD001       LCD Monitor    150          5

Example 2:
We will insert 1 record againt into ProductList, in some fields only.

insert into ProductList (ProductId,ProductName) values ('LCD002','LCD Monitor') 

select * from ProductList
#Results:
ProductId    ProductName    UnitPrice    Quantity
LCD001       LCD Monitor    150          5
LCD002       LCD Monitor    NULL         NULL

sql create table

On SQL Server, to create table use statement as follows:

CREATE TABLE TableName
(
    FieldName1 DataType,
    FieldName2 DataType,
    FieldName3 DataType,
    ...
)

Example 1:
Create table with name EmployeeList.

CREATE TABLE EmployeeList
(
    EmployeeId int,
    EmployeeName nvarchar(50),
    EmployeeAddr nvarchar(50),
    EmployeeDept nvarchar(30)
)

After run on query analyzer, here are
#Results:
Command(s) completed successfully

Hereafter, copy and paste on query analyzer script below:

Select * from EmployeeList

#Results:
EmployeeId    EmployeeName    EmployeeAddr    EmployeeDept

This Table is empty.

We can be fill EmployeeList table with data,  use INSERT INTO SQL SERVER Statement.

insert into EmployeeList (EmployeeId,EmployeeName,EmployeeAddr,EmployeeDept) values ('11111','John Nuxer','Jl Raya Bekasi Km 28','MIS Dept')

Yes, now 1 data inserted to EmployeeList table.
See with run this query

Select * from EmployeeList

#Results:
EmployeeId    EmployeeName    EmployeeAddr            EmployeeDept
11111         John Nuxer      Jl Raya Bekasi Km 28    MIS Dept

How to create table with another way?  or Is there any way auto create table on SQL Server?
Yes, here.