Stuff

select cast(STUFF('154903',3,2,9)as int)
15903
select cast(STUFF('154903',3,2,'')as int)
1503

bluk insert

Create a table with the following structure

1.create table bulk_insert_test

2.(

3.    employee_id int identity(1,1),

4.    first_name varchar(30), 

5.    last_name varchar(30),

6.    address varchar(100)

7.)

Note that the table has two extra columns employee_id and address

Now this query will fail

1.BULK INSERT bulk_insert_test

2.   FROM 'g:\test.txt'

3.   WITH

4.     (

5.        FIELDTERMINATOR =',',

6.        ROWTERMINATOR = '\n'

7.      )

Becuase the table has four columns and text file has data for only two columns. In this case you can import data to specific columns using the following methods

1 Use a View

Create a view that has only required columns

1.create view vw_bulk_insert_test

2.as

3.select first_name,last_name from bulk_insert_test

 

 

Now use BULK INSERT using this view

1.BULK INSERT vw_bulk_insert_test

2.   FROM 'g:\test.txt'

3.   WITH

4.     (

5.        FIELDTERMINATOR =',',

6.        ROWTERMINATOR = '\n'

7.      )

Name it as format.txt Now use BULK INSERT using this format file

1.BULK INSERT bulk_insert_test

2.   FROM 'g:\test.txt'

3.with (formatfile = 'g:\format.txt')

3 Use OPENROWSET 

1.INSERT INTO bulk_insert_test(first_name,last_name)

2.SELECT 

3.    * 

4.FROM 

5.    OPENROWSET('Microsoft.Jet.OLEDB.4.0','text;HDR=NO;FMT=FixedLength;Database=g:\', test#txt)

As you see method 3 doesn't require any extra work if the number of columns are different

DROP CONSTRAINT

ALTER TABLE  [tblexternalload]

DROP CONSTRAINT [DF_tblexternalload_last_modifydate]

XML Insert with Merge

ALTER

Procedure [dbo].[MSP_InsertRevenueDetails]

(

@_forapptrans

varchar(max)

)

as

Begin

DECLARE

@MyXML XML

SET

@MyXML =@_forapptrans

IF

EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].#Tmp') AND type in (N'U'))

DROP TABLE [dbo].#Tmp

SELECT

B

.test.query('UploadId').value('.', 'int') as UploadId,

B

.test.query('Modalityid').value('.', 'int') as Modalityid,

B

.test.query('CenterId').value('.', 'int') as CenterId ,

B

.test.query('GrossAmount').value('.', 'decimal') as GrossAmount,

B

.test.query('NoofStudy').value('.', 'int') as NoofStudy,

B

.test.query('Collection').value('.', 'decimal') [Collection],

B

.test.query('RevenueDate').value ('.','datetime') RevenueDate,

B

.test.query('CreatedBy').value ('.','varchar(20)') CreatedBy

into

#tmp

FROM

@MyXML.nodes('BulkUpload/Revenue') AS B (test)

MERGE

tbl_InsertRevenueDetails as TARGET

USING

(

SELECT

UploadId

,

Modalityid

,

CenterId

,

GrossAmount

,

NoofStudy

,

[Collection]

,

RevenueDate

,

CreatedBy

FROM

#tmp

)

as New

ON (TARGET.Modalityid=new.Modalityid and Target.CenterId = New.CenterId and Target.RevenueDate=New.RevenueDate )

WHEN MATCHED

AND

( TARGET.UploadId<>new.UploadId

OR TARGET.GrossAmount<>new.GrossAmount

OR TARGET.NoofStudy<>new.NoofStudy

OR TARGET.[Collection]<>new.[Collection]

--OR TARGET.RevenueDate<>new.RevenueDate

OR TARGET.CreatedBy<>new.CreatedBy

)

THEN

UPDATE SET

TARGET.UploadId=new.UploadId,

TARGET.GrossAmount=new.GrossAmount,

TARGET.NoofStudy=new.NoofStudy,

TARGET.[Collection]=new.[Collection],

-- TARGET.RevenueDate=new.RevenueDate,

TARGET.CreatedBy=new.CreatedBy

WHEN NOT MATCHED BY TARGET

THEN INSERT

(

[UploadId]

,[Modalityid]

,[CenterId]

,[GrossAmount]

,[NoofStudy]

,[Collection]

,[RevenueDate]

,[CreatedBy]

,[CreatedDate]

,[Status]

)

Values

(

new.UploadId,

new

.Modalityid,

new

.CenterId,

new

.GrossAmount,

new

.NoofStudy,

new

.[Collection],

new

.RevenueDate,

new

.CreatedBy,

GETDATE(),

0

);

end

EXAMPLE :DATEADD,DATEDIFF,DATEPART

Dateadd Function

DATEADD (datepart, number, expression)

select GETDATE() as 'today',

 DATEADD(D, 1, GETDATE()) as 'day_d',

DATEADD(M, 1, GETDATE())as 'month_m',

DATEADD(YEAR, 1, GETDATE())as 'year_year',

DATEADD(MI, 2, GETDATE()) as 'minite_MI'

 

Output

today	day_d	month_m	year_year	minite_MI
2011-07-05 19:41:35.2	2011-07-06 19:41:35.2	2011-08-05 19:41:35.2	2012-07-05 19:41:35.2	2011-07-05 19:43:35.2

 

DATEDIFF Function

DATEDIFF (datepart, expression1, expression2)

select DATEDIFF (day,getdate(),GETDATE()+1) as 'DAY_DIFFERENCE'

Output

DAY_DIFFERENCE

1

 

Datepart Function

DATEPART (part_of_day, expression)

SELECT DATEPART(DAY,GETDATE()) as 'DAY',DATEPART(MONTH,GETDATE()) AS 'MONTH',DATEPART(YEAR,GETDATE())AS 'YEAR'

'DAY'	'MONTH'	'YEAR'
5	7	2011

 

--
--

Regards,

Basheer Ahamed

 

Example For Rank , Rank_Number,Dense_Rank

SELECT p.FirstName, p.LastName

    ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS 'Row Number'

    ,RANK() OVER (ORDER BY a.PostalCode) AS 'Rank'

    ,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS 'Dense Rank'

    ,NTILE(4) OVER (ORDER BY a.PostalCode) AS 'Quartile'

    ,s.SalesYTD, a.PostalCode

FROM Sales.SalesPerson s

    INNER JOIN Person.Person p

        ON s.BusinessEntityID = p.BusinessEntityID

    INNER JOIN Person.Address a

        ON a.AddressID = p.BusinessEntityID

WHERE TerritoryID IS NOT NULL

    AND SalesYTD <> 0;

Here is the result set.

FirstName	LastName	Row Number	Rank	Dense Rank	Quartile	SalesYTD	PostalCode
Michael	Blythe	1	1	1	1	4557045.0459	98027
Linda	Mitchell	2	1	1	1	5200475.2313	98027
Jillian	Carson	3	1	1	1	3857163.6332	98027
Garrett	Vargas	4	1	1	1	1764938.9859	98027
Tsvi	Reiter	5	1	1	2	2811012.7151	98027
Shu	Ito	6	6	2	2	3018725.4858	98055
José	Saraiva	7	6	2	2	3189356.2465	98055
David	Campbell	8	6	2	3	3587378.4257	98055
Tete	Mensa-Annan	9	6	2	3	1931620.1835	98055
Lynn	Tsoflias	10	6	2	3	1758385.926	98055
Rachel	Valdez	11	6	2	4	2241204.0424	98055
Jae	Pak	12	6	2	4	5015682.3752	98055
Ranjit	Varkey Chudukatil	13	6	2	4	3827950.238	98055

--
--

Regards,

Basheer Ahamed