Monday, December 31, 2012

Difference between Truncate and Delete

i) Truncate an Delete both are used to delete data from the table.
ii) These both the command will only delete the data of the specified table, they cannot remove the whole table data along with its structure both the SQL statements are used to only delete the data from the table but they both differ from each other in many aspects like syntax, performance, resources uses etc.
So, first let’s take a look of both of these terms (Truncate and Delete),


Truncate
-> Truncate command in SQL removes all rows from a table without logging the individual row deletion in the transaction log.
-> Truncate statement having the sane functionality as the Delete command has that is it deletes the data from the table without modifying or deleting the structure of the table.
-> You can not use the Where Clause with this (Truncate) statement.

Syntax:
TRUNCATE TABLE [ { database_name.[ schema_name ]. | schema_name . } ] table_name Table_name : Is the name of the table to truncate or from which all rows are removed.
Simple it looks like below query.
TRUNCATE TABLE Employees
The above command will delete all data from the table Employees.




Delete
-> Delete command in SQL also removes all rows from a table with logging the individual row deletion in the transaction log.
-> You can use the Where Clause with this (Delete) statement.
-> For more focus please follow the link Click me

Syntax:
DELETE FROM TABLE_NAME[ { database_name.[ schema_name ]. | schema_name . } ] table_name Table_name : Is the name of the table to truncate or from which all rows are removed.
Simple it looks like below query.
DELETE FROM Employees
The above command will delete all data from the table Employees.
In case of delete statements you can limit your delete query using where clause to delete, only particular records that fulfills the condition of where clause will be deleted not the all records.
It looks like below query with where clause.
DELETE FROM Employees Where EmployeeID IN (1,2,3)


Happy Coding!!! 

Thursday, December 13, 2012

SQL SERVER – Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server.


These are the common questions, How to import CSV file into SQL Server? How to load CSV file into SQL Server Database Table? How to load comma delimited file into SQL Server? 
Let us see the solution here
CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.

1) Create a Table using the below script: (To Download Please Click Here)

USE [Experimental]
GO
/****** Object:  Table [dbo].[CSVTest]    Script Date: 12/13/2012 16:08:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CSVTest](
[ID] [int] NOT NULL,
[FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BirthDate] [smalldatetime] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF





2) Create CSV file in drive e: with name csvtest.txt with following content. The location of the file is E:\csvtest.txt (To Download Please Click Here)
1, Anup, Shah, 19880803
2, Mac, Macwan, 19880803
3, Ankit, Patel, 19880803
4, Devang, Shah, 19880803

3) Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted. 
BULK
INSERT CSVTestFROM 'E:\csvtest.txt' WITH
(
FIELDTERMINATOR ',',

ROWTERMINATOR '\n')
GO
--Check the content of the table.
SELECT FROM CSVTest
GO









Converting SQL Server Database to XML format


i. First Open Notepad and write below code till </root>:

<root>
<%begindetail%>
<%insert_data_here%>
<%enddetail%>
</root>

Now Save it as c:\sqlexamples\template.tpl

Next you open SQL Server Query Editor and type:

sp_makewebtask @outputfile = 'c:\myxmlfile.xml',
    @query = 'select * from sysobjects for xml auto',
    @templatefile = 'c:\sqlexamples\template.tpl'

The Result is a XML-File!

Happy Coding! ;)