September 10, 2003 at 1:38 pm
I've got a stored procedure which creates a local temp table and then alters the table to create a PK (PKThisTable) that includes 3 fields. This procedure is part of a number of procedures and this data in the table is passed around so a table variable probably isn't possible.
Problem lies, when a second instance of the temp table is created before the first instance is drop, there is an error that the object PKThisTable already exists and it can't be created. I know SQL Server automatically adds a suffix to the name of the temp table to keep each instance separate, but why does this not happen for the PK object? Do I need to create a string variable or something to make a unique name for the PK?
Am I forgetting something obvious?
September 10, 2003 at 3:32 pm
I suggest not explicitly naming the PK constraint. Let SQL come up with a unique one. Eg.
ALTER TABLE #ThisTable WITH NOCHECK ADD
PRIMARY KEY CLUSTERED (a,b,c)
Cheers,
- Mark
Cheers,
- Mark
September 11, 2003 at 9:24 am
Excellent, that is what I eventually found as well. But now my user wants multiple column indexes on the temp table. Any way to create a temp table without naming the indexes?
Thanks for your help,
Linda
September 11, 2003 at 4:12 pm
Difficult question. It CAN be done, but it's not an ideal solution. The example below will acheive the aim (in a very ugly way), using SQL's automatic creation of indexes for PRIMARY KEY and UNIQUE constraints.
The first UNIQUE example can be used for an index on columns you know are going to be unique.
The second UNIQUE clause is for columns that aren't necessarily unique, so to make them unique the primary key columns are included.
As I warned, not very pretty. But it does the job.
create table #ThisTable (
a int not null,
b int not null,
c int not null,
d int not null,
e int not null,
f int not null,
g int not null,
h int not null,
i int not null,
primary key clustered (a,b,c),
unique (d, e),
unique (f,g,h,a,b,c)
)
Cheers,
- Mark
Cheers,
- Mark
September 11, 2003 at 4:22 pm
Or, going back to your original idea of a string variable....
create table #ThisTable (
a int not null,
b int not null,
c int not null,
d int not null,
e int not null,
f int not null,
g int not null,
h int not null,
i int not null,
primary key clustered (a,b,c),
unique (d, e)
)
go
declare @newindex varchar(200)
set @newindex = 'create index [' + convert(varchar(50), newid()) + '] on #ThisTable (f,g,h)'
execute (@newindex)
set @newindex = 'create index [' + convert(varchar(50), newid()) + '] on #ThisTable (i)'
execute (@newindex)
Cheers,
- Mark
Cheers,
- Mark
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply