Drop Table And Recreate from a View problem

[Cocheez]

For some reason I think the solution to this is very simple. I however can not find it.

I need to drop a table in which the structure may be obsolete (the data will always be). Then I need to recreate the table based on another table or view's structure. What I have works when the table is of the same structure or does not exists, kind of like the Drop Table command isn't executing at all or in time. What am I missing?

Create     Procedure spSyncTest
As

If Exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_Indexed_Scores_Combined]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	Exec('Drop Table [dbo].[tbl_Indexed_Scores_Combined]')

-- Start : Resynce tbl_Indexed_Scores_Combined --
Select * Into [dbo].[tbl_Indexed_Scores_Combined] From [dbo].[vw_Scores_Combined] Where 1 = 0

ALTER TABLE [dbo].[tbl_Indexed_Scores_Combined] WITH NOCHECK ADD 
	CONSTRAINT [PK_tbl_Indexed_Scores_Combined] PRIMARY KEY  CLUSTERED 
	(
		[QuestionID],
		[SerialID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 

Insert Into [dbo].[tbl_Indexed_Scores_Combined] Select * From [dbo].[vw_Scores_Combined]

 

[donutman]

I'm guessing but I think the Drop has to be done i.e. finalized. If you did that in QA with a GO after the Drop it should work.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

In the first place why aren't you using table variables or temp tables? It is a bad practice to create and drop permamanent tables with any regularity. IT will make your database run less efficiently and if multiple users are running the process simultaneously, their results will get mixed up. Very bad practice.

What table is the view you used based on? Could it be the table you dropped? Why are you basing a table on a view? Why not just create teh columns you need? You are continually recreating the view with different columns too are you?

Alnever use select * in an insert statement. YOu should always specify the exact columns you want, then if one table changes but hte other does not, the insert won't fail.



Questions about posting. See faq183-874

 

[Cocheez]

That is true, in QA it does work with a GO. However, I need this in stored procedure so that another person can call it and refresh the data whenever he needs to, even put it on a timed process if he has to.

 

[donutman]

You could put it in its own SP and call it from the other SP, but SQLSister has valid points of greater concern.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Cocheez]

SQLSister, It is actually an attempt to solve a situation where the view is far to slow to query data from (in the users eyes). Therefore we are creating tables, which are mirror images of the views from the last time it was run. We then can index the columns and the flattened table version of the data is lightning fast. This is not a process that a user will be running, only another programmer, and never during user operation. Think of it as a maintenance tool only.

The view is based on many many tables to flatten data out for reporting. By the way, the select * in the insert statement should be fine since it is supposed to be first dropping the table then recreating the table structure and not blinding inputting data.

I guess I just need to know how to finialize the drop statement.

 

[Cocheez]

I tried adding the Drop statements in another SP but it still gave me the same problem.

I also do not contest SQLSisters concerns, I just don't think they will matter in my case as I am not matching any of her scenarios. I am sorry to get you all wryled up, it is hard to explain your exact situation without writing what amounts to a short story on these boards.

 

[donutman]

Hmm. I'm surprised that having one SP call the other didn't work. Well your app could call the DropSP and then call the CreateSP.
BTW, a view can be indexed.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Cocheez]

Can't a view only be indexed in Enterprise edition though? At least that is what the documentation I read said. We only have standard edition

http://www.sqlteam.com/item.asp?ItemID=1015

Thanks for your help by the way guys & gals.

 

[Cocheez]

You are correct Karl, running one then the other works great and will probably be my final solution. Still would be nice to see how to finalize drop (or any other) commands while in an SP.

Thanks again.

 

[SQLSister]

That's correct, you can't index a view in Standard edition.

So basically you are creating a data warehouse?

Why not truncate the table and then just repopulate it? Why drop the whole table?

Since you say the only ones running it will be IT people, they cna be given the necessary rights to truncate tables.

Questions about posting. See faq183-874

 

[Cocheez]

While it is true IT people will only be running this procedure, I wanted it to be flexable enough to automatically pick up any changes made to the flat model (views) of the data without having to add the field to insert statement as well. Mainly a convience thing and so that there is less of a chance of human error.

 

[SQLSister]

Ok then try this

Make a multiple step job. Step one is the table dropping if it exists

Step two is the rest of the process.

That should solve the problemthat the batch hasn;t committed.

Questions about posting. See faq183-874

 

[donutman]

"committed". Would a Begin Transaction/Commit Transaction sequence around the Drop statement in a single SP do the trick?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]