Import Tab Delimited Text File with bcp

  
 USE [myDB]
GO
 
CREATE TABLE [dbo].[mySourceTable](
	[col1] [nvarchar](max) NULL,
	[Zip] [nvarchar](max) NULL,
	[col3] [nvarchar](max) NULL,
	[col4] [nvarchar](max) NULL,
	[col5] [nvarchar](max) NULL,
	[col6] [nvarchar](max) NULL,
	[col7] [nvarchar](max) NULL,
	[col8] [nvarchar](max) NULL,
	[col9] [nvarchar](max) NULL,
	[Latitude] [nvarchar](max) NULL,
	[Logitude] [nvarchar](max) NULL,
	[col12] [nvarchar](max) NULL
)  
GO

 

  

 
 --1.Generate XML format file 
 exec master..xp_cmdshell  'bcp  [myDB].[dbo].[mySourceTable] format nul -c -x -f  C:\temp\myFormatFileZip.xml -T  -t\t -r0x0A -S MC0555\SQL2014DEV';
 --Tab delimited and Row terminator -r0x0A

---2. bcp in with all data 
exec master..xp_cmdshell  'bcp  [myDB].[dbo].[mySourceTable] IN "C:\temp\US.txt" -f "C:\temp\myFormatFileZip.xml" -T -S MC0555\SQL2014DEV';

go


  

Import Tab Delimited Text File with dbaTool

  
  
  
$DataTable = Import-Csv C:\temp\mysourceZip.txt -Header "col1","Zip","col3","col4","col5","col6","col7","col8","col9","Latitude","Logitude","col12" -Delimiter  "`t"
Write-DbaDbTableData -SqlInstance MC04\myDEVserver -InputObject $DataTable -Table mydb.dbo.myStagingTable -AutoCreateTable 

  

 

https://docs.dbatools.io/#Write-DbaDbTableData

https://stackoverflow.com/questions/24751377/how-can-i-import-a-tab-delimited-text-file-with-import-csv-when-the-file-has-no

How to Change Primary Key Column Name in a Table with Spatial Index

Here is a list of steps for how modify a column (primary key) name with spatial index for geography data type.

  
  
--step 0  Optional
--drop  foreign keys if pointing to the primary key column in question

---Step 1 drop spatial index
 --Step 2 drop primary key constraint


 --Step 4:  add primary key constraint
  
--Step 3 rename column name
 sp_rename @objname = N'[dbo].[myTablewithSpatialIndexForDatatype_geography_Column].[myOldID]', @newname = N'myNewId'  
 Go

  ---Step 5: add back SPATIAL INDEX

  ---Step 6:  add foreign key back to point to the primary key after name change 
 

 

dbaTools sample Inventory Code

Andreas Schubert posted a blog about how to use dbaTools to get SQL Server information inventory in your organization.
Here is the link to the post:

https://dbatools.io/building-an-inventory/

And the code download:
https://github.com/AndreasESchubert/Automation

You just need to create a name list for your servers.
The scan is pretty quick and generate an HTML view.

Script Table and Export Data with dbaTools

  


## Get-DbaDbTable
## Export-DbaScript
## Export-DbaDbTableData
  
  #script table: accounttype and addresstype
Get-DbaDbTable -SqlInstance myserver -Database mydb -Table 'dbo.accounttype', 'dbo.addresstype' | Export-DbaScript -FilePath C:\temp\myexport\myexportscript.sql 
# export data from accounttype
Get-DbaDbTable -SqlInstance myserver -Database mydb -Table 'dbo.accounttype' | Export-DbaDbTableData -Path C:\temp\myexport\ 
# export data from addresstype
Get-DbaDbTable -SqlInstance myserver -Database mydb -Table 'dbo.addresstype' | Export-DbaDbTableData -Path C:\temp\myexport\
 
  

 

https://docs.dbatools.io/#Export-DbaScript

dbatools: Backup-DbaDatabase and Restore-DbaDatabase samples

I have played around with Backup-DbaDatabase and Restore-DbaDatabase cmdlets from dbatools. The following snippets are: backup selected databases and restore them with a different name on the same server.

  
  
  
#create a shared folder
$Network_Folder = '\\myfileshare\backups\'


#clean up backup folder --shared
Get-ChildItem -Path $Network_Folder -Include *.bak -File -Recurse | foreach { $_.Delete() }

#backup selected databases
Backup-DbaDatabase -SqlInstance my2019Machine -Database myDB1,myDB2,myDB3 -Type Full -CompressBackup -CopyOnly -BackupDirectory $Network_Folder

# restore a copy with new name
Restore-DbaDatabase -SqlInstance my2019Machine -Path $Network_Folder -RestoredDatabaseNamePrefix 'daily_' -DestinationDataDirectory d:\restoreedDB -WithReplace

# clean up
Remove-DbaDatabase -SqlInstance my2019Machine -Database daily_myDB1,daily_myDB2,daily_myDB3 -Confirm:$false

 
 

 

The term ‘Install-Module’ is not recognized When you try to Install dbatools

If you are not on a machine with Powershell 5.1 or above, you will run into this error.
You can run command below to see the installed version.

  
  
  
 
 $PSVersionTable.PSVersion

 ## or

$PSVersionTable.PSVersion
 
 
 

 

You can try to download Windows Management Framework 5.1 to get Powershell 5.1.

https://www.microsoft.com/en-us/download/details.aspx?id=54616

dbatools 1.0 released Today!

Let’s start with Powershell console with administrator rights.

  

Install-Module -Name dbatools

 

You will have dbatools 1.0 installed from Powershell console with a few clicks.
You can check to see how many versions before 1.0 installed on your machine:

  

Get-InstalledModule -Name dbatools -AllVersions

 

You can clean up installed old versions by running:

  

$module = Get-InstalledModule -Name dbatools
Get-Module -Name $module.Name | Remove-Module
Get-InstalledModule $module.Name -AllVersions | Where-Object {$_.Version -ne $module.Version} | Uninstall-Module -Verbose

 

You can check again:

  

Get-InstalledModule -Name dbatools -AllVersions

 

It is time to dive into the dbatools 1.0 now.

https://dbatools.io/dbatools10/
https://claudioessilva.eu/2019/06/19/dbatools-v1-0-its-available-check-it-out/

http://passionindata.de/2017/11/28/cleanup-remove-old-powershell-modules/

https://dbatools.io/getting-started/

COUNT Distinct Over() Sample

  
 create table #Tab (Eset varchar(5),Pid varchar(5), opt varchar(9), Erule varchar(5), effdate datetime)
Insert into #Tab values ('004A','ET','opt1rc150','A','2017-01-01')
Insert into #Tab values ('004A','ET','opt2rc170','A','2017-01-01')
Insert into #Tab values ('004A','ET','opt3rc150','A','2017-01-01')
Insert into #Tab values ('004A','ET','opt4rc150','A','2017-01-01')

Insert into #Tab values ('004A','ET','opt1rc200',NULL,'2018-01-01')
Insert into #Tab values ('004A','ET','opt2rc200',NULL,'2018-01-01')
Insert into #Tab values ('004A','ET','opt3rc200',NULL,'2018-01-01')
Insert into #Tab values ('004A','ET','opt4rc200',NULL,'2018-01-01')


Insert into #Tab values ('005A','ET','opt1rc175',NULL,'2018-01-01')
Insert into #Tab values ('005A','ET','opt1rc200',NULL,'2018-01-01')
Insert into #Tab values ('005A','ET','opt1rc200',NULL,'2018-01-01')
Insert into #Tab values ('005A','ET','opt1rc200',NULL,'2018-01-01')

Insert into #Tab values ('007A','ET','opt1rc140',NULL,'2019-01-01')
Insert into #Tab values ('007A','ET','opt1rc140',NULL,'2019-01-01')
Insert into #Tab values ('007A','ET','opt1rc140',NULL,'2019-01-01')
Insert into #Tab values ('007A','ET','opt1rc140',NULL,'2019-01-01')

 


;with mycte as (
SELECT  Eset,Pid  , opt ,Erule,effdate,
dense_rank() over( Partition by Eset,Pid,effdate Order by  right(opt,3)) 
+ dense_rank() over(Partition by Eset,Pid,effdate Order by  right(opt,3) desc) - 1 as dr FROM #Tab  
 )
 

 select Eset,Pid,opt ,Erule,effdate
 from mycte
 where dr=1




drop table  #Tab 
 

https://stackoverflow.com/questions/11202878/partition-function-count-over-possible-using-distinct