Bulk Insert Fixed Length Text File

  

 use myDb;

--create destination table structure 
create table bcpTest
(
mob  char(3),
acct  char(5),
mmid  char(4) 
)


 -- Generate fmt format file 
 DECLARE @cmd VARCHAR(4000) 
set @cmd ='bcp  myDb.dbo.bcpTest format nul -c -f E:\DATA\myFormatFiletest.fmt -t ""  -T -S'+ @@servername;;
exec master..xp_cmdshell @cmd


--BULK Insert
BULK INSERT bcpTest
FROM 'E:\DATA\user_data.txt'
WITH
(
FORMATFILE ='E:\DATA\myFormatFiletest.fmt',
ERRORFILE = 'E:\DATA\ERROR_FILE_UD3.log'
)
select * from bcpTest
 

 Drop table bcpTest




 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8d93ac67-7499-4614-b4ea-3c81daf64d0f/bcp-width-range?forum=transactsql

Advertisements

Database is in Single_User Mode and How to Change to Multi_User Mode

For unknown reason (due to operation failure), a database was stuck in Single_User mode.

Here are a few steps to kill the troubled session and change the database back to multi_user mode.

–1.Find the session_id to kill
Select request_session_id From sys.dm_tran_locks Where resource_database_id=DB_ID(‘DBName_In_Single_User_Mode’);

–2: Kill the found session_id

Kill theseessionid ;

–3. set the database to multi_user mode
USE [master]

ALTER DATABASE [DBName_In_Single_User_Mode] SET MULTI_USER WITH ROLLBACK IMMEDIATE;


Find All Related (directly or indirectly) IDs

  

DECLARE @Value INT
SET @Value = 6

CREATE TABLE People (ID INT PRIMARY KEY , name NVARCHAR(50), motherID INT, fatherID INT, sex NVARCHAR(50));

INSERT People VALUES(1, 'A', NULL, NULL, 'male');
INSERT People VALUES(2, 'B', NULL, NULL, 'female');
INSERT People VALUES(3, 'C', 1, 2, 'male');
INSERT People VALUES(4, 'X', NULL, NULL, 'male');
INSERT People VALUES(5, 'Y', NULL, NULL, 'female');
INSERT People VALUES(6, 'Z', 5, 4, 'female');
INSERT People VALUES(7, 'T', NULL, NULL, 'female');

CREATE TABLE marriages (
HusbandID INT REFERENCES People(id),
WifeID INT REFERENCES People(id)
)
INSERT marriages VALUES (1,2);
INSERT marriages VALUES (4,5);
INSERT marriages VALUES (1,5);
INSERT marriages VALUES (3,6);

--create source relation dataset
;with dataSource as (
select ID id, null relatedid from People
union
select ID, motherID relatedid from People
union
select ID, fatherID relatedid from People
union
Select HusbandID, WifeId relatedid  from marriages

)

, LeftIDs AS
(
  SELECT id, relatedid  FROM dataSource
  WHERE relatedid = @Value
    UNION ALL
  SELECT a.id, a.relatedid FROM dataSource a
  JOIN LeftIDs b ON a.id = b.relatedid
)
, RightIDs AS
(
  SELECT id, relatedid FROM dataSource
  WHERE id = @Value
    UNION ALL
  SELECT a.id, a.relatedid FROM dataSource a
  JOIN LeftIDs b ON ( b.relatedid = a.id or a.relatedid = b.id)
)
, RightIDs2 AS
(
  SELECT id, relatedid FROM dataSource
  WHERE id = @Value
    UNION ALL
  SELECT a.id, a.relatedid FROM dataSource a
  JOIN RightIDs b ON ( b.relatedid = a.id or a.relatedid = b.id)

)
 , RightIDs3 AS
(
  SELECT id, relatedid FROM dataSource
  WHERE id  = @Value
    UNION ALL
  SELECT a.id, a.relatedid FROM dataSource a
  JOIN RightIDs2 b ON ( b.relatedid = a.id or a.relatedid = b.id)
)
 , RightIDs4 AS
(
  SELECT id, relatedid FROM dataSource
  WHERE id  = @Value
    UNION ALL
  SELECT a.id, a.relatedid FROM dataSource a
  JOIN RightIDs3 b ON ( b.relatedid = a.id or a.relatedid = b.id)
)

,finalcte as
(
  SELECT id  FROM LeftIDs
  UNION
  SELECT relatedid  FROM LeftIDs
  UNION
  SELECT id  FROM RightIDs
  UNION
  SELECT relatedid
  FROM RightIDs
  UNION
  SELECT id FROM RightIDs2
  UNION
  SELECT relatedid  FROM RightIDs2
  UNION
  SELECT id  FROM RightIDs3
  UNION
  SELECT relatedid  FROM RightIDs3
  UNION
  SELECT id  FROM RightIDs4
  UNION
  SELECT relatedid FROM RightIDs4

)

select id from finalcte
WHERE id is not null

---brutal force solution
--declare @id int=1
 
 
;with mycte as (
select ID, null relatedID from People
union
select ID, motherID relatedID from People
union
select ID, fatherID relatedID from People
union
Select HusbandID, WifeId relatedID  from marriages
)
 
 
,mycte2 as (
SELECT id  FROM mycte
where id in (select id from mycte where id=@id)
or relatedID in (select relatedID from mycte where id=@id)
union
SELECT   relatedID FROM mycte
where id in (select id from mycte where id=@id)
or relatedID in (select relatedID from mycte where id=@id)
)
 
 
select id from mycte 
where  id in (select id from mycte2 ) or relatedID in (select id from mycte2)
union
select relatedID from mycte 
where ( id in (select id from mycte2 )or relatedID in (select id from mycte2))
and relatedID is not null
 
 
 
drop table marriages, People




 

https://stackoverflow.com/questions/18601791/need-query-to-select-direct-and-indirect-customerid-IDses


Value Pair Query

DECLARE @TypeMax TABLE
(
MaxCol nvarchar(max)
)

INSERT INTO @TypeMax
VALUES (‘{“ID”:”075405435453.doc”,”Name”:”Scenario1″,”Date”:”2016-12-19 23:01:03″,”Type”:”SL”}’)

, (‘{“ID”:”55453450480003.wav”,”Name”:”Change43″,”Date”:”2016-12-13 21:03:23″,”Type”:”AL”}’)

–SELECT * FROM @TypeMax

–SQL Server 2016 or 2017
SELECT isJSON(MaxCol),
JSON_VALUE(MaxCol, ‘$.ID’) Id,
JSON_VALUE(MaxCol, ‘$.Name’) Name,
JSON_VALUE(MaxCol, ‘$.Date’) [Date],
JSON_VALUE(MaxCol, ‘$.Type’) Type
FROM @TypeMax

–Before JSON functions were introduced to SQL SERVER
—Use XML shredding

;With mycte as (
SELECT Cast(N'<H><r ‘ + Replace(Replace(Replace(Replace(Replace( MaxCol , ‘{“‘,”), ‘}’,”), ‘”,”‘,'” ‘),'”:”‘,’=”‘) + ‘” /></H>’ ,'””‘,'”‘ ) as xml) AS [vals]
FROM @TypeMax
)

,mycte1 as (
SELECT ROW_NUMBER() OVER (ORDER BY S.a.value(‘count(.)’, ‘tinyint’)) rn,
S.a.value(‘@ID’, ‘varchar(50)’) as [ID],
S.a.value(‘@Name’, ‘varchar(50)’) as [Name],
S.a.value(‘@Date’, ‘varchar(50)’) as [Date],
S.a.value(‘@Type’, ‘varchar(50)’) as [Type]

FROM mycte d CROSS APPLY d.[vals].nodes(‘/H/r’) S(a) )

select ID,Name,[Date],[Type] from mycte1
/*
ID Name Date Type
075405435453.doc Scenario1 2016-12-19 23:01:03 SL
55453450480003.wav Change43 2016-12-13 21:03:23 AL
*/

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4f229634-d6bb-4096-809b-544895bb0529/sql-help-one-column-spilt-into-multiple-columns?forum=transactsql


Generating Sorted List Becomes Easy with SQL Server 2017’s New String Function: String_AGG

  

 create table #temp
(id int,Name1 varchar(30),
Name2 varchar(30),
Name3 varchar(30) 
)

insert into #temp
values(1,'Ashley','Zebo','Terry'),
(2,'John','Adam','Bert') 

;with mycte as 
(select * from #temp
cross apply(Values(Name1),(Name2),(Name3)) d(Names) 
)

SELECT id, String_AGG(Names,',') WITHIN GROUP ( ORDER BY Names)  AS Result
FROM mycte
Group by id

drop table #temp 

 

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql


Fill Date Range Gaps and Values

  

 create table #forum (Country varchar(20), City varchar (20) , Market varchar(10),  NDate date, NOpen int, NClose int)

Insert into #forum values 
('Japan','Tokyo','Fish', '2017-01-01', 100, 150),  ('Japan','Tokyo','Fish', '2017-02-01', 150, 180), ('Japan','Tokyo','Fish', '2017-04-01', 180, 160)
,('France','Paris','Coffee', '2017-01-01', 1100, 950), ('France','Paris','Coffee', '2017-02-01', 950, 1080), ('France','Paris','Coffee', '2017-06-01', 1080, 1260),
('France','Paris','Fruit', '2017-01-01', 100, 110), ('France','Paris','Fruit', '2017-03-01', 110, 130),
('Argentina','Buenos Aires','Coffee', '2017-01-01', 300, 250),('Argentina','Buenos Aires','Coffee', '2017-02-01', 250, 50),('Argentina','Buenos Aires','Coffee', '2017-03-01', 50, 450),
('Argentina','Buenos Aires','Coffee', '2017-04-01', 450, 160),('Argentina','Buenos Aires','Coffee', '2017-05-01', 160, 320),('Argentina','Buenos Aires','Coffee', '2017-06-01', 320, 200)

--Expected Result
create table #forumResult (Country varchar(20), City varchar (20) 
, Market varchar(10),  NDate date, NOpen int, NClose int)

Insert into #forumResult values 
('Japan','Tokyo','Fish', '2017-01-01', 100, 150),  ('Japan','Tokyo','Fish', '2017-02-01', 150, 180), ('Japan','Tokyo','Fish', '2017-03-01', 180, 180),
('Japan','Tokyo','Fish', '2017-04-01', 180, 160),
('France','Paris','Coffee', '2017-01-01', 1100, 950), ('France','Paris','Coffee', '2017-02-01', 950, 1080), ('France','Paris','Coffee', '2017-03-01', 1080, 1080),
('France','Paris','Coffee', '2017-04-01', 1080, 1080), ('France','Paris','Coffee', '2017-05-01', 1080, 1080),('France','Paris','Coffee', '2017-06-01', 1080, 1260),
('France','Paris','Fruit', '2017-01-01', 100, 110), ('France','Paris','Fruit', '2017-02-01', 110, 110), ('France','Paris','Fruit', '2017-03-01', 110, 130),

('Argentina','Buenos Aires','Coffee', '2017-01-01', 300, 250),('Argentina','Buenos Aires','Coffee', '2017-02-01', 250, 50),('Argentina','Buenos Aires','Coffee', '2017-03-01', 50, 450),
('Argentina','Buenos Aires','Coffee', '2017-04-01', 450, 160),('Argentina','Buenos Aires','Coffee', '2017-05-01', 160, 320),('Argentina','Buenos Aires','Coffee', '2017-06-01', 320, 200)

select * from #forumResult

declare @startdate date 
declare @enddate date   

Select @startdate = min(NDate),@enddate = max(NDate) from #forum 

--****  create a Number table
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y), 
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

 --Country, City, Market and Dates  
,myCountryCityMarket as
(
select Country, City, Market, dateadd(month,n-1,@startdate) dt from Nums 
Cross join (Select Distinct Country, City, Market From  #forum ) a    
)

,mycte2 as (
Select cck.Country,cck.City,cck.Market, 
f.NOpen,f.NClose,dt, f.NDate  

FROM myCountryCityMarket cck
LEFT JOIN    #forum f ON  f.NDate =cck.dt  
and f.Country=cck.Country and f.City =cck.City and f.Market= cck.Market  
WHERE cck.dt<=@enddate --last date
 )

 select Country,City,Market,dt NDate  
 , ISNULL(NOpen, NCloseFill) NOpen
 , ISNULL(NClose, NCloseFill) NClose  
 from mycte2 m
 Outer apply (select top 1 NClose from mycte2 m2 
WHERE m2.Country=m.Country and m2.City =m.City and m2.Market= m.Market
 and m2.dt<=m.dt and NClose is not null 
Order by dt DESC) d1(NCloseFill)
 Outer apply (select top 1 NClose from mycte2 m2 
WHERE m2.Country=m.Country and m2.City =m.City and m2.Market= m.Market
 and m2.dt>=m.dt and NClose is not null 
Order by dt DESC) d2(NCloseFill2)
WHERE NCloseFill2 is not null
Order by 1,3,4

drop table  #forumResult, #forum 

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e5a9bf94-8f89-414c-8c42-a0bada141971/filling-the-gap-in-the-calendar-having-open-and-close-value?forum=transactsql


XML Split and Pivot

  
 
create table rating_label
(
id_pk int,
label_message varchar(500)
)


insert into rating_label values(105,'Look and Feel');
insert into rating_label values(107,'Performance And Speed');
insert into rating_label values(106,'Ease of Use');
insert into rating_label values(109,'Good');
insert into rating_label values(108,'Performance');

create table query_info
(
cust_mob varchar(20),
rating_id varchar(200),
Rated_Date date default getdate()
)

INSERT query_info (cust_mob, rating_id, Rated_Date) VALUES (N'6556949494', N'109*1#108*1#107*3#106*0#105*4#', CAST(N'2018-02-15' AS Date))
 
--INSERT into query_info (cust_mob,rating_id, Rated_Date) VALUES (N'7756946507', N'109*4#108*0#107*5#106*1#105*5#', CAST(N'2018-02-12' AS Date))
--, (N'6565676879', N'109*3#108*4#107*2#106*3#105*4#', CAST(N'2018-02-12' AS Date))
--, (N'3434544657', N'109*2#108*1#107*0#106*2#105*0#', CAST(N'2018-02-12' AS Date))
--insert into query_info (cust_mob,rating_id)values('6556949494','109*1#108*1#107*3#106*0#105*4#'),('7556949486','109*1#108*2#107*2#106*0#105*0#'),('8547125934','109*5#108*0#107*4#106*0#105*4#')
;with mycte as (
select * ,  
CAST (N'<H><r>' +  REPLACE(rating_id,  '#', '</r><r>')+ '</r></H>' AS XML) vals
from query_info
WHERE Rated_Date='2018-02-15' )
, mycte1 as
 ( 
 SELECT  cust_mob,  S.a.value('.', 'VARCHAR(100)') AS splitVal1 
 FROM mycte d
 CROSS APPLY d.[vals].nodes('/H/r') S(a)
 )
 , mycte2 as
 ( 
Select *, CAST (N'<H><r>' + Replace( splitVal1, '*','</r><r>') + '</r></H>' AS XML) [vals] from mycte1
 )
 ,mycte3 as (

 Select DISTINCT cust_mob, 
 S.a.value('(/H/r)[1]', 'VARCHAR(100)') cat,  S.a.value('(/H/r)[2]', 'VARCHAR(100)') val  
 FROM mycte2 d   
 CROSS APPLY d.[vals].nodes('/H/r') S(a)  
 )

 Select rl.label_message, sum(Case when val=1 then 1 else 0 end) [1]
 , sum(Case when val=2 then 1 else 0 end) [2]
 , sum(Case when val=3 then 1 else 0 end) [3]
 , sum(Case when val=4 then 1 else 0 end) [4]
 , sum(Case when val=5 then 1 else 0 end) [5]
 from mycte3 m join rating_label rl on m.cat=rl.id_pk
 WHERE cat<>''
 Group by cat,rl.label_message
 Order by cat
  
 

 

drop table query_info , rating_label
 


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0e6ddccd-5e94-4478-bd4b-8ca7412cb915/split-row-query?forum=transactsql