SQL Server method to insert data

Brief about Insert Statement with an example.

Answer
INSERT: The Insert statement is used to insert values as rows in a table.

Syntax:
INSERT INTO Table_name values (value1, value2,…);
INSERT INTO Table_name (column1, colum2…) values (value1, value2,…);

Example
A table customer has fields customer id, customer name, customer salary. 
INSERT INTO customer (1,John,20000);

Brief about Select….into statement with an example.

Answer
Select into is used to create back up copies of tables. It selects data from one table and inserts into another.

Syntax:
Select * INTO dest_table_name FROM source_table_name

Example: Select data from customer table into customer_backup
Select * INTO customer_backup FROM customer;

It can also be used with WHERE clause for a condition.
Select * INTO customer_backup FROM customer WHERE cust_salary > 1000;

Brief about Bulk copy with an example.

Answer
Bulk copy utility of SQL allows data to be copied from one data file to another. The data is first exported from the source data file and then imported into the SQL server using the bcp command. It can also be used to transfer data from a table to a file.

Describe how bcp command prompt utility is used to import and export data.

Answer
The bcp utility is accessed from the command prompt.

Syntax:
bcp {dbtable | query} {in | out | queryout | format} datafile [-n native type] [-c character type] [-S server name] [-U username] [-P password] [-T trusted connection]

It starts with specifying the database or object name. In and out means whether the data needs to be copied into or out of the database. Hence in will be used for importing data while out will be used to export data. Remaining arguments are the basic command line arguments.

Example1: import data of Company database to the employee table
bcp Company.dbo.employee in c:\temp\employee.bcp -c -Sstevenw -Usa –P

Example2: Export data of Company database from the employee table
bcp Company.dbo.employee out c:\temp\employee.bcp

Describe how bulk insert statement is used to import data.

Answer
Bulk Insert is used to copy data from a file into a table or view in a format as specified by the user.

Syntax:
BULK INSERT
[ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]
FROM 'data_file'
[ WITH
(
[ [ , ] BATCHSIZE = batch_size ]
[ [ , ] CHECK_CONSTRAINTS ]
[ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ [ , ] DATAFILETYPE =
{ 'char' | 'native'| 'widechar' | 'widenative' } ]
[ [ , ] FIELDTERMINATOR = 'field_terminator' ]
[ [ , ] FIRSTROW = first_row ]
[ [ , ] FIRE_TRIGGERS ]
[ [ , ] FORMATFILE = 'format_file_path' ]
[ [ , ] KEEPIDENTITY ]
[ [ , ] KEEPNULLS ]
[ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]
[ [ , ] LASTROW = last_row ]
[ [ , ] MAXERRORS = max_errors ]
[ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
[ [ , ] ROWS_PER_BATCH = rows_per_batch ]
[ [ , ] ROWTERMINATOR = 'row_terminator' ]
[ [ , ] TABLOCK ]
[ [ , ] ERRORFILE = 'file_name' ]
)]

Example:
BULK INSERT target.txt FROM ‘C:\Dest.txt’ WITH ( FIELDTERMINATOR = ‘ ” , ” ‘)
The FIELDTERMINATOR argument allows you to specify the delimiter used to discern column values.