| Author |
Topic |
|
REDTAIL_CODEGUY
Starting Member
17 Posts |
 Posted - 2011-08-23 : 11:07:13
|
| I have Management Studio Express and QueryPlus Querytools that I use for MS-Sql.Is there a tool that will export data from a table to a filethat can be directly used in an "Insert" statement with verylittle or no editing?All the tools I've tried will export into various different formsof .csv, but typically the boolean, uniqueidentifier, and datetime type fields are not single-quoted. Character fields are sometimes double-quoted, which need to be changed to single-quotes before the data is inserted into a table... |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-08-23 : 12:58:40
|
| Have a look at BCP and BULK INSERT |
 |
|
|
REDTAIL_CODEGUY
Starting Member
17 Posts |
Posted - 2011-08-25 : 23:17:07
|
| Thanks. I had looked a BCP before. I'm not sure that BCP willdo any better than the other gui tools out there that I've used.Maybe I'll develop one that will do everything I typically need forsetting up a test database and then offer it up! |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-08-25 : 23:47:53
|
| BCP isn't aa GUI tool, and it's the fastest possible way to accomplish waht you're asking.If you want a GUI, use SSIS, which can do the same thing. Seems you want to re-invent the wheel here. Any decent programmer can write a program to create flat files for easy insert.Are you trying to script the insert statements for every record in a given table? This too can be done with existing tools, but is far slower than BCP and incurs a lot more overhead.By the way, if you just want a test database, based on a production one, just restore a backup. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-26 : 00:42:49
|
quote:
Originally posted by REDTAIL_CODEGUY
I have Management Studio Express and QueryPlus Querytools that I use for MS-Sql.Is there a tool that will export data from a table to a filethat can be directly used in an "Insert" statement with verylittle or no editing?All the tools I've tried will export into various different formsof .csv, but typically the boolean, uniqueidentifier, and datetime type fields are not single-quoted. Character fields are sometimes double-quoted, which need to be changed to single-quotes before the data is inserted into a table...
Are you trying to create insert script from table data and export to text file?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
REDTAIL_CODEGUY
Starting Member
17 Posts |
Posted - 2011-08-26 : 00:57:00
|
| Yes, partly.I've had to do this a lot for various projects. But I always end up having to manuallyfix a whole bunch of sql errors (constraints, keys, etc.) and also manipulate the testdata into syntactically-correct insert statements to load the test database once I have it built.I'd like to have a tool that would build the structure straight on, without a whole bunchof modifications for constraints, etc. Sort of like a backup/restore create-new-structure-onlywithout the data. And then on the data side of it I would like it (the tool) to create insert statements for me with all the proper single-quoting and parens, null values, dates quoted - if need be, etc. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-26 : 02:12:20
|
| for that you can do it using an excel quite easily by taking dump from table and applying excel formulas------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-26 : 03:24:36
|
| SSMS will generate "create table" type scripts, and also the INSERT statements for data (there is a parameter you have to find & check buried deep and I always find it hard to find). They are syntactically correct.Just make sure that FOREIGN KEY creation is done after data import, to avoid referential integrity errors.We keep a "shipping database" of our application. This is a ready-to-install copy, with all starter-data, as well as database structure, of course, and all latest patches. We just restore this for new clients (instead of building a database from scratch). |
|
|
|
REDTAIL_CODEGUY
Starting Member
17 Posts |
Posted - 2011-08-26 : 17:36:33
|
| Thanks for your replies everyone.If I was going use Excel, I might as well just do it the way I usually dousing a good editor. Every table is different.I did use SSMS Express to create my scripts. Express is somewhat limited in many ways; one of themis export capability. I used Script Database as --> Create to --> File; and that didn't create a syntactically correct, directly useable schema for me. You're right Kristen it's a good idea todo the foreign key bits after data import! :) I found that QueryPlus was better at extractingthe data than SSMS Express. I had less manual labor involved to fix the syntax and create theinsert statements, etc.Kristen, what option do you use to create the "Insert" scripts? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-27 : 04:32:31
|
| If you want to create insert script right click on database choose tasks-> generate scripts and in the wizard select script data to yes and it will generate insert script with entire table data for you------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-27 : 05:12:18
|
| "that didn't create a syntactically correct, directly useable schema for me"I'm amazed - although I have never used the Express version. I use SSMS to generate scripts for all changes we make to the database, and I've never had a problem with its script."I had less manual labor involved to fix the syntax and create the insert statements, etc."I wouldn't expect there to be any!Issues I have are that certain constraints may not exist on the target database. SSMS scripts assume that the constraint is there, so you get an error on the DROP statement (which then terminates the transaction). And I am careful to create FKeys last ... but other than that I can't thing of anything, and I've never had a syntax error from scripts generated by SSMS. Never.But that's aint SSMS Express ..."what option do you use to create the "Insert" scripts?"Sorry, can't remember. I just remember that each time I do it it takes ages to find the parameter in the long list of such things. Its definitely in there though ... |
|
|
|
REDTAIL_CODEGUY
Starting Member
17 Posts |
Posted - 2011-08-27 : 07:37:48
|
quote:
Originally posted by visakh16
If you want to create insert script right click on database choose tasks-> generate scripts and in the wizard select script data to yes and it will generate insert script with entire table data for you------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
I had tried that before, but something didn't look right for me, so I gave up on that and went with the other method. I just tried it again and took all the defaults. Looks like it worked pretty well. I didn't actually run it; but, it looks better than what I got the other day using the other method that I mentioned above...  So, maybe that's the way to go for the schema. Data is still a pain though. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-27 : 16:01:03
|
| "Data is still a pain though."I'm missing a bit here .. why is data "a pain"? visakh described the steps to include the data in the script (I hadn't seen his reply when I replied) |
|
|
|
|
MSSQL EXPORTING DATA FOR SQL INSERT?