Using a tool or script to automate table and relationship creation based on certain criteria

Question

Using a tool or script to automate table and relationship creation based on certain criteria

info-849042

Ten Centuries

Points: 1108
More actions
June 13, 2009 at 7:13 pm

#205958

I need to make 100 or so tables. I have tables called PartStatsXXX and the tables to be made will all be called PartReviewXXX. Some PartStatsXXX tables have corresponding PartReviewXXX tables but I need to make the rest and create a 1:n relationship (as one product can have multiple reviews).
Is there some sort of script or tool which could do this? Or do I have to do this manually?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

info (6/13/2009)
I need to make 100 or so tables. I have tables called PartStatsXXX and the tables to be made will all be called PartReviewXXX. Some PartStatsXXX tables have corresponding PartReviewXXX tables but I need to make the rest and create a 1:n relationship (as one product can have multiple reviews).
Is there some sort of script or tool which could do this? Or do I have to do this manually?
Thanks

try this:

declare @SqlCmd varchar(max)

declare cMyCursor cursor for

select 'CREATE TABLE dbo.' + Replace(name, 'Stats', 'Review') + ' ( RowID int identity, );'

from sys.tables

where name like 'PartStats%' -- get all tables that start with "PartStats"

and object_id('dbo.' + Replace(name, 'Stats', 'Review')) is null -- and do not have a corresponding "PartReview" table

open cMyCursor

fetch next from cMyCursor into @SqlCmd

while @@fetch_status = 0 begin

exec (@SqlCmd)

fetch next from cMyCursor into @SqlCmd

end

close cMyCursor

deallocate cMyCursor

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Can we ask why you are making 100 separate tables? Why not just make one pair of tables adding the XXX as part of the key? You could always make 100 part specific Views of these two tables.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

info-849042 Ten Centuries Points: 1108 More actions · Answer 3

First poster - thanks!

To the last poster - XXX denotes a specific part of a car. So I have one table containing reviews for all parts with a table design like:

ID

PartType (eg wheel, tire)

Comment

I guess this is a possibility. Can you provide an example of exactly what you mean?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

info (6/14/2009)
First poster - thanks!
To the last poster - XXX denotes a specific part of a car. So I have one table containing reviews for all parts with a table design like:
ID
PartType (eg wheel, tire)
Comment
I guess this is a possibility. Can you provide an example of exactly what you mean?

This is SOP relational design. Just use the design that you have above. When you want a particular part just use the where clause to parse it out. If you need it to appear as a separate table, then just copy that Select with a where clause into a view.

I can show you specific examples if you post the definitions for the PartStatXXX and PartReviewXXX tables.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

info-849042 Ten Centuries Points: 1108 More actions · Answer 5

RBarryYoung (6/14/2009)
info (6/14/2009)
First poster - thanks!
To the last poster - XXX denotes a specific part of a car. So I have one table containing reviews for all parts with a table design like:
ID
PartType (eg wheel, tire)
Comment
I guess this is a possibility. Can you provide an example of exactly what you mean?
This is SOP relational design. Just use the design that you have above. When you want a particular part just use the where clause to parse it out. If you need it to appear as a separate table, then just copy that Select with a where clause into a view.
I can show you specific examples if you post the definitions for the PartStatXXX and PartReviewXXX tables.

The above I understand. I have experience with views etc so it all makes sense.

What does SOP mean? Apologies for the naivety, and is there any performance issues in loads of relations coming into one table?

Thanks for all your advice.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

No, I should apologize. I use american slang and business jargon too often and I forget that many readers are not familiar with them.

"SOP" stands for "Standard Operating Procedure" and it just means "this is how it's normally done".

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]