BCP with Fixed Width text file



DECLARE @sql VARCHAR(4000) 
SET @sql='bcp [mytest4].[dbo].[testbcpFixed] format nul -c  -f  C:\temp\myFormat.fmt -T -S'+ @@servername
exec master..xp_cmdshell @sql
--Remove "\r\n" with "" and "\t" with "" in your generated format file
GO 


--Run the following bcp to load your fixedwidth text file into your table
DECLARE @sql3 VARCHAR(4000) 
SET @sql3='bcp [mytest4].[dbo].[testbcpFixed]  IN "C:\temp\bcpFixed1.txt" -f "C:\temp\myFormat.fmt" -F 2 -T  -b 10000 -S'+ @@servername
exec master..xp_cmdshell @sql3



 

Query Result To Excel with Messed Up Rows (SSMS 2012)

When you export your query result from SSMS 2012 (not SSMS 2008) to a text file or copy and paste the result (with header), you may run into problem with columns (such as varcahr(max)…) that include Tab — char(9), Line feed — char(10) or Carriage return — char(13). You have garbled rows due to these invisible chars.

A quick fix is to remove them by using nested REPLACE functions in your SELECT query.
Here is and example:


--Sample code
--

SELECT Replace( REPLACE(REPLACE(yourCOlumn, CHAR(10),''), CHAR(13),'') ,CHAR(9),'')  as yourcolumn

--
 

SELECT Replace( REPLACE(REPLACE(yourCOlumn, CHAR(10),”), CHAR(13),”) ,CHAR(9),”) as yourcolumn

If you want to remove them forever, you can use an UPDATE command to update these columns in your source table.


Get total number of rows in a table without SELECT count(*)

--Option 1:
SELECT  OBJECT_SCHEMA_NAME(object_id)+'.'+ OBJECT_NAME(object_id) AS  [TableName]
, SUM(dmps.row_count) AS [RowCount]
 FROM sys.dm_db_partition_stats AS dmps
 WHERE OBJECTPROPERTY(object_id, N'IsUserTable') = 1
-- And object_id = OBJECT_ID('mytablename')
Group by OBJECT_SCHEMA_NAME(object_id) +'.'+ OBJECT_NAME(object_id)

--Option 2
--2.1 Get rows from all user tables
SELECT OBJECT_NAME(object_id) AS Name, SUM(rows) AS TotalCnt
FROM sys.partitions
WHERE index_id in (0,1)
aND OBJECTPROPERTY(object_id, 'IsUserTable') = 1
GROUP BY object_id

--2.2 Get row counts for a table
SELECT OBJECT_NAME(object_id) AS TableName, SUM(rows) AS TotalRows
FROM sys.partitions
WHERE index_id in (0,1) AND object_id = OBJECT_ID('mytablename')
GROUP BY object_id

--Option 3
--3.1
DBCC CHECKTABLE ("yourtablename")
--3.2
DBCC CHECKDB

--Option 4
  SELECT  OBJECT_SCHEMA_NAME(object_id)+'.'+ OBJECT_NAME(object_id) AS  [TableName]
, SUM(rows) AS [RowCount]
 FROM [sys].[system_internals_partitions]
WHERE OBJECTPROPERTY(object_id, N'IsUserTable') = 1
-- And object_id = OBJECT_ID('mytablename')
Group by OBJECT_SCHEMA_NAME(object_id) +'.'+ OBJECT_NAME(object_id)

--Option 5
select so.[name] as TableName, max(si.[rows]) AS TotalRowCount
from sys.sysindexes si inner join sys.sysobjects  so
on si.[id] = so.[id]
where so.xtype = 'U' 
group by so.[name]

 

Option 6: Powershell with SQL Server Provider:


 Import-Module -Name 'SQLPS' -DisableNameChecking 



$svrname = "MC\MSSQL2014" 
$mydbName="myDB1"
$myTableName="mylog"

$myTablePath= "SQLSERVER:\SQL\$svrname\Databases\$mydbName\Tables"
#$_.name -match
#$_.name -eq
Get-ChildItem $myTablePath | ?{$_.name -Like $myTableName}| Select-Object Name,RowCount | Format-Table –AutoSize
 

 

 

Option 7: Powershell with SMO:


 Import-Module -Name 'SQLPS' -DisableNameChecking 
 

$myDBName ="myDB1"
$mytableName ="mylog"

$svrname = "MC\MSSQL2014" 
$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $svrname 

$db = $srv.Databases.Item($myDBName)

$db.Tables.Item($mytableName).RowCount


 



 Import-Module -Name 'SQLPS' -DisableNameChecking


 
$myDBName ="myDB1"


$svrname = "MC\MSSQL2014" 
$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $svrname 

$db = $srv.Databases.Item($myDBName)


foreach ( $t in $db.Tables ) { 

Try
{
Write-Host "Table: " $t.Name  "--- Cnt: " $t.RowCount
 
 
}
Catch [Exception]
 {
 write-host "  "$_.Exception.Message
 }
}


 

Convert String Type British Datetime Data (DD/MM/YYYY hh:mm:ss ) in Varchar/NVarchar to Standard Datetime with T-SQL



create table test (strdt varchar(255))
--British datetime in string
insert into test values 
('13/12/2013 13:33:00 +00:00')
,('01/01/2013 13:33:00 +00:00')
,('28/02/2013 13:33:00 +00:00')
,('31/10/2013 13:33:00 +00:00')
Select 
--string manipulation and cast to datetime
Cast(substring(strdt,7,4)+substring(strdt,4,2)+substring(strdt,1,2) +' '+ substring(strdt,12,8) as datetime),

--Convert string to British datetime first and then convert the British datetime to standard
  convert(datetime, convert(datetime, Left(strdt,20), 103),112)
   
FROM test


--clean up
drop table test

 


 

Strip non-numeric character in a column



create table test (t_id int primary key, col varchar(50))
INSERT INTO test
VALUES      (1,'462'),
            (2,'E08R'),
            (3,'E07'),
            (4,'E09'),
            (5,'E06'),
            (6,'360'),
            (7,'E04'),
            (8,'E11R'),
            (9,'E02R') 

 

;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1), 
processTable as (
    select col,Cast(col1 as int) col1   from test
        cross apply (
            select (select C + ''
            from (select n, substring(Col, n, 1) C 
			FROM Num1  where n<=datalength(Col)) [1]
            where PATINDEX('%[0-9]%',C)> 0
            order by n
            for xml path(''))
        ) p (Col1)

		)

SELECT col,col1 FROM processTable
 
 --http://stackoverflow.com/questions/18625548/t-sql-select-query-to-remove-non-numeric-characters


--Another option
;with
mycte1 as
(select  col, REPLACE(col,SUBSTRING(col,PATINDEX('%[^0-9]%',col),1),'') col1 from test )
,mycte2 as (
select col, REPLACE(col1,SUBSTRING(col1,PATINDEX('%[^0-9]%',col1),1),'') col2 from mycte1)
,mycte3 as (
select col, REPLACE(col2,SUBSTRING(col2,PATINDEX('%[^0-9]%',col2),1),'') col3 from mycte2)
,mycte4 as (
select col, REPLACE(col3,SUBSTRING(col3,PATINDEX('%[^0-9]%',col3),1),'') col4 from mycte3)

Select col, Cast(col4 as int) from mycte4

drop table test

--Another example
create table test (COLUMN_A varchar(100))
INSERT INTO test VALUES
('AAAA AAAAAAA CO A.A. # 4030'),      	
('BBBBBB BBB BBB CO. #  4260.'),  	
('CCC CCCCC #3001'),
('CCC CCCCC 3001')

;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1), 

processTable as (

select Substring(COLUMN_A, 0,Charindex('#',  COLUMN_A)) COLUMN_A , Cast(col1 as int) COLUMN_B   from test
        cross apply (
            select (select C + ''
            from (select n, substring(COLUMN_A, n, 1) C 
            FROM Num1  where n<=datalength(COLUMN_A)) [1]
            where PATINDEX('%[0-9]%',C)> 0
            order by n
            for xml path(''))
        ) p (Col1)
 
        )

 Select * from processTable

 Drop table test

 

A Sample to remove non-numeric values from columns


CREATE TABLE [dbo].[DecoderModels](
	[PK_DecoderModelID] [bigint] IDENTITY(1,1) NOT NULL Primary Key,
	[ModelName] [nvarchar](50) NOT NULL,
	[SN_First] [varchar](50) NOT NULL,
	[SN_Last] [varchar](50) NOT NULL)

declare @sn varchar(50)='aao255'

Insert into [DecoderModels] Values 
('aaa','om000001','om499999')
,('bbb','0001','9999')
,('ccc','aao000001','aao9999999')

;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1),

processTable as (
    select [SN_First], [SN_Last]
	, ISNULL(Cast(Col1 as int),0) as Col1
	, ISNULL(Cast(Col2 as int),99999999) as Col2
	, Cast(Col3 as int) Col3
	,Col4
    from [DecoderModels]
        cross apply (
            select (select C + ''
            from (select N, substring([SN_First], N, 1) C from Num1  where N<=datalength([SN_First])) t
            where PATINDEX('%[0-9]%',C)> 0
            order by N
            for xml path(''))
        ) p (Col1)

		   cross apply (
            select (select C + ''
            from (select N, substring([SN_Last], N, 1) C from Num1  where N<=datalength([SN_Last])) t
            where PATINDEX('%[0-9]%',C)> 0
            order by N
            for xml path(''))
        ) p2 (Col2)

		   cross apply (
            select (select C + ''
            from (select N, substring(@SN, N, 1) C from Num1  where N<=datalength(@SN)) t
            where PATINDEX('%[0-9]%',C)> 0
            order by N
            for xml path(''))
        ) p3 (Col3)

		   cross apply (
            select (select C + ''
            from (select N, substring(@SN, N, 1) C from Num1  where N<=datalength(@SN)) t
            where PATINDEX('%[^0-9]%',C)> 0
            order by N
            for xml path(''))
        ) p4 (Col4)

 
)

SELECT [SN_First], [SN_Last], Col1,Col2,Col3,col4
FROM processTable
WHERE Cast(Col3 as int) Between cast(Col1 as int) And cast(Col2 as int) 
AND ([SN_First] Like Col4+'%' AND  [SN_Last]  Like Col4+'%') Or (IsnuMeric([SN_First])=1 And Isnumeric([SN_Last])=1 And Col4 Is NUll)



drop table [DecoderModels]


 

http://stackoverflow.com/questions/18625548/t-sql-select-query-to-remove-non-numeric-characters


Retrieve XML Name Value Pair with T-SQL


Declare @s as NVarchar(2000) 
='<d><nv n="ParamID" v="Param/FYIInc./354088488" />
<nv n="ParamId" v="151af628-0000-0000-0000-000025700000" />
<nv n="ExtID" v="Station/Television" />
<nv n="StationId" v="0000357e-0000-0000-0000-000000587000" />
<nv n="StartTime" v="03/11/2014 02:01:00" />
<nv n="IsDynamic" v="True" />
<nv n="IsRecurring" v="True" />
<nv n="Frequency" v="EveryDay" />
<nv n="DurationSecs" v="0" /></d>'

--from a string
SELECT
    S.a.value('@n', 'varchar(50)') as name,
    S.a.value('@v', 'varchar(50)') as value   
FROM (SELECT Cast(@s as XML) as sXML) as t
CROSS APPLY sXML.nodes('d/nv') as S(a)


--from a table
declare @test table(id int identity(1,1), myString nvarchar(2000) );
INSERT INTO @test (myString) values(@s);

SELECT 
S.a.value('@n', 'varchar(50)') as name,
S.a.value('@v', 'varchar(50)') as value
FROM (SELECT Cast(myString as XML) as sXML 
FROM @test) t 
CROSS APPLY sXML.nodes('/d/nv') S(a)