Showing posts with label BCP. Show all posts
Showing posts with label BCP. Show all posts

Tuesday, 24 July 2018

BCP SQL Export By Casting all columns as text


BCP Export By Casting all columns as text
 
You have to concatenate the columns with quotes, except the date columns as follows.

You also have to union all the column name headers with the values.

bcp "SELECT 'coldate' as coldate ,'col2' as col2 ,'col3' as col3 union all select convert(varchar(max),[coldate],120) ,char(34)+[col2]+char(34) ,char(34)+[col3]+char(34) FROM [database].[schema].[Table_Name];" queryout "filePath\csvFile.CSV" -t "," -c  -q  -C 65001 -S"ServerName" -U"UserName" -P"Password"

Thursday, 19 July 2018

BCP SQL Conversion and Cast Functions for Field Headers and Field Values

BCP SQL Export Conversion and Cast Functions for Field Headers and Field Values


--convert to date string from date time
select convert(varchar(50),getdate(),120)

--convert to number string from number
select convert(varchar(20),1234.56)

--convert to string from unicoe string
select convert(varchar(max),N'Test')

Saturday, 31 March 2018

Windows Command Line Powershell CMD Bulk Copy Program SQL Imports And Exports



Windows CMD Powershell BCP SQL Imports and Exports

Windows BCP Export including field headers using union query from Microsoft SQL:

PS C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft SQL Server Tools 17> bcp "select 'Product','Low','High','Close','Previous','Direction','Date'  union all  select * from  test.
dbo.optionsonfxmajorsii" queryout optionsonfxmajorsiv.txt -T -c -SServerName\SQLEXPRESS -U"Username"

Windows BCP Import to Microsoft SQL skip first header row:

PS C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft SQL Server Tools 17> bcp "test.dbo.optionsonfxmajorsii" in optionsonfxmajorsiv.txt -T -c -F2 -SServername\SQLEXPRESS -U"Username"

Note:
-S = Server name without a space
-U = Username without a space.
-T = You trust the database you are connecting to.
-c = character text tab delimiter.
-F2 = import from second row.