Formatting Output In Sqlcmd Mode?

Is there a way I can specify output file format in SQLCMD mode so that I can read it back using BULK INSERT? I want to do something like this: :CONNECT SERVER1 :OUT 'E:\test' SELE

Solution 1:

Here's a good example for using sqlcmd to generate a CSV file:

http://www.siusic.com/wphchen/sqlcmd-example-to-output-in-csv-format-350.html

sqlcmd 
  -S “ServerName” -U “UID” -P “PWD” -d “DBName” 
  -i “c:\input.txt” 
  -o “c:\output.txt” 
  -s “,” 
  -h -1

You can use SQLCmd /? to display all applicable parameters. ... Parameter -h -1 instructs the utility not to output result set header. Parameter -s “,” instructs the utility to use comma as column separator.

Solution 2:

One way to get the data bulk inserted in a somewhat automated fashion is to use an intermediate table.

(using your original scenario)

  :CONNECT SERVER1
  :OUT'E:\test'SELECT TOP 100'^'+cast(ID asvarchar(16))+'^^'+NAME
  FROM DB1.dbo.TABLE1

  GO

  :CONNECT SERVER2
  :OUT'E:\test2'CREATETABLE #TEMP1(BOTHCOLUMNS VARCHAR(116))

  BULK
  INSERT #TEMP1
  FROM'E:\test'
  GO

  CREATETABLE #TEMP2(ID INT, NAME VARCHAR(100))

  INSERT #TEMP2
  SELECTSubstring(BOTHCOLUMNS, 2, Patindex('%^^%', BOTHCOLUMNS) -2), 
       Substring(BOTHCOLUMNS, Patindex('%^^%', BOTHCOLUMNS) +2, 
                 Len(BOTHCOLUMNS) - Patindex('%^^%', BOTHCOLUMNS) -1) 
  FROM   #TEMP1
  WHERE  Patindex('%^^%', BOTHCOLUMNS) >1SELECT t2.*FROM DB2.dbo.TABLE2 t2
  JOIN #TEMP2 tmp2 on tmp2.ID=t2.ID


-- Other queries for #TEMP

GO