How to script table data with either TSQL or Powershell?

• Roust_m

  SSCoach

  Points: 17410

  More actions

  April 11, 2013 at 12:34 am

  #273319

  Hi,

  I can use GUI of the MS to script out data from a table. Is this possible using either Powershell or TSQL?

  Thanks.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  April 11, 2013 at 5:58 am

  #1605534

  i just found this for powershell:

  http://stackoverflow.com/questions/3891952/sql-server-script-data-smo-scripter-not-working-when-output-to-file

  for doing it via TSQL, i've used a script by Narayana Vyas Kondreddi for years and years:

  http://vyaskn.tripod.com/code.htm#inserts

  there's a few parameters that need to be changed to varchar(max) if you trip over some errors, but it's a really great effort.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• j.miner

  SSCrazy

  Points: 2264

  More actions

  April 12, 2013 at 1:20 pm

  #1606119

  Hi There,

  I usually do not promote products; However, red gates source control software plug in allows you to save data into SVN. This data then can be restored with the schema to another database.

  http://www.red-gate.com/products/sql-development/sql-source-control/entrypage/5-minutes?utm_source=google&utm_medium=cpc&utm_content=unmet_need&utm_campaign=sqlsourcecontrol&gclid=CLja-ZvqxbYCFcXe4AodyHEAAg

  If you do not have the money to spend, BCP the data out/in using your favorite text format. Check out my blog showing how to do this.

  http://craftydba.com/?p=1584

  Microsoft even does this with the Adventure Works install script using a CSV format. I like a tilda ~ or fixed format since tabs do occur in unclean data.

  BULK INSERT [Person].[ContactType] FROM '$(SqlSamplesSourceDataPath)ContactType.csv'

  WITH (

  CHECK_CONSTRAINTS,

  CODEPAGE='ACP',

  DATAFILETYPE='char',

  FIELDTERMINATOR='\t',

  ROWTERMINATOR='',

  KEEPIDENTITY,

  TABLOCK

  );

  Good luck

  John

  John Miner
  Crafty DBA
  www.craftydba.com

• j.miner

  SSCrazy

  Points: 2264

  More actions

  April 12, 2013 at 1:23 pm

  #1606122

  A couple more ideas.

  1 - When I was doing Oracle work in a past life, the TOAD utility had the ability to turn a record set in a data grid to a bunch of SQL INSERT statements.

  This is definitely slower than BCP.

  2 - Use the import export utility, which is a wizard utility tool, to create and run a simple SSIS package. This uses bulk copy operation under the hood.

  Many other ways can be listed, but the import/export and bcp utilities come installed out of the box.

  John Miner
  Crafty DBA
  www.craftydba.com