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


  
Advertisements

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:

building an inventory view of SQL Servers with dbatools

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