Select Into with Pri Key definition

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 2000 Forums

SQL Server Development (2000)

Select Into with Pri Key definition

Author

Topic

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2005-11-28 : 12:01:02

Please Help Guys,

I am creating a table dynamically thru a stored procedure using the [select into] statement

One of the parameters of the stored procedure "@Identifier" is to be used as the pri key. it has a type of bigint.

I need to basically use this parameter as primary key and also give it column name when the table is created

CREATE Procedure CreateTempTable
( @PoolID int, @Identifier bigint )
as

SELECT @Identifier bigint, C1.Operator, C1.SerialNumber into MyTempTable FROM
COLTABLE C1
WHERE C1.POOLID = @POOLID

when I run this procedure, the name is defaulting to bigint...I also need to make the column the table's primary key

Please help

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-28 : 12:04:06

Would need to do this via dynamic sql.

exec ('SELECT ' + @Identifier + ' = convert(bigint, null), C1.Operator, C1.SerialNumber into MyTempTable FROM
COLTABLE C1
WHERE C1.POOLID = ' + convert(varchar(20),@POOLID) )

will need to set the values before making @Identifier a pk.
If it's an identity then look at the ientity function.
Again use dynamic sql to add the pk constraint.

It's probably a bad idea to do any of this,

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-28 : 12:08:02

Please read the hint link in my sig, BUT...is your query going to only return 1 row? Also, you don't specify the data type. It's assuming it's the alias of the column, which you do need to supply.

Also, if this is being called by multiple operations, then it will fail becaus eyour table already exists.

Do you mean it to be #myTempTable?

All in all I can't see the value of soemthing like this.

Can you describe what you are trying to accomplish, and please read the hint link below.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2005-11-28 : 13:42:16

I was dropping and recreating the table, but decided to create the table once, on first use and have each subsequent user identified by the identifier column.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyTempTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

skip table creation and do insert

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2005-11-28 : 14:03:39

Thanks a bunch.

How do I give the Identifier column a name

quote:
Originally posted by nr

Would need to do this via dynamic sql.

exec ('SELECT ' + @Identifier + ' = convert(bigint, null), C1.Operator, C1.SerialNumber into MyTempTable FROM
COLTABLE C1
WHERE C1.POOLID = ' + convert(varchar(20),@POOLID) )

will need to set the values before making @Identifier a pk.
If it's an identity then look at the ientity function.
Again use dynamic sql to add the pk constraint.

It's probably a bad idea to do any of this,

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-30 : 13:19:11

The column name will be the contents of the variable @Identifier - isn't that what you wanted?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources