SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to script table data with either TSQL or Powershell?


How to script table data with either TSQL or Powershell?

Author
Message
Roust_m
Roust_m
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3520 Visits: 1981
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
Lowell
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69822 Visits: 40918
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
j.miner
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 358
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
j.miner
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 358
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search