duplicating tables 2

[jesse1]

I am a Coldfusion programer that uses SQL 2008 as a backend but am not proficient sql. I would like to write a script that I can use to duplicate tables. For example, I have table1Dem, table2Dem and table3Dem.

I would like to duplicate table1Dem and table2Dem w/o carrying the data over. Table3dem I would like to duplicate the table and the data.

I would also like to pass a parameter for the naming of the new tables. Ie. I would pass "Lyn" and the new tables would be named table1Lyn, table2Lyn and table3Lyn.

I would appreciate any guidance in this area.

Thanks

 

[bborissov]

CREATE PROCEDURE DulicateTables
       @EndOfTheName varchar(200) = 'BAK'
AS
 BEGIN
     DECLARE @NewTableName varchar(200)
     DECLARE @Sql          varchar(200)
     SET @NewTableName = 'Table1Dem'+@EndOfTheName
     SET @SQL = 'SELECT * INTO '+@NewTableName+
                ' FROM Table1Dem WHERE 1=0'
     EXEC(@SQL)

     SET @NewTableName = 'Table2Dem'+@EndOfTheName
     SET @SQL = 'SELECT * INTO '+@NewTableName+
                ' FROM Table2Dem WHERE 1=0'
     EXEC(@SQL)

     SET @NewTableName = 'Table3Dem'+@EndOfTheName
     SET @SQL = 'SELECT * INTO '+@NewTableName+
                ' FROM Table3Dem'
     EXEC(@SQL)
 END

BE AWARE! By using dynamic SQL you are open for SQL injections.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[jesse1]

I created a script with the above code. When I run the script it creates the "DulicateTables" procedure. How do I run the run that procedure?
If I try to excute the script again it just says the "DulicateTables" procedure already exists.

 

[bborissov]

Sure, that just create an SP that YOU should use when you want to duplicate tables.
To run that SP:

EXEC DulicateTables 'Lyn'

or:

EXEC DulicateTables @EndOfTheName  = 'Lyn'

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Hi Boris,

Your code doesn't replace Dem with Lyn (@EndOfTheName).

 

[markros]

E.g. Boris code should work, just remove Dem from
SET @NewTableName = 'Table1Dem'+@EndOfTheName

lines.

 

[bborissov]

O! S***

Also I forgot to ask WHAT if there are tables with the same name already?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

or a view with the name of the table... or a procedure with the same name as the table... or a function....

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

plus.... nobody mentioned that duplicating tables violates normalization rules.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

Friday evening
George be merciful with us

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

I'm grumpy today. My apologies to everyone.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jesse1]

It works. but should I be worried about violating normalization rules?

 

[gmmastros]

You should always be worried about violating normalization rules.

You don't mention WHY you want to duplicate tables. It seems to me like you want each user to have their own copy of certain tables. Having multiple tables (with different names) holding similar information is a maintenance nightmare.

For example:

[tt][blue]
GeorgeFavorites
---------------
Cheesesteak
Lamborghini
Long Walks on the beach

JesseFavorites
---------------
Top Gun
Bananas
Anything Orange
[/blue][/tt]

Instead, it's better to have a single table with another column, like this:

[tt][blue]
Favorites
------------
George Cheesesteak
George Lamborghini
George Long Walks on the beach
Jesse Top Gun
Jesse Bananas
Jesse Anything Orange
[/blue][/tt]

With a single table, there's no need to mess around with dynamic SQL. All the related data is in one table. Add a new user? No problem. Add another row to a table.

With the multiple table approach, adding a new user is incredibly difficult, prone to errors and slow.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

No apologies needed. You are pretty right of course.
That should be the first question asked

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[jesse1]

George,
I guess the problem lies in my lack of database knowledge. The original database was set up how you are suggesting. I decided to separate the tables (per client) because I feared data corruption with multiple clients hitting the same tables at the same time. I also wondered if the database would slow down with larege numbers of records.

Should I revert back to the original set up?

 

[markros]

Looks like you came from some other database background. Keep one table (not multiple). You may want to partition it later if the table will be huge.

 

[gmmastros]

Should I revert back to the original set up?

I think you should.

SQL is optimized to handle large tables, and do it efficiently.

Databases can slow down with large numbers of records, but you should realize that 99% of the time, the solution to this "slow down" problem can be solved by writing [google]SQL Server sargable[/google] queries and/or creating better indexes.

As for data corruption.... You really need to work pretty hard to corrupt SQL Server itself. It is incredibly stable. Now... the data you store inside tables.... that's another story. Making sure that the data in the tables is correct is your responsibility.

Have you considered a separate database for each client? What you could do is create tables in the model database. Populate some of them with lookup data. Then, when you get a new client, simply create a new database for them. If you are interested in this idea, I encourage you to read this:

http://blogs.lessthandot.com/index....rver/automatically-create-a-sql-server-table-

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jesse1]

George,
Thanks so much for the help. My last question is how many records can I put in a table before things start to slow down. My concern is with two tables, the first has 9 int fields, 6 nvarchar fields, and a date field. The second has 6 text fields and 2 int fields

 

[gmmastros]

how many records can I put in a table before things start to slow down.

That's a question with no good answer. You see.... it depends on many different things.

1. It depends on the indexes
2. It depends on the queries
3. It depends on the server hardware
4. It depends on the contention (reading data vs. writing data)
5. It depends on the number of concurrent users.
6. It depends on your definition of slow.

With so many things it depends on, there's not real answer. What I can say is..... each of these issues have solutions.

I've seen tables with millions of rows perform like they are nearly empty, and I've seen tables with just a couple thousand rows behave poorly.

If you have queries that are performing poorly, I encourage you to start a new question asking for suggestionst o improve performance.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[snitin78]

If you are interested in this idea, I encourage you to read this:

http://blogs.lessthandot.com/index....rver/automatically-create-a-sql-server-table-

George, I am really interested in reading this article, but the link appears to be broken (incomplete in your previous post). Can you please provide the complete URL?

Thanks.