Skip to content

Instantly share code, notes, and snippets.

@steve-jansen
Created June 24, 2013 02:44
Show Gist options
  • Save steve-jansen/5847457 to your computer and use it in GitHub Desktop.
Save steve-jansen/5847457 to your computer and use it in GitHub Desktop.
A helpful way to import or export data from SQL Server to bcp.exe binary flat files. This can be helpful for storing relatively small sets of test data in source control, dropbox, etc for multiple environments.
:: Name: TestData.cmd
:: Purpose: Convenience script to use SQL Server bcp.exe to import or export
:: sample data as binary files in ~/src/Database/TestData
:: Author: Steve Jansen
:: Revision: December 2012
@ECHO OFF
SETLOCAL ENABLEEXTENSIONS
:: default values
SET me=[%~n0]
SET project=%~dps0..
:: normalize the project path
PUSHD %project% >NUL
SET project=%CD%
POPD >NUL
SET args=%*
SET log="%TEMP%\%~n0.log"
SET cygwin=
SET filename=
SET action=export
SET server=.\SQLExpress
SET database=
SET table=
SET user=
SET password=
SET query=
SET maxrows=
:: parameter checking
ECHO.%* | FINDSTR /I "^-? ^/? ^-help ^/help" >NUL 2>&1 && GOTO :usage
:: environment validation
BCP.exe /? >NUL
IF /I %ERRORLEVEL%==9009 (
ECHO %me%: ERROR - SQL Server bcp.exe utility not found in PATH
EXIT /B 2
)
SQLCMD.exe /? >NUL
IF /I %ERRORLEVEL%==9009 (
ECHO %me%: ERROR - SQL Server sqlcmd.exe utility not found in PATH
EXIT /B 2
)
IF DEFINED ProgramFiles(x86) (
SET cygwin="%ProgramFiles(x86)%\Git\bin\sh.exe" -login -c
) ELSE (
SET cygwin="%ProgramFiles%\Git\bin\sh.exe" -login -c
)
%cygwin% pwd >NUL
IF /I NOT %ERRORLEVEL%==0 (
ECHO %me%: WARNING - Cygwin not found, gzip compression disabled for binary files
SET cygwin=
)
:: command line argument parsing
:args
SET arg=%~1
:: normalize "/X" switches as "-X"
SET arg=%arg:/=-%
:: skip to the main processing block if no args left to process
IF "%arg%"== "" GOTO :main
IF /I "%arg%"=="import" (
SET action=import
SHIFT
GOTO :args
)
IF /I "%~1"=="export" (
SET action=export
SHIFT
GOTO :args
)
IF /I "%arg%"=="-S" (
SHIFT
SET server=%~2
SHIFT
GOTO :args
)
IF /I "%arg%"=="-D" (
SHIFT
SET database=%~2
SHIFT
GOTO :args
)
IF /I "%arg%"=="-U" (
SHIFT
SET user=%~2
SHIFT
GOTO :args
)
IF /I "%arg%"=="-P" (
SHIFT
SET password=-P "%~2"
SHIFT
GOTO :args
)
IF /I "%arg%"=="-T" (
SHIFT
SET table=%~2
SHIFT
GOTO :args
)
IF /I "%arg%"=="-N" (
SHIFT
SET maxrows=%~2
SHIFT
GOTO :args
)
IF /I "%arg%"=="-Q" (
SHIFT
SET query=%~2
SHIFT
GOTO :args
)
:main
IF NOT "%maxrows%"=="" SET maxrows=-L %maxrows%
:: prompt for any mandatory properties that were not passed as command line arugments
IF NOT DEFINED database (
SET /P database=%me%: enter the sql server database name for %action%^>
GOTO :main
)
IF NOT DEFINED user (
ECHO %me%: sql server username not specified
ECHO %me%: defaulting to integrated Windows authentication
SET user=-T
) ELSE (
SET user=-U "%user%"
)
:: tracing printout
ECHO [--- ENVIRONMENT TRACE ---]
ECHO [CMDCMDLINE]: [%CMDCMDLINE%]
ECHO [ARGS]: [%*]
ECHO [CD]: [%CD%]
ECHO [cygwin]: [%cygwin%]
ECHO [action]: [%action%]
ECHO [project]: [%project%]
ECHO [server]: [%server%]
ECHO [database]: [%database%]
ECHO [table]: [%table%]
ECHO [user]: [%user%]
ECHO [password]: [%password%]
ECHO [maxrows]: [%maxrows%]
ECHO [query]: [%query%]
ECHO [--- ENVIRONMENT TRACE ---]
IF "%action%"=="import" GOTO :import
IF "%action%"=="export" GOTO :export
REM this line should be skipped
ECHO %me%: unknown action "%action%"
EXIT /B 4
:export
IF NOT DEFINED table (
ECHO %me%: generating list of all user tables
SQLCMD.exe -S "%server%" -d "%database%" %user:-T=-E% %password% -I -Q "SET NOCOUNT ON; SELECT OBJECT_SCHEMA_NAME([object_id]) + '.' + OBJECT_NAME([object_id]) AS [FullName] FROM [sys].[tables] ORDER BY [FullName]" -h -1 -o "%TEMP%\%~n0.txt" || GOTO :ERROR
) ELSE (
ECHO %table% > "%TEMP%\%~n0.txt"
)
FOR /F %%I IN (%TEMP%\%~n0.txt) DO (
CALL :bcp-export "%%~I" || GOTO :ERROR
)
GOTO :END
:import
SET filename=%table:dbo.=%
SET filter=%filename%.bcp*
IF NOT DEFINED table (
SET filter=*.bcp*
)
ECHO %me%: disabling all constraints in all tables
SQLCMD.exe -S "%server%" -d "%database%" %user:-T=-E% %password% -I -Q "EXEC sp_MSforeachtable @command1='ALTER TABLE ? NOCHECK CONSTRAINT ALL'" || GOTO :ERROR
FOR %%I IN (%filter%) DO (
CALL :bcp-import "%%~nI" || GOTO :ERROR
)
ECHO %me%: enabling all constraints in all tables
SQLCMD.exe -S "%server%" -d "%database%" %user:-T=-E% %password% -I -Q "EXEC sp_MSforeachtable @command1='ALTER TABLE ? CHECK CONSTRAINT ALL'" || GOTO :ERROR
IF DEFINED query (
ECHO %me%: executing post-import query "%query%"
SQLCMD.exe -S "%server%" -d "%database%" %user:-T=-E% %password% -I -Q "%query%" || GOTO :ERROR
)
GOTO :END
:ERROR
ECHO %me%: exiting due to error
SET /A ERRORLEVEL = %errno% + %ERRORLEVEL%
:END
DEL /Q %log% >NUL 2>&1
ENDLOCAL
ECHO ON
@EXIT /B %errno%
:: functions
::#############################################################################
:normalize
SET table=%~1
REM strip the default schema "dbo" from the filename
SET filename=%table:dbo.=%
EXIT /B 0
::#############################################################################
:bcp-export
CALL :normalize "%~1"
ECHO %me%: exporting table %table%
REM see http://msdn.microsoft.com/en-us/library/ms191516.aspx for more info on bcp.exe
REM recreate the format file first
BCP.exe "%table%" FORMAT nul -S "%server%" -d "%database%" %user% %password% -n -f "%filename%.fmt" > %log%
SET /A errno = %errno% + %ERRORLEVEL%
IF NOT "%errno%"=="0" (
FINDSTR /I /B "Error" %log%
EXIT /B %errno%
)
REM export the rows in binary format
BCP.exe "%table%" OUT "%filename%.bcp" -S "%server%" -d "%database%" %user% %password% -f "%filename%.fmt" %maxrows% > %log%
SET /A errno = %errno% + %ERRORLEVEL%
IF NOT "%errno%"=="0" (
FINDSTR /I /B "Error" %log%
EXIT /B %errno%
)
IF DEFINED cygwin (
%cygwin% "gzip --force --best '%filename%.bcp'" >NUL
)
SET /A errno = %errno% + %ERRORLEVEL%
EXIT /B %errno%
::#############################################################################
:bcp-import
CALL :normalize "%~1"
SET table=%table:.bcp=%
SET filename=%filename:.bcp=%
ECHO %me%: importing data into table %table%
IF NOT EXIST "%filename%.fmt" (
ECHO %me%: WARNING - skipping table %table% - file not found - %filename%.fmt
EXIT /B 1
)
IF DEFINED cygwin (
IF EXIST "%filename%.bcp.gz" (
%cygwin% "gzip --force --best --decompress --stdout '%filename%.bcp.gz' > '%filename%.bcp'" >NUL
)
)
SET /A errno = %errno% + %ERRORLEVEL%
SQLCMD.exe -S "%server%" -d "%database%" %user:-T=-E% %password% -I -Q "DELETE FROM %table%" >NUL
SET /A errno = %errno% + %ERRORLEVEL%
BCP.exe "%table%" IN "%filename%.bcp" -S "%server%" -d "%database%" %user% %password% -E -f "%filename%.fmt" > %log%
SET /A errno = %errno% + %ERRORLEVEL%
IF NOT "%errno%"=="0" (
FINDSTR /I /B "Error" %log%
EXIT /B %errno%
)
IF DEFINED cygwin (
IF EXIST "%filename%.bcp" (
DEL /Q "%filename%.bcp" >NUL
)
)
EXIT /B %errno%
::#############################################################################
: usage
ECHO.
ECHO %me%
ECHO. Import or export database sample data to/from binary bulk copy files
ECHO. in the current working directory
ECHO.
ECHO.Usage:
ECHO. %~nx0 [import^|export] -S server -D database [-U username] [-P password]
ECHO. [-T table] [-N maxrows] [-Q query]
ECHO.
ECHO. [import^|export] defaults to %action% when not specified
ECHO. -S the SQL Server host/instance name, defaults to %server%
ECHO. -D the SQL Server database name, required
ECHO. -U defaults to Windows Authentication as %%USERNAME%%
ECHO. -P password, not required for Windows Authentication,
ECHO. otherwise will prompt for password on each table if not specified
ECHO. -T table name to target, defaults to all tables for export,
ECHO. or all bcp files for import
ECHO. -N the maximum number of rows to export, defaults to all rows
ECHO. -Q custom sql query to execute after import; ignored for export
SET /A errno=1
GOTO :END
EXIT /B 0
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment