The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Free tool for sql scripting??

How so I script table design and data values that need to get promoted from development server to qa or even produciton server. Is there a free sql utility tool for this?

Here is what I do in development server:
1. Design tables
2. Populate the tables with Enterprise manager.
3. Keep testing and refining my design.

When its time to promote to QA server.
1. Create scripts to create tables using enterprise manager.
2. Create scripts to manually add data.
3. Run scripts to make sure that every thing is working and unit test the application.

There has to be a better way of doing the following task:
**2. Create scripts to manually add data.**
Because there is so much chances of error.

Please help
Thursday, March 17, 2005
Oy, this task may be non-trivial.

The hardest thing to do is when you're mapping a populated, existing table to a new table which has either lost some of its fields (compared to the old table) or the meaning of those fields have changed. 

If the meaning of those fields have changed, you may need to 'massage' the data coming out of the old table to insert it into the new one, without losing anything.

And it's hard to test, because to do so you need the old table, what the new table SHOULD look like, another old table copy to use next time, and a place to put the new table so you can compare it against what SHOULD have happened.  And you only really need it to work ONCE, after that you just keep the new, correctly populated table.

I don't know of a tool that does this yet, sorry.
Thursday, March 17, 2005
I'm not sure there's a free tool that automates these functions for you entirely, but I would suggest you take a look at CodeSmith or some of the other DAL code generation tools. Maybe not what you're looking for in a "tool that does it all, but I don't want to pay for it", but I'm using CodeSmith for much the same thing with good results.
Free is good
Thursday, March 17, 2005
May be I didn't explain myself correctly. I only encounter this problem when I have finalized my data table design and am ready to promote it to QA or later to production.

In development, I do have all the tables working with lookup data but how do I promote it to QA? I thought about using DTS but then I don't want to load it from one source to another just in case dev got corrupted or some thing.

So how do I promote table structure and lookup data w/o using development environment as a source?
Thursday, March 17, 2005
A simple solution, but it works for me.

There is a fellow named Vyas who has written a nice stored procedure that goes in master called "sp_generate_inserts".  It is free.  Google should reveal.

When you run this proc against a table it creates a series of insert statements that repro the current data in that table.  Run it once for each of your lookup tables and save the output to a data population script.  Voila.
Matt Conrad Send private email
Thursday, March 17, 2005
Thanks Matt..
Friday, March 18, 2005
Matt, I just tried it and it worked great...

Thanks a lot..
Friday, March 18, 2005 for those too lazy to Google...


Friday, March 18, 2005
You could always use DTS to copy both the table structure and data (I believe it is the copy objects task), as long as both servers are SQL Servers.
Friday, March 18, 2005
Glad it worked out for you, happy to help.
Matt Conrad Send private email
Friday, March 18, 2005

Sorry its not free, but its rocks.
Robin Chauhan Send private email
Thursday, March 24, 2005
I think you've overly complicated the problem.

Promoting from dev to QA should be as simple as exporting.  Any solid commercial database has export/import utilities to allow you to export a table and/or its data.

What we do is set aside a development schema called "stage" where we build a promotable set of objects.  Once we're happy with our development effort, we export stage into QA (actually we call it "Test").  Testers bang up against it and if it passes, we export stage to prod and perform a round of sanity checks.
christopher Send private email
Friday, April 08, 2005

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz