Select * into and alter table - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Select * into and alter table

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-15 : 09:37:56

I need to run a SELECT * INTO for a table who's data i need in a new database, but, after moving that data in, i need to change the structure of one column, removing the IDENTITY specification to that column. What does my alter table line need to look like that i will add to this script. The code for the table is posted below. The column that needs to change is the ID column:


USE [DouglasConversion]
GO

/****** Object:  Table [dbo].[warrantdata]    Script Date: 03/15/2012 08:33:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[warrantdata](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[WarrantTypeID] [int] NULL,
	[CFSID] [int] NOT NULL,
	[WarrantNumber] [varchar](25) NULL,
	[WarrantStatusID] [int] NULL,
	[IssuedDate] [datetime] NULL,
	[InputDate] [datetime] NOT NULL,
	[CaseNumber] [varchar](50) NULL,
	[OTN] [varchar](50) NULL,
	[OffenseID] [int] NULL,
	[IncidentDateFrom] [datetime] NULL,
	[IncidentDateTo] [datetime] NULL,
	[MailedOutTo] [varchar](50) NULL,
	[MailedDate] [datetime] NULL,
	[MailReturnedDate] [datetime] NULL,
	[AssignedFugitiveDate] [datetime] NULL,
	[CourtDate] [datetime] NULL,
	[Misc] [text] NULL,
	[ReleaseDate] [datetime] NULL,
	[ReleaseWeek] [varchar](5) NULL,
	[RollNumber] [int] NULL,
	[DateEnteredNCIC] [datetime] NULL,
	[EnterNCICTypeID] [int] NULL,
	[DateCancelledNCIC] [datetime] NULL,
	[CancelNCICTypeID] [int] NULL,
	[CoDefendants] [varchar](255) NULL,
	[CheckNumber] [varchar](20) NULL,
	[ArrestDate] [datetime] NULL,
	[BondInfo] [text] NULL,
	[Magistrate] [varchar](25) NULL,
	[ReceivedDate] [datetime] NULL,
	[BondTypeID] [int] NULL,
	[BondRestriction] [bit] NULL,
	[ReceiptNumber] [varchar](20) NULL,
	[Agency] [varchar](50) NULL,
	[EFNumber] [varchar](20) NULL,
	[InCustody] [varchar](50) NULL,
	[HoldPlaced] [datetime] NULL,
	[ChargeTypeID] [int] NULL,
	[WarrantNarrative] [text] NULL,
	[Location] [char](255) NULL,
	[AgencyID] [int] NULL,
	[DivisionID] [int] NULL,
	[SeeNotes] [bit] NULL,
	[ExpireDate] [datetime] NULL,
	[LastUpdate] [datetime] NULL,
	[Judge] [varchar](25) NULL,
	[OutOfCounty] [bit] NULL,
	[EntryUserID] [int] NULL,
	[Notify] [bit] NULL,
	[TolledDate] [datetime] NULL,
	[MNIPRE] [varchar](9) NULL,
	[ArrestnoPre] [varchar](9) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-15 : 09:51:47

See here: http://social.msdn.microsoft.com/Forums/en-US/sqlsmoanddmo/thread/adc78696-9b31-4ffd-b881-5a2f87249cfd

You can not remove Identity propery of a column with out droping it. Possible method will be

(a) Add a new column

(c)Update the column with identity column value

(c) Remove the identity column.

Alter Table Tablename Add newColumnname int

Update Table set newColumnname =IdentityColumn

Alter Table TableName Drop IdentityColumnName

No, you're never too old to Yak'n'Roll if you're too young to die.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-15 : 09:58:00

But i cannot add an identity specification to a new column while the identity specification still exists on the first column. Oddly enough, if i do it through the GUI in MGMT Studio, i can drop the id spec on the first column, then create my new column with the ID spec that i need.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-15 : 10:00:23

Check what T-SQL the SSMS GUI issues. Hint: create table ... insert into .. select ... drop table ... sp_rename

Rather than using SELECT ... INTO, craft the CREATE TABLE statement as you need it and specify exactly where you want the identity column.

--
Gail Shaw
SQL Server MVP

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-15 : 10:08:14

quote:
Originally posted by WJHamel

But i cannot add an identity specification to a new column while the identity specification still exists on the first column. Oddly enough, if i do it through the GUI in MGMT Studio, i can drop the id spec on the first column, then create my new column with the ID spec that i need.

Your first post isn't saying that you need a new column with identity?
The GUI isn't a kind of magic, it does the same T-SQL in background.

No, you're never too old to Yak'n'Roll if you're too young to die.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-15 : 10:18:37

I'm aware that the GUI is doing the same thing i would be doing in a script, i would just like to be able to see what the GUI is doing as it drops that spec. I'll go the create table route suggested.

thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-15 : 10:29:10

There's a little button top left on just about any dialog or wizard that allows you to script the changes to a query window.

--
Gail Shaw
SQL Server MVP

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-15 : 10:29:32

quote:
Originally posted by WJHamel

I'm aware that the GUI is doing the same thing i would be doing in a script, i would just like to be able to see what the GUI is doing as it drops that spec. I'll go the create table route suggested.

thanks

In GUI means in Designer and there the left most button is used to get a script instead of execution so you can see what would be going on.

No, you're never too old to Yak'n'Roll if you're too young to die.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-15 : 10:41:10

Thank you both.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources