Create Primary Key with SELECT INTO

Here's what I got:

CREATE PROCEDURE [dbo].[sp_Create_Version]  
@versiontable varchar(100),
@coretable varchar(100),
@versionname varchar(500),
@versioncreator varchar(100),
@srcFields varchar(4000)
AS
declare @SQL varchar(5000)
set @SQL = ' IF NOT  EXISTS ( select [name] from sysobjects WHERE type = ' + '''' + 'U' + '''' + ' AND [name] = ' + '''' + @versiontable + '''' + ')
                            BEGIN
                               select *, cast(''' + @versionname + ''' as varchar(500)) as version_name, cast(''' + @versioncreator + ''' as varchar(100)) as version_creator, getdate() as version_date into [dbo].' + @versiontable + ' from ' + @coretable +'
                            END
                    ELSE
                    BEGIN
                           INSERT INTO '+@versiontable+'('+@srcFields+', version_name, version_creator, version_date)
                             SELECT '+@srcFields+','''+@versionname+''','''+@versioncreator+''', getdate()  FROM '+@coretable+'
                   END'
                     
EXEC (@SQL)
GO

In the select *, cast ....section, I want to create a new field with the Primary Key, like:


 BEGIN
                               select recid Primary Key,*, cast(''' + @versionname + ''' as varchar(500)) as version_name, cast(''' + @versioncreator + ''' as varchar(100)) as version_creator, getdate() as version_date into [dbo].' + @versiontable + ' from ' + @coretable +'
                            END

I know that won't work, but is it possible in a select into statment?  Or do I need to ALTER the table after this statement?

Additional details here :  http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21213871.html
LVL 31
alorentzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HilaireCommented:
Not possible AFAIK
You'll need to create the PK afterwards

Yet if you need a unique key, you could use

select identity(int, 1, 1) as recid, *, .... into .... from .....
0
tomvergoteCommented:
you'll have to do an
alter table @versiontable add primary key ....
0
HilaireCommented:
Note that my solution above won't create a real primary key,
but will create a unique auto-incremental sequence number
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

alorentzAuthor Commented:
The identity is being passed from other table, so the identity is already there.  The problem is that I'm also linking these table through Access, and with the primary key, they are not updatable.  So I need to create the Primary Key in the sp I am using.

I changed to this:

 BEGIN
                               select *, cast(''' + @versionname + ''' as varchar(500)) as version_name, cast(''' + @versioncreator + ''' as varchar(100)) as version_creator, getdate() as version_date into [dbo].' + @versiontable + ' from ' + @coretable +'
                               ALTER TABLE '+@versiontable+'
                              ADD recID INT IDENTITY
                              CONSTRAINT recID_pk PRIMARY KEY
                            END

But I get error because the table already has identity.  
0
alorentzAuthor Commented:
Typo:

The problem is that I'm also linking these table through Access, and with NO primary key, they are not updatable
0
alorentzAuthor Commented:
Also, can I drop the existing Identity without know the name of the column?  By INDEX maybe?
0
alorentzAuthor Commented:
Nevermind, I figured out how to do it...  Thanks.
0
HilaireCommented:
>>Also, can I drop the existing Identity without know the name of the column? <<
No, but

You can find the identity col as follows

select name from syscolumns where id = object_id('yourtable') and columnproperty(id,name,'IsIdentity')=1

rather than drop it , you could use it as the primary key
I let you make it dynamic

alter table yourtable add constraint pk_table_name_version primary key (identity_column)

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HilaireCommented:
Sorry for beeing so long, I was busy at my own job ...
0
alorentzAuthor Commented:
And I thought everyone here didn't work! ;)
0
tomvergoteCommented:
I work :)
0
alorentzAuthor Commented:
>>I let you make it dynamic

>>alter table yourtable add constraint pk_table_name_version primary key (identity_column)

Where does this value come from?  "identity_column"
0
tomvergoteCommented:
select name from syscolumns where id = object_id('yourtable') and columnproperty(id,name,'IsIdentity')=1
should return the name, so select an @var for it
0
HilaireCommented:
as tomvergote suggests ;-)

declare @sql3 varchar(1000)
select @sql = 'alter table yourtable add constraint pk_' + @versiontable + ' primary key (' + name + ')'
from syscolumns where id = object_id('yourtable') and columnproperty(id,name,'IsIdentity')=1
exec (@sql3)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.