Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cipher
Starting Member
10 Posts |
 Posted - 2007-01-06 : 17:22:05
|
| What is the easiset way to create TSQL Insert scripts for each record in a table. Can this be accomplished with one of the tools in SQL Server 2005?Thanks in advance |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-01-06 : 17:29:56
|
| There are a lot of SPs around that will do ithttp://www.nigelrivett.net/SQLTsql/sp_CreateDataLoadScript.htmlhttp://www.nigelrivett.net/SQLTsql/sp_CreateDataLoadScript_Rem.htmlThey don't include the schema but it's easy to add.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-01-06 : 19:56:35
|
if the tables are large, it's better to use bcp. it's much faster to bulk load rather than execute 10m individual insert statements.
www.elsasoft.org |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-07 : 02:53:01
|
| Yup, agree with that. What are you wanting to achieve Cipher? Knowing that would enable use to give you better advice. Otherwise I'm guessing a bit - for example, if its a Deployment tool for a self-install new system I would go for a RESTORE or ATTACH db type solution. If its to get from A to B, and RESTORE is not appropriate, I would either go for BCP for a recurring need, or DTS for a one off.But it rather depends on the circumstances / requirementsKristen |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-01-07 : 03:26:34
|
dts for a one off? but bcp is so easy! I have to admit I've never used dts for anything - *blush* - my understanding is that you have to use a GUI though, and that's a -1 already in my book. 
www.elsasoft.org |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-07 : 10:57:50
|
| "but bcp is so easy! "Yeah, for you and I, but I reckon a novice finds a Point-and-Click GUI interface easier for one offs. Plus DTS will reasonably script a table (at least to PK, IDENTITY and NULL/NOT NULL levels, if not to FK etc. levels), so will do a good-enough job for many novice situations without scripting the tables etc.DTS will also deal with transporting the data to the remote server - and if what you have is, for example, an ISP box then FTP up, locate the right logical-to-physical folder mapping etc. etc. is usually quite a challenge for a one of.Which in turn leads to my point above "I would either go for BCP for a recurring need," so that for something that needs to be done repeatedly its better for the novice user to learn how to tool up for BCP.Actually "Novice" hasn't got much to do with it! With the exception of a big BIG table I would use DTS for a one off too."I have to admit I've never used dts for anything"I'm not a big fan of fully automated DTS stuff. We have several clients who use that, and they all have 0% robustness. They break once in a while (comms down, whatever) and in all instances they cause major trashing of other stuff and loads of consequences down-stream. And they never know about it until users start complaining, and then it takes them hours to work our which of their convoluted DTS tasks caused the problem ...... but that's not a fault of DTS, its a fault of naive programmers thinking that the Point-and-click interface is all they need to do to copy data from A-to-B at 6AM every day! ... actually, maybe that IS a fault of DTS - "false sense of security"None the less, you should try it, you'll be pleasantly surprised.Kristen |
|
|
|
|
|
|
|
|
Create Insert scripts for existing table?