SQL Server method to insert data interview questions
SQL Server method to insert data - Nov 20, 2008 at 18:00 PM
by Rajmeet Ghai
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.
<<Previous
Next>>
|