T-SQL and Dropping and Recreating a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

T-SQL and Dropping and Recreating a table

Author

Topic

ease2002
Starting Member

8 Posts

Posted - 2008-06-11 : 10:55:27

Hi Guys,

I am trying to DROP a table and recreate the table in a stored procedure and then I am trying to INSERT records into the newly created table in the same stored procedure.

I know I don't have to DROP the table, but I am trying to just get it to work. The process runs without error, but when I refresh the tables, the table I created isn't there. The T-SQL is as follows:


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ImportFilesPremier]
	
AS

DROP TABLE dbo.[PremierTest]

CREATE TABLE [PremierTest] (
	[AYQ] nvarchar(6) null ,
	[CYQ] nvarchar(6) null ,
	[Description] nvarchar(50) null, 
	[PIP] [decimal] (17,2) ,
	[BI] [decimal](17,2) Not null,
	[PD] [decimal](17,2) Not null,
	[COLL] [decimal](17,2) not null,
	[COMP] [decimal](17,2) not null,
	[DCRAT] [nvarchar](2) null ,
	[Agent][nvarchar](3) null ,
)  ON [PRIMARY]

begin transaction

insert into [PremierTest]
select *
from dbo.[new agt type tri 0804]
WHERE dir_ceded_ind = 'C'     

commit transaction

Any information on how I can tweak my code so it works properly would be greatly appreciated. Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 11:05:11

You use schema dbo when dropping the table.
Maybe you should use same schema (dbo) when creating the table?

E 12°55'05.25"
N 56°04'39.16"

ease2002
Starting Member

8 Posts

Posted - 2008-06-11 : 11:22:32

No dice...

I tried what you suggested and the PremierTest table does not show up under Tables in the SSMS explorer pane.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 11:25:31

its always better to encapsulate the DROP statement with a IF

IF EXISTS(SELECT 1 FROM sysobjects where type='U' AND name='PremierTest')
DROP TABLE dbo.[PremierTest]
GO

CREATE TABLE [PremierTest] (
...

ease2002
Starting Member

8 Posts

Posted - 2008-06-11 : 13:47:24

Thanks, that appears to put me on the right track to solving this issue.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources