(kccrosser) I think the original answer was correct, assuming that the question term “to script out the data” meant “generate a complete set of Insert statements to reproduce the contents of a table”. There isn’t any native function that does this. The “sqlcmd” example below simply dumps the data, but a “script” should contain a fully qualified “Insert into <table> ( <column_list> ) values ( <value_list> );” statement for each row of data.
I disagree with the below answer, there is a native utility that can be used to script data in a Microsoft SQL Server (2005 and above) called “SQLCMD”. In previous versions of SQL, this command was ‘osql’. This can be used in interactive mode from a command prompt as well as used in scripting. See the below example, also, from a command prompt on your SQL server type ‘sqlcmd -?’ for a full list of the command parameters.
sqlcmd -H “HOSTNAME of Server” -S ServerName\InstanceName -d Employees -U Userid -P Password -Q “SELECT * From Personal_Info” -o F:\Data\SQLTest.txt
Breaking this simple batch file down, it initiates sqlcmd, passes the hostname of the database server, defines the database server\sql instance (if an instance is defined, otherwise just the database server name), passes a simple SELECT * from Employees.Personal_Info table and redirects the output to a text file. If you need the output in unicode format, pass the -u flag in addition to the others. You can also use a previously saved sql query by using the -i flag instead of -Q.
There are some third party utilities which can do this for you. I believe that Red-Gate and Quest Software both have one.
There are no native tools that can be used to script out the data. You can export the data using the BCP command line utility or SSIS.