Truncation Issue with Importing Text File to SQL Server (“Text was truncated or…”)

You 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

  

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

  
 
 SET 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

  
 
 create 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

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e263a3cb-af26-4a4a-991a-ffe855a7d4ae/query-tables-parent-key-and-ancestors?forum=transactsql


Split and Dynamic Pivot with Case

  

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

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8189de40-33f5-4814-a836-5911d435a99b/split-strings-into-a-table-without-loop-in-sql-server-2008r2?forum=transactsql


Export Stored Procedure Result with OPENQUERY

  
-- 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

 

Transfer Table Schema From one to dbo

 

  

declare @sql varchar(8000), @table varchar(1000), @oldschema varchar(1000), @newschema varchar(1000)

set @oldschema = 'IIS APPPOOL\User1'
set @newschema = 'dbo'

while exists(select * from sys.tables where schema_name(schema_id) = @oldschema)
begin
select @table = name from sys.tables 
where object_id in(select min(object_id) from sys.tables where schema_name(schema_id) = @oldschema)

set @sql = 'alter schema [' + @newschema + '] transfer [' + @oldschema + '].' + @table

exec(@sql)
end
 
 

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/dc3ec5b6-282c-4b6f-8161-ebb754bffc49/change-schema-sql-2016?forum=transactsql

Original Script from:
https://www.sqlservercentral.com/Forums/654301/change-old-schema-name-into-new-schema-name