BCP in and out commands

EXPORT TO DAT FILE

declare @sql varchar(8000)

select @sql = 'bcp database..tablename out c:\table.dat -T -c'

print @sql

exec master..xp_cmdshell @sql

IMPORT FROM DAT FILE

declare @sql varchar(8000)

select @sql = 'bcp database..tablename in c:\table.dat -T -c'

print @sql

exec master..xp_cmdshell @sql

Using FOR XML PATH to covert rows to columns with comma concatenation

Createtable Table1( rowid int, value varchar(50))
insertinto Table1 values(1,'A')
insert into Table1 values(1,'B')
insert into Table1 values(1,'C')
insert into Table1 values(2,'D')
insert into Table1 values(2,'E')
insert into Table1 values(2,'F')
insert into Table1 values(2,'A')

SELECT DISTINCT

rowid,

STUFF(

(SELECT ',' + SubTable.value

FROM Table1 AS SubTable

WHERE SubTable.rowid = Table1.rowid

FOR XML PATH('')), 1, 1, '') AS ConcateValues

FROM Table1

ORDER BY rowid

--Results

rowid ConcateValues
1 A,B,C
2 D,E,F,A

TSQL to reconsile/exclude the cancelled claims based on quanity

create table ##test
(ndc  varchar(10),
nabp varchar(10),
utilqty int,
rowind char(1),
rownum int )

insert into ##test values(0,0,100,null,1)
insert into ##test values(1,1,100,null,2)
insert into ##test values(1,1,-100,null,3)
insert into ##test values(2,2,200,null,4)
insert into ##test values(2,2,200,null,5)
insert into ##test values(2,2,-200,null,6)
insert into ##test values(3,3,-300,null,7)
insert into ##test values(3,3,-300,null,8)
insert into ##test values(3,3,-300,null,9)
insert into ##test values(3,3,300,null,10)
insert into ##test values(3,3,300,null,10)
insert into ##test values(4,4,-400,null,11)
insert into ##test values(4,4,-400,null,12)
insert into ##test values(4,4,-400,null,13)
insert into ##test values(4,4,300,null,14)
insert into ##test values(5,5,300,null,15)
insert into ##test values(5,5,300,null,16)
insert into ##test values(5,5,300,null,17)
insert into ##test values(5,5,-300,null,18)
insert into ##test values(5,5,-300,null,19)


select ndc,nabp,abs(utilQty) absutilqty,COUNT(*) noofrows,
SUM(
                 CASE
                       WHEN utilQty <0
                       THEN  1
                       ELSE 0
                 END
           )                            AS NumberOfNeg
      ,     SUM(
                 CASE
                       WHEN utilQty <0
                       THEN  0
                       ELSE 1
                 END
           )                            AS NumberOfPos
      ,     SUM(utilQty)           AS Total_utilQty
      into #dataset1
      from ##test
      group by ndc,nabp,abs(utilQty)
     
      HAVING           COUNT(*)   > 1
      AND         SUM(
                 CASE
                       WHEN utilQty <0
                       THEN  1
                       ELSE 0
                 END
                 )          > 0
      AND         SUM(
                 CASE
                       WHEN utilQty <0
                      THEN  0
                       ELSE 1
                 END  
                 )          > 0
                
                 select * from #dataset1
                
                 select * from ##test
                
update ##test
set rowind='Y'
from ##test a  innerjoin #dataset1 b on
a.ndc=b.ndc and
a.nabp=b.nabp and absutilqty=abs(a.utilqty)
and b.Total_utilQty=0;


select a.ndc,a.nabp,a.utilqty,a.rownum into #POSUTI from (
select ndc,nabp,utilqty,rownum ,
row_number() over(PARTITIONby  ndc,nabp,utilqty order by rownum ) as rank
from ##test) a inner join #dataset1b on

a.ndc=b.ndc and a.nabp=b.nabp and abs(a.utilqty)=absutilqty
and b.Total_utilQty>0 --and a.utilqty<0

and a.rank<=b.NumberOfNeg

select * from #POSUTI

select a.ndc,a.nabp,a.utilqty,a.rownum  into #NEGUTI from (
select ndc,nabp,utilqty,rownum ,
row_number() over(PARTITIONby  ndc,nabp,utilqty order by rownum ) as rank
from ##test) a inner join #dataset1b on

a.ndc=b.ndc and a.nabp=b.nabp and abs(a.utilqty)=absutilqty
and b.Total_utilQty<0 --and a.utilqty<0

and a.rank<=b.NumberOfPos

select * from #NEGUTI

update ##test
set rowind='Y'
from ##test a  innerjoin #POSUTI b on
a.ndc=b.ndc and
a.nabp=b.nabp and a.utilqty=a.utilqty
and a.rownum=b.rownum

update ##test
set rowind='Y'
from ##test a  innerjoin #NEGUTI b on
a.ndc=b.ndc and
a.nabp=b.nabp and a.utilqty=a.utilqty
and a.rownum=b.rownum

Reasons-JOIN IN TSQL IS SLOW

If your JOIN in TSQL is slow, it probably has something to do with:

1. inappropriate or missing indexes
2. outdated statistics
3. non-SARGable arguments in the WHERE clause
4. incompatible datatypes in the ON clause of the JOIN, forcing the engine to do an implicit conversion, resulting in indexes not being used