SQL - Drop, Truncate and Delete

Yeah.. basic things but it might be asked on the interview for Application Consultant position at Barclays.

What is the difference between Drop, Truncate and Delete?
Drop is to remove the whole table including the structure.
Truncate is to clean the table, delete all the data within the table.
Delete is to delete a record from the table.

So delete from table and truncate is the same?
The answer is no.

Delete will lock the table, but truncate will not.
That's the main difference!

Inserting Large Amount of Data - Bulk Insert

In order to pass a large amount of data, in VB.NET we can use Bulk Insert, here's the article for more information on the sample too.
Bulk insert sample in vbs file, click here.

If you need to create an XML from the Dataset, then u can click here.

Here's a sample on reading XML format in stored procedure:

DECLARE @idoc int

DECLARE @doc varchar(1000);

Select @doc ='
<ROOT>
<Customer CustomerID="1" ContactName="John 1"/>
<Customer CustomerID="2" ContactName="John 2"/>
<Customer CustomerID="1" ContactName="John 3"/>
<Customer CustomerID="2" ContactName="John 4"/>
</ROOT>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT    *
FROM       OPENXML (@idoc, '/ROOT/Customer',1)
            WITH (CustomerID  varchar(10),
                  ContactName varchar(20))

EXEC sp_xml_removedocument @idoc

Please take note that this is not reading from the physical XML file, this can only be used for reading table field with the value of XML formatted text.
More info on feeding XML to Stored Procedure, click here.

This is a sample of reading physical file from the stored procedure, but I have not tested it as I do not have this permission in my office development environment.
EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.
I guess this action requires sysadmin rights.

declare @objFSys int
declare @objFile int
declare @blnEndOfFile int
declare @strLine varchar(4000)

exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out

-- Change the file path to the one that is passed to your stored procedure
exec sp_OAMethod @objFSys, 'OpenTextFile', @objFile out, 'C:\test.txt', 1
exec sp_OAMethod @objFile, 'AtEndOfStream', @blnEndOfFile out
while @blnEndOfFile=0 begin
  exec sp_OAMethod @objFile, 'ReadLine', @strLine out
  -- Here you got one line from the file
  select @strLine
  exec sp_OAMethod @objFile, 'AtEndOfStream', @blnEndOfFile out
end
exec sp_OADestroy @objFile
exec sp_OADestroy @objFSys 

Running SQL in bat file

For MS SQL Server 2000, we can use osql.
click here and here for some samples.

For MS SQL Server 2005, we can use sqlcmd.
Click here for more info

For Oracle, we can use sqlplus.