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


Database defintion from an Excel Spec?


Database defintion from an Excel Spec?

Author
Message
Dan Guzman - Not the MVP
Dan Guzman - Not the MVP
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1310 Visits: 751
It seems like it would be an easy thing to add some VBA code to the excel sheet with an ADO connection to the database.

ADOdb.Open (connectionstring)
Loop table names
Apply Filter
ADOdb.CommandText = Concatenate all the relevant cells
ADOdb.Execute
Loop Back
ADOdb.close

This should run each create statement in a couple of minutes
David Walker-278941
David Walker-278941
SSC Veteran
SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)

Group: General Forum Members
Points: 233 Visits: 231
Personally, I would have not created a SQL script at all. I would have created a VBA module (formerly known as an Excel macro) that reads the spreadsheet columns and creates a text file/SQL script as output, containing Create Table statements, which will create all of the tables. Then you just have to open the generated SQL script and run it. The Excel macro could create one script per table, or one per several tables.

That seems simpler to me. But then, I'm good with both SQL and VBA.
Scott D. Jacobson
Scott D. Jacobson
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1280 Visits: 1020
maybe i'm over-simplifying. but with only 4 or 5 tables, why couldn't you just import them using the Import/Export Wizard? every solution i've seen including the OP seems way more complicated than it needs to be. VB module? there's already a built-in wizard for this.
reggiesnelly
reggiesnelly
SSC Veteran
SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)

Group: General Forum Members
Points: 246 Visits: 19
Seems like you were working with inept management. If the vendor I'm working with doesn't trust their own work, I don't think I would be comfortable with them. At the least hold back the 10% or whatever retention should have been written into the contract subject to my satisfaction.
reggiesnelly
reggiesnelly
SSC Veteran
SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)

Group: General Forum Members
Points: 246 Visits: 19
The requirements should have been written into the document. If you're looking at state and federal level - the request for proposal might need to be rewritten. It could be a change control issue or an out for the vendor if there are problems. The vendor has a low level of confidence in their product or is trying to apply it generically across a number of RDMS.
Dan Guzman - Not the MVP
Dan Guzman - Not the MVP
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1310 Visits: 751
All of these last statements are true, but the author specifically said it was too late for any of those things, and sometimes you just find yourself saying 'Next time I won't....' but until then, you just have to deal with it.

I'm also sure he did not then say 'Hey, now I have this cool program/script generator, who needs contracts and project specs anymore'

If you don't need it, don't use it. Unfortunately, in the real world, this kind of thing happens more frequently than we would like.
ian.hadlington
ian.hadlington
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 366
Well it's a good 5-6 months since I created that script, and it looks as if the project to implement this new software has been delayed until March next year!

Quite frankly, it's difficult to guess at what the politics are.

The consultancy, who is not the software vendor, may well not totally trust the software.

Someone in the higher management structure is keen to be one of the first organisations to successfully implement this software .... in order to impress /their/ (government) client ... or to attract a bonus funding reward ... maybe!

And somewhere along the line, it had been decided, between the consultancy and software client, that we had to deliver something before the consultancy did!! [and they still haven't, as far as I know!!]

The chain of relationships is far from simple to say the least!

Software Vendor
- Software consultancy
- Government body i) (End user) and
- Government body ii) (Information Management)

Inevitably, with such an extended relationship between software vendor and end user, is it any surprise that focus can be pulled in a number of directions. Ideally, both government bodies will have elected a single representative, equally responsive to both, to simplify that relationship chain.

However, my experience of governmental bodies is that they are very reluctant to relinquish control, if they can possibly help it!
Samuel Vella
Samuel Vella
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1941 Visits: 2144
ian.hadlington (11/13/2009)
Well it's a good 5-6 months since I created that script, and it looks as if the project to implement this new software has been delayed until March next year!

Quite frankly, it's difficult to guess at what the politics are.

The consultancy, who is not the software vendor, may well not totally trust the software.

Someone in the higher management structure is keen to be one of the first organisations to successfully implement this software .... in order to impress /their/ (government) client ... or to attract a bonus funding reward ... maybe!

And somewhere along the line, it had been decided, between the consultancy and software client, that we had to deliver something before the consultancy did!! [and they still haven't, as far as I know!!]

The chain of relationships is far from simple to say the least!

Software Vendor
- Software consultancy
- Government body i) (End user) and
- Government body ii) (Information Management)

Inevitably, with such an extended relationship between software vendor and end user, is it any surprise that focus can be pulled in a number of directions. Ideally, both government bodies will have elected a single representative, equally responsive to both, to simplify that relationship chain.

However, my experience of governmental bodies is that they are very reluctant to relinquish control, if they can possibly help it!


Unfortunately that post can be boiled down to 2 words:
Public Sector
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