Truncation Issue with Importing Text File to SQL Server (“Text was truncated or…”)
Posted: August 17, 2017 Filed under: Uncategorized Leave a commentYou can use right click your database name >>Tasks>>Import data… to launch SQL Server Import and Export Wizard >>click on Next>> choose data source >>pick Flat File Source >> browse to the location of your text file>> there are many settings you can play with and I am describing them here. You can go with default for most settings and give it a try.
Sometimes you may run into an annoying truncation error:
“Data conversion failed. The data conversion for column “xxxxxx” returned status value 4 and status text “Text was truncated or one or more characters had no match in the target code page “
This is
One or more columns in your text file have larger column sixe than the target table. You can follow the error message to increase your target table column size. The default for all columns are 50.
If you load your text file into a staging table, you can quickly change the default size to a larger one to finish up the loading process.
…. pick Flat File Source >> Choose a data source : browse to the location of your text file >> click on Advanced. Choose the first column and then hold down the shift key to select all columns you want to change size >> Change the Outputcolumnwidth size from 50 to a larger number, for example 100 or 2000. All column sizes will be changed to the new size.
Continue with the process and the truncation error should be fixed.
Another way to import text file is a method that I prefer to use.
Use bcp to import text file data with a format file into staging table. First to create a target table with proper column size and run bcp to create a format file and run bcp command with the format file to load the text file very quickly. The target table columns should have big enough size to receive the text file data to avoid truncation error
Relational Division Sample Query
Posted: August 16, 2017 Filed under: Uncategorized Leave a comment
CREATE TABLE tbl_Division(
DivisionID INT NOT NULL PRIMARY KEY
,DivisionName VARCHAR(100) NOT NULL
);
INSERT INTO tbl_Division(DivisionID,DivisionName)
VALUES (1,'Division A'), (2,'Division B'), (3,'Division C');
CREATE TABLE tbl_Region(
RegionID INT NOT NULL PRIMARY KEY
,RegionName VARCHAR(100) NOT NULL
);
INSERT INTO tbl_Region(RegionID,RegionName)
VALUES (1,'North America'),(2,'South America'),(3,'Europe');
CREATE TABLE tbl_DivisionRegion(
DivisionRegionID INT NOT NULL PRIMARY KEY
,DivisionID INT NOT NULL
,RegionID INT NOT NULL
);
ALTER TABLE tbl_DivisionRegion
ADD FOREIGN KEY (DivisionID) REFERENCES tbl_Division(DivisionID);
ALTER TABLE tbl_DivisionRegion
ADD FOREIGN KEY (RegionID) REFERENCES tbl_Region(RegionID);
INSERT INTO tbl_DivisionRegion(DivisionRegionID,DivisionID,RegionID)
VALUES (1,1,1),(2,1,2),(3,2,3), (4,3,1),(5,3,3);
--Option 1 to use EXISTS
--Question 1
SELECT d.DivisionName, r.RegionName
FROM tbl_Division d
LEFT JOIN tbl_DivisionRegion dr ON d.DivisionID = dr.DivisionID
LEFT JOIN tbl_Region r ON dr.RegionID = r.RegionID
WHERE
Exists (Select 1 from tbl_DivisionRegion dr2 JOIN tbl_Region r ON dr2.RegionID = r.RegionID where d.DivisionID=dr2.DivisionID and r.RegionName ='North America' )
AND
Exists (Select 1 from tbl_DivisionRegion dr2 JOIN tbl_Region r ON dr2.RegionID = r.RegionID where d.DivisionID=dr2.DivisionID and r.RegionName ='South America' )
ORDER BY dr.DivisionID, dr.RegionID
/*
DivisionName RegionName
Division A North America
Division A South America
*/
--Question 2
SELECT d.DivisionName, r.RegionName
FROM tbl_Division d
LEFT JOIN tbl_DivisionRegion dr ON d.DivisionID = dr.DivisionID
LEFT JOIN tbl_Region r ON dr.RegionID = r.RegionID
WHERE
Exists (Select 1 from tbl_DivisionRegion dr2 JOIN tbl_Region r ON dr2.RegionID = r.RegionID where d.DivisionID=dr2.DivisionID and r.RegionName ='North America' )
AND
Exists (Select 1 from tbl_DivisionRegion dr2 JOIN tbl_Region r ON dr2.RegionID = r.RegionID where d.DivisionID=dr2.DivisionID and r.RegionName ='Europe' )
ORDER BY dr.DivisionID, dr.RegionID
/*
DivisionName RegionName
Division C North America
Division C Europe
*/
--Question 3
SELECT d.DivisionName, r.RegionName
FROM tbl_Division d
LEFT JOIN tbl_DivisionRegion dr ON d.DivisionID = dr.DivisionID
LEFT JOIN tbl_Region r ON dr.RegionID = r.RegionID
WHERE
Exists (Select 1 from tbl_DivisionRegion dr2 JOIN tbl_Region r ON dr2.RegionID = r.RegionID where d.DivisionID=dr2.DivisionID and r.RegionName ='North America' )
AND
Exists (Select 1 from tbl_DivisionRegion dr2 JOIN tbl_Region r ON dr2.RegionID = r.RegionID where d.DivisionID=dr2.DivisionID and r.RegionName ='South America' )
AND
Exists (Select 1 from tbl_DivisionRegion dr2 JOIN tbl_Region r ON dr2.RegionID = r.RegionID where d.DivisionID=dr2.DivisionID and r.RegionName ='Europe' )
ORDER BY dr.DivisionID, dr.RegionID
/*
DivisionName RegionName
*/
---Option 2
--Pass your region checklist
declare @RegionName1 varchar(50)='North America'
declare @RegionName2 varchar(50)= 'South America' -- NULL
declare @RegionName3 varchar(50)= NULL --'Europe'
;with checklist as (
Select RegionName from (values (@RegionName1),(@RegionName2) ,(@RegionName3)) d(RegionName )
)
,mycteSrc as (
SELECT d.DivisionName, d.divisionID, r.RegionID,r.RegionName
FROM tbl_Division d
LEFT JOIN tbl_DivisionRegion dr ON d.DivisionID = dr.DivisionID
LEFT JOIN tbl_Region r ON dr.RegionID = r.RegionID
)
,mycteProcessing as (
SELECT src.DivisionName, CASE WHEN COUNT(src.RegionName) > (SELECT COUNT(RegionName) FROM checklist ) AND COUNT(ckl.RegionName) = (SELECT COUNT(RegionName)FROM checklist )
THEN 'match with extra'
WHEN COUNT(src.RegionName) = (SELECT COUNT(RegionName) FROM checklist) AND COUNT(ckl.RegionName) = (SELECT COUNT(RegionName) FROM checklist)
THEN 'exactly match'
WHEN MIN(ckl.RegionName) IS NULL
THEN 'none'
ELSE 'some' END AS location_status
FROM mycteSrc AS src LEFT JOIN checklist AS ckl ON src.RegionName = ckl.RegionName
GROUP BY src.DivisionName )
Select a.DivisionName,a.RegionName
from mycteSrc a join mycteProcessing m
on a.DivisionName =m.DivisionName
Where m.location_status='exactly match'
-- option 3
;with checklist as (
Select @RegionName1 as RegionName
Union all
Select @RegionName2 as RegionName
Union all
Select @RegionName3 as RegionName
)
,mycteSrc as (
SELECT d.DivisionName, d.divisionID, r.RegionID,r.RegionName
FROM tbl_Division d
LEFT JOIN tbl_DivisionRegion dr ON d.DivisionID = dr.DivisionID
LEFT JOIN tbl_Region r ON dr.RegionID = r.RegionID
)
SELECT src.DivisionName
FROM mycteSrc AS src LEFT JOIN checklist AS checklist
ON src.RegionName = checklist.RegionName
GROUP BY src.DivisionName
Having( COUNT(src.RegionName) = (SELECT COUNT(RegionName) FROM checklist)
AND COUNT(checklist.RegionName) = (SELECT COUNT(RegionName) FROM checklist)
)
drop table tbl_DivisionRegion,tbl_Division, tbl_Region
--You can search the term Relational division to learn more on this topic
https://forums.asp.net/t/2127017.aspx?MS+SQL+Query+Exclusive+Where
Get Next Sunday With Different DATEFIRST Setting
Posted: August 11, 2017 Filed under: Uncategorized Leave a commentSET DATEFIRST 1; --SET DATEFIRST 7; Declare @TimeByDay Date Set @TimeByDay = '2017-11-12' --returns next Sunday in the week with any DATEFIRST value Select DATEADD(dd, 6-(((DATEPART(dw,@TimeByDay)+@@DATEFIRST) % 7)+5) % 7 , @TimeByDay) --not working for DATEFIRST<>1 select DATEADD(dd,-(DATEPART(dw, @TimeByDay)-7), @TimeByDay) as WeekEndDate --US default SET DATEFIRST 7;
A Recursive Sample
Posted: August 9, 2017 Filed under: Uncategorized Leave a commentcreate table Category (PKey int, Description varchar(20), ParentCategoryKey int) Insert into Category values(1,'Car',null) ,(2,'Truck',null) ,(3,'Ford',1) ,(4,'Mustang',3) ,(5,'Lexus',1) ,(6,'pickup',2) ;with mycte as ( Select PKey,Description, PKey as PKey2, ParentCategoryKey , Cast(Cast(pkey as varchar(10))+ '-' as varchar(200)) AS pkeys from Category WHERE ParentCategoryKey is null Union all Select c.PKey,c.Description , PKey2 , c.ParentCategoryKey, Cast(m.pkeys+ Cast(c.pkey as varchar(10))+'-' as varchar(200)) from mycte m join Category c on c.ParentCategoryKey=m.pkey) Select PKey,Description, ParentCategoryKey, pkeys from mycte WHERE '-'+( select pkeys from mycte where Description='Mustang') like '%-'+Cast(pkey as varchar(10)) +'-%' drop table Category
Split and Dynamic Pivot with Case
Posted: August 4, 2017 Filed under: Uncategorized Leave a comment
create table test ( strCol varchar(2000))
insert into test values( '|Xyz,Type1,03/01/2018 10:00 PM,L1,P,-1|Abc,Type2,03/01/2018 10:00 PM,L1,A,1|Mno,NA,03/01/2018 10:00 PM,L2,P,-1')
,( '|Xyz2,Type1,03/01/2018 10:00 PM,L1,P,-1|Abc2,Type2,03/01/2018 10:00 PM,L1,A,1|Mno2,NA,03/01/2018 10:00 PM,L2,P,-1')
--===== Create number table on-the-fly
;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)
--load number table
Select * into NumsTable
from Nums
;with mycte as (
select strCol, n,
substring(strCol, n, charindex('|', strCol + '|', n) - n) splitData
from test
cross apply NumsTable
Where n <= len(strCol) AND substring('|' + strCol, n, 1) = '|'
)
--Load data into temp table 1
Select IDENTITY(int, 1,1) id, *
into mytemp1
from mycte
;with mycte1 as (
select id, splitData, m.n
, row_number()Over(partition by id Order by id,m.n) rn2
, substring(splitData, Nums.n, charindex(',', splitData + ',', Nums.n) - Nums.n) splitData2
from mytemp1 m
cross apply NumsTable nums
Where Nums.n <= len(splitData) AND substring(',' + splitData, Nums.n, 1) = ','
)
--Load data into temp table 2
Select * into mytemp2
from mycte1
--dynamic pivot with CASE
Declare @sql nvarchar(max);
Declare @ColumnHeaders VARCHAR(MAX)
SET @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + 'Max(CASE WHEN rn2=' + cast(rn2 as varchar(5))
+ ' THEN splitData2 else null end ) as ' + quotename(rn2,'[') + char(10)+char(13)
FROM mytemp2
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
--print @ColumnHeaders
set @sql = N'SELECT ' + @ColumnHeaders + ' FROM mytemp2 group by id ';
--print @sql
exec(@sql);
--clean up
drop table mytemp2, mytemp1,NumsTable
drop table test
--dynamic pivot query
create table test ( strCol varchar(2000))
insert into test values( '|Xyz,Type1,03/01/2018 10:00 PM,L1,P,-1|Abc,Type2,03/01/2018 10:00 PM,L1,A,1|Mno,NA,03/01/2018 10:00 PM,L2,P,-1')
,( '|Xyz2,Type1,03/01/2018 10:00 PM,L1,P,-1|Abc2,Type2,03/01/2018 10:00 PM,L1,A,1|Mno2,NA,03/01/2018 10:00 PM,L2,P,-1')
--===== Create number table on-the-fly
;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)
Select * into NumsTable
from Nums
;with mycte as (
select strCol, n,row_number()Over( Order by strCol) rn1,
substring(strCol, n, charindex('|', strCol + '|', n) - n) splitRole
from test
cross apply NumsTable
Where n <= len(strCol) AND substring('|' + strCol, n, 1) = '|'
)
Select IDENTITY(int, 1,1) id, *
into mytemp1
from mycte
;with mycte1 as (
select id, splitRole,rn1, m.n, row_number()Over(partition by id,rn1 Order by id,m.n) rn2, substring(splitRole, Nums.n, charindex(',', splitRole + ',', Nums.n) - Nums.n) splitRole2
from mytemp1 m
cross apply NumsTable nums
Where Nums.n <= len(splitRole) AND substring(',' + splitRole, Nums.n, 1) = ','
)
Select * into mytemp2
from mycte1
--dynamic
Declare @sql nvarchar(max);
Declare @cols varchar(max);
Declare @cols2 varchar(max);
set @cols = STUFF((SELECT ',max(' + QUOTENAME(rn2) +') as ' +QUOTENAME(rn2)
FROM mytemp2
group by rn2
order by rn2
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') ;
set @cols2 = STUFF((SELECT ',' + QUOTENAME(rn2)
FROM mytemp2
group by rn2
order by rn2
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') ;
set @sql = N'SELECT id,' + @cols + '
FROM mytemp2
PIVOT (MAX(splitRole2) FOR rn2 IN (' + @cols2 + ')) p group by id ';
print @sql
exec(@sql);
--select * from mytemp1
--select * from mytemp2
drop table mytemp2, mytemp1,NumsTable
drop table test
Export Stored Procedure Result with OPENQUERY
Posted: August 3, 2017 Filed under: Uncategorized Leave a comment
-- EXEC sp_serveroption 'myserver\Instancename', 'DATA ACCESS', TRUE
SELECT *
INTO mytmpTable
FROM OPENQUERY("myserver\Instancename", 'EXEC mydbname.[dbo].[usp_name_here] 2') -- with parameter value 2
select * from mytmpTable
drop table mytmpTable
You can use INSERT INTO atable EXECUTE sp syntax to put result into an existing table.
if object_id('datatemp','U') is not null
drop table datatemp
--create a temp table base Stored procedure resultset
create table datatemp (id int, col varchar(50))
---insert result from stored procedure
INSERT INTO datatemp(id, col)
EXECUTE yourspname @parameter1
--clean up
Drop table datatemp
Jingyang Li 