Category: Script

select @@servernameASServerName

Select @@VERSIONAsSQLServerVerjion

go

selectserverproperty(‘Edition’)AsServerEdition

selectserverproperty(‘ProductLevel’)ServerProductLevel

selectserverproperty(‘BuildClrVersion’)BuildVersion

/*

Bulk insert And OPENROWEST

*/

http://msdn.microsoft.com/en-us/library/aa225968(v=sql.80).aspx

—————————*******************Bulk Insert******************—————————–

–Step 1 Create Table [CSVTest]

CREATE

TABLE [dbo].[CSVTest]

(

[ID] [int]

NULL,

[FirstName] [varchar]

(40)COLLATE Arabic_CI_AS NULL,

[LastName] [varchar]

(40)COLLATE Arabic_CI_AS NULL,

[nvarchar]

(50)COLLATE Arabic_CI_AS NULL

—Step 2 Create Text File on D:\ Location

—Step 3 Try Bulk Insert

go

BULK

INSERT

[CSVTest]

FROM

‘D:\TxtFile2.txt’

WITH

(

BATCHSIZE

= 2,—- Show The Rows Affected

FIRSTROW

= 2,—– Start the insert from Row number 2

FIELDTERMINATOR

=‘,’,

ROWTERMINATOR

=‘\n’,

LASTROW

= 4,—- End the insert when you Arraive to row number 4

MAXERRORS

= 1 — Number of errors After this Number The SQL not complete the insert the defult value is 10

)

GO

–Step No 4 Check the Data inserted

Select

*from [CSVTest]

Delete

from [CSVTest]

Drop

Table [CSVTest]

We can Convert row to column by Twoways (Select Case – Pivot) let’s see How can i make this

Way No One

—Create Table

create Table States
(
id int primary key identity,
City nvarchar (20),
PersonName nvarchar (20)
)

—-Insert Data

insert into States values (‘Egypt’,’MOstafa’)
insert into States values (‘Egypt’,’Sayed’)
insert into States values (‘Egypt’,’Refay’)
insert into States values (‘London’,’Jon’)
insert into States values (‘London’,’Better’)
insert into States values (‘London’,’Cristian’)

—–Convert row to column

SELECT dbo.States.id,
(CASE City WHEN ‘Egypt’ THEN
(PersonName )  ELSE ‘No Name’ END) AS Egypt
,(CASE City WHEN ‘London’ THEN
(PersonName)ELSE ‘No Name’END) AS London
from States

Way No Two

—Create Table

CREATE TABLE [dbo].[Visit_Count_City](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[city] [nvarchar](50) NULL,
[VisitCount] [bigint] NULL
)

—–Insert Data

INSERT INTO visit_count_city VALUES (‘Mostafa’,’Sudia Arabia’,2)
INSERT INTO visit_count_city VALUES (‘Mostafa’,’Egypt’,2)
INSERT INTO visit_count_city VALUES (‘Mostafa’,’India’,2)
INSERT INTO visit_count_city VALUES (‘syaed’,’Sudia Arabia’,2)
INSERT INTO visit_count_city VALUES (‘Ahmed’,’Quter’,2)
INSERT INTO visit_count_city VALUES (‘Ali’,’Egypt’,2)
INSERT INTO visit_count_city VALUES (‘Mohamed’,’India’,2)

—-Convert row to column

SELECT * FROM
( SELECT p.NAME,p.city,p.VisitCount FROM dbo.Visit_Count_City AS p)datatable
PIVOT
(
SUM (VisitCount)
FOR city IN
(Egypt,[Sudia Arabia],Quter,India))PivotTable
go