TSQL: Inserting Data

Basic syntax:

INSERT [INTO] table_or_view [(column list)] data_values;

Values can be:

• Expressions
• Sub-queries, EXECUTE
• NULL (when allowed)
• DEFAULT

Samples:

insert into Table1(CountryID, Country)
select CountryID, Country
from --derived table
     (values('ph', 'Philippines'),
                 ('au', 'Australia'))
      as Countries(CountryID, Country)

--if 1 row can't be inserted successfully due to some constraint, everything will not be inserted as it's considered as the insert is considered 1 transaction.
insert into Table1(CountryID, Country, ModifiedDate)
values('ph', 'Philippines', default),
          ('au', 'Australia', default)

--if all columns have default values configured
insert into Table2
default values;

-- insert data using the results from an EXECUTE statement
-- EXECUTE statement output columns must match the column list for the insert
insert into Table(col1, col2)
exec dbo.usp_GetList @CountryID = 'ph'