Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to script table data with either TSQL or Powershell? Expand / Collapse
Author
Message
Posted Thursday, April 11, 2013 12:34 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 10:41 PM
Points: 989, Visits: 1,653
Hi,

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

Thanks.



Post #1441123
Posted Thursday, April 11, 2013 5:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:34 PM
Points: 12,903, Visits: 32,144
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1441197
Posted Friday, April 12, 2013 1:20 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 1:08 PM
Points: 80, Visits: 351
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
Post #1441876
Posted Friday, April 12, 2013 1:23 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 1:08 PM
Points: 80, Visits: 351
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
Post #1441879
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse