[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Create Primary Key with SELECT INTO

Posted on 2004-11-23
14
Medium Priority
?
3,459 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:alorentz
  • 6
  • 5
  • 3
14 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 12654811
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
 
LVL 8

Expert Comment

by:tomvergote
ID: 12654823
you'll have to do an
alter table @versiontable add primary key ....
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 12654835
Note that my solution above won't create a real primary key,
but will create a unique auto-incremental sequence number
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 31

Author Comment

by:alorentz
ID: 12654986
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
 
LVL 31

Author Comment

by:alorentz
ID: 12654993
Typo:

The problem is that I'm also linking these table through Access, and with NO primary key, they are not updatable
0
 
LVL 31

Author Comment

by:alorentz
ID: 12655095
Also, can I drop the existing Identity without know the name of the column?  By INDEX maybe?
0
 
LVL 31

Author Comment

by:alorentz
ID: 12655464
Nevermind, I figured out how to do it...  Thanks.
0
 
LVL 26

Accepted Solution

by:
Hilaire earned 2000 total points
ID: 12655471
>>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
 
LVL 26

Expert Comment

by:Hilaire
ID: 12655484
Sorry for beeing so long, I was busy at my own job ...
0
 
LVL 31

Author Comment

by:alorentz
ID: 12655564
And I thought everyone here didn't work! ;)
0
 
LVL 8

Expert Comment

by:tomvergote
ID: 12655663
I work :)
0
 
LVL 31

Author Comment

by:alorentz
ID: 12655702
>>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
 
LVL 8

Expert Comment

by:tomvergote
ID: 12655737
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 12655762
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question