TSQL - Using ALTER TABLE - ALTER COLUMN to modify column type / Set Identity Column

_{Friday, September 7, 2007 7:47 PM}

Hi guys,

If I have a temporary table called #CTE

With the columns

[Account]

[Name]

[RowID Table Level]

[RowID Data Level]

and I need to change the column type for the columns:

[RowID Table Level]

[RowID Data Level]

to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.

What will be the right syntax using SQL SERVER 2000?

I am trying to solve the question in the link below:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1

Thanks in advance,

Aldo.

I have tried the code below, but getting syntax error...

ALTER TABLE #CTE

ALTER COLUMN

[RowID Table Level] INT IDENTITY(1,1),

[RowID Data Level] INT;

I have also tried:

ALTER TABLE #CTE

MODIFY

[RowID Table Level] INT IDENTITY(1,1),

[RowID Data Level] INT;

_{Saturday, September 8, 2007 4:29 AM ✅Answered}

I briefly looked at the other post, and noticed you were doing select into #CTE. When you do that, new table gets the structure of the select list.

You could avoid [RowID Table Level] and [RowID Data Level] data type problems by removing '' in the select list.By this '', you are forcing it to be varchar/char field.

And Identity is a property that is set at the time the table is created or a new column is added in alter table statement. You can't alter the column and set it to identity. Since you know the datatypes in the select list,  why don't you create table first and then do insert #CTE select ....

_{Saturday, September 8, 2007 3:53 PM ✅Answered}

Its very hard to as why you are getting invalid column error. You shouldn't be unless, you are making a mistake somewhere. Let me ask a stupid question. Are your new columns named as [RowID Table Level] or [RowID_Table_Level].

Can you check the column list when you do select * from #CTE after the alter #CTE statement.

--No underscores here

SELECT [RowID Table Level], [RowID Data Level] FROM #CTE

--underscores here

ALTER TABLE #CTE

ADD

[RowID_Table_Level] INT IDENTITY(1,1),

[RowID_Data_Level] INT;

_{Sunday, September 9, 2007 6:43 AM ✅Answered}

Guys, it's Running!!!

Thanks a lot!!!

Aldo.

I just added "GO"...

Code Snippet

-- NOT Running!!!

USE test2006mdt;

SELECT ACCOUNTKEY INTO #CTE FROM ACCOUNTS

ALTER TABLE #CTE ADD [NewColumn] INT;

SELECT [ACCOUNTKEY], [NewColumn] FROM #CTE

Code Snippet

-- Running!!!

USE test2006mdt;

GO

SELECT ACCOUNTKEY INTO #CTE FROM ACCOUNTS

GO

GO

ALTER TABLE #CTE ADD [NewColumn] INT;

GO

SELECT [ACCOUNTKEY], [NewColumn] FROM #CTE

_{Wednesday, October 24, 2007 11:21 AM ✅Answered}

Explaining myself might take a while. So to save time I'll just explain the code.

CREATE TABLE #temp (test int, test2 varchar(50))

ALTER TABLE #temp

DROP COLUMN test -- We can't make this an identity column simply because it doesn't make sense to. So we drop it.

ALTER TABLE #temp

ALTER COLUMN test2 int -- This we can change

ALTER TABLE #temp

ADD test int IDENTITY(1,1) -- And we simple recreate the indentity column

DROP TABLE #temp

In all likelihood, you'll want to keep the old column and just create a new identity column. The old column must be of some benefit or it wouldn't be there.

Adam

_{Tuesday, October 30, 2007 7:49 PM ✅Answered}

Hi Aldo,

I'm having the same problem and have found 2 ways around it.  

I cant drop the table and recreate it, or the field and readd it.  I need to save the columns in the same order with the same names and all data except what is in the field I'm changing to IDENTITY.  If you need to save that data too then only opt 1 works (but take care with dup values if it is to be a unique key by itself).

First and easiest (but unfortunately cant be rolled out in script form) is in ms sql srvr mgmt studio.  Connect to the db, show the table and columns in object explorer window, right click on column name, select modify. In the column properties tab alter "identity specification"/"is identity" to YES.  You can then alter the seed and increment too if you wish but you don't have to.  If you leave it at default (1,1) and insert a row the identity will be set to the next highest value for the column anyway (ie. have 2 rows already with the field set to 1 and 5 respectively it will add the next as 6).

The bigger pain is if you need to do it in script form so it can be rolled out...  (which I'm about to do)   Create a new table with all the same field names/order but set the IDENTITY field as needed.  Insert into new table selecting all fields from the old (except the field changing to identity - the system will give it all new values).  Delete the old table and rename the new to the name of the old. 

If you need a script example let me know. 

Hope this helps you, Jane

_{Saturday, September 8, 2007 6:30 AM}

Hi, thanks for answering!

I can only do changes in #CTE.  I can not touch the original tables. 

The first idea, (Mani's idea) was adding two new columns using ALTER TABLE / ADD, and setting RowID Table Level as Identity.

The problem is that after doing that, I can not retrieve the results of the new columns, and getting Invalid Column Name. It is very strange because if I use SELECT * FROM #CTE I can the results for all the columns, but If I use

SELECT [RowID Table Level], [RowID Data Level] FROM #CTE I get Error. So I thought inserting both columns in the first select statement and after that changing the types and setting [RowID Table Level] as Identity...

Below the code I am trying to run. Is there any other posibility to the ALTER TABLE? (SQL SERVER 2000)

Code Snippet

-- Note: #CTE (creates a new table on temp database & only accessible for current session/scope(local temp table)

SELECT

Accounts.ACCOUNTKEY AS 'Accounts.ACCOUNTKEY',

DocumentsDef.DOCNAME As 'DocumentsDef.DOCNAME',

CASE

WHEN CAST(Stock.DOCUMENTID as int) = 1 THEN Stock.DOCNUMBER

WHEN CAST(Stock.DOCUMENTID as int) = 3 THEN Stock.DOCNUMBER

WHEN CAST(Stock.DOCUMENTID as int) = 35 THEN Stock.DOCNUMBER

WHEN CAST(Stock.DOCUMENTID as int) = 120 THEN Stock.DOCNUMBER

WHEN CAST(Stock.DOCUMENTID as int) = 31 THEN Stock.REFERENCE

WHEN CAST(Stock.DOCUMENTID as int) = 44 THEN Stock.REFERENCE

WHEN CAST(Stock.DOCUMENTID as int) = 34 THEN Stock.REFERENCE

WHEN CAST(Stock.DOCUMENTID as int) = 43 THEN Stock.REFERENCE

WHEN CAST(Stock.DOCUMENTID as int) = 40 THEN Stock.REFERENCE

ELSE '' END AS 'Invoice No',

JurnalTransMoves.SUF AS 'JurnalTransMoves.SUF',

JurnalTransMoves_1.SUF AS 'JurnalTransMoves_1.SUF'

INTO #CTE

FROM

JURNALTRANSMOVES AS JurnalTransMoves_1

INNER JOIN JURNALTRANSMOVES AS JurnalTransMoves

INNER JOIN (SELECT DISTINCT JURNALTRANSID, RECEIPTSTOCKID, FULLMATCH, TABLFNUM, CKCODE, RSORT, RUSEFID FROM RECEIPTJURNALMATCH) AS ReceiptJurnalMatch_1 ON ReceiptJurnalMatch_1.JURNALTRANSID = JurnalTransMoves.ID

INNER JOIN ACCOUNTS AS Accounts ON JurnalTransMoves.ACCOUNTKEY = Accounts.ACCOUNTKEY

INNER JOIN JURNALTRANS AS JurnalTrans ON JurnalTransMoves.TRANSID = JurnalTrans.TRANSID

INNER JOIN STOCK AS Stock ON JurnalTrans.STOCKID = Stock.ID ON JurnalTransMoves_1.TRANSID = JurnalTrans.TRANSID AND JurnalTransMoves_1.ACCOUNTKEY = Accounts.ACCOUNTKEY

LEFT OUTER JOIN ITEMS AS Items

INNER JOIN STOCKMOVES AS StockMoves ON Items.ITEMKEY = StockMoves.ITEMKEY

INNER JOIN ITEMSORTNAMES AS ItemSortNames ON Items.SORTGROUP = ItemSortNames.ITEMSORTCODE ON Stock.ID = StockMoves.STOCKID

LEFT OUTER JOIN ACCSORTNAMES AS AccSortNames ON Accounts.SORTGROUP = AccSortNames.ACCSORTCODE

LEFT OUTER JOIN CREDITTERMS AS CreditTerms ON Accounts.CREDITTERMSCODE = CreditTerms.CREDITTERMSCODE

LEFT OUTER JOIN DOCUMENTSDEF AS DocumentsDef ON Stock.DOCUMENTID = DocumentsDef.DOCUMENTID

WHERE

Accounts.SORTGROUP BETWEEN '3001' AND '3020'

AND Accounts.ACCOUNTKEY IN ('123456')

ORDER BY

[Invoice No],

[JurnalTransMoves_1.SUF]

-- #CTE Up to here

ALTER TABLE #CTE

ADD

[RowID_Table_Level] INT IDENTITY(1,1),

[RowID_Data_Level] INT;

--        When  C  H  E  C  K  I   N   G         #CTE     Results

-- SELECT * FROM #CTE ==> With this line works

-- SELECT [Invoice No] FROM #CTE ==> With this line also works and The results are:

-- SELECT [Invoice No], [RowID_Data_Level] FROM #CTE ==> This Line Gets ERROR:

--Invalid column name 'RowID_Data_Level'.

-- SELECT [RowID_Table_Level] FROM #CTE ==> This Line Gets ERROR:

--Invalid column name 'RowID_Table_Level'.

_{Sunday, September 9, 2007 6:11 AM}

Hi,

This query is killing me...

I have simplified all the code above as shown below:

Code Snippet

USE test2006mdt;

SELECT ACCOUNTKEY INTO #CTE FROM ACCOUNTS

ALTER TABLE #CTE ADD [NewColumn] INT;

SELECT [ACCOUNTKEY], [NewColumn] FROM #CTE

But still having the same error.

Using the line: SELECT [ACCOUNTKEY]  FROM #CTE  or  the line SELECT *  FROM #CTE It DOES WORK.

But changing to the line SELECT [ACCOUNTKEY], [NewColumn] FROM #CTE generates the ERROR: "Invalid column name".

I also tried to run the query in different Databases, but always getting the same error.

Any good ideas?

Thanks in advance,

Aldo.

_{Sunday, September 9, 2007 4:46 PM}

I am just curious. What version and servicepack are you using?

_{Monday, September 10, 2007 4:23 AM}

Hi,

In my PC:

Microsoft SQL Server Management Studio      9.00.1399.00
Microsoft Analysis Services Client Tools      2005.090.1399.00
Microsoft Data Access Components (MDAC)      2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML      2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer      7.0.5730.11
Microsoft .NET Framework      2.0.50727.832
Operating System      5.1.2600

 

but the database I use is located in the company's server, working under SQL SERVER 2000.

_{Wednesday, October 24, 2007 4:25 AM}

CREATE TABLE #temp (test int, test2 varchar(50))

ALTER TABLE #temp

DROP COLUMN test

ALTER TABLE #temp

ALTER COLUMN test2 int

ALTER TABLE #temp

ADD test int IDENTITY(1,1)

DROP TABLE #temp

Adam

_{Wednesday, October 24, 2007 8:13 AM}

Hi Adam, thanks for answering. Could you explain your self, please?

Thanks,

Aldo.

_{Wednesday, October 31, 2007 5:58 AM}

Thanks!

_{Monday, November 26, 2007 10:50 PM}

Adding GO would work but all variable (eg. @var) will be destroyed.

Create dummy table:

Code Block

Create TABLE #TEMP_info ([a] varchar(100))

Create TABLE #TEMP_info_2 ([a] varchar(100), [id] [bigint] IDENTITY(1,1))

Code Block

...

INSERT INTO #TEMP_info_2 ([a])

select [a] from #TEMP_info where blah blah

select id, a from #TEMP_info_2

Guys, it's Running!!!

Thanks a lot!!!

Aldo.

I just added "GO"...

Code Snippet

-- NOT Running!!!

USE test2006mdt;

SELECT ACCOUNTKEY INTO #CTE FROM ACCOUNTS

ALTER TABLE #CTE ADD [NewColumn] INT;

SELECT [ACCOUNTKEY], [NewColumn] FROM #CTE

Code Snippet

-- Running!!!

USE test2006mdt;

GO

SELECT ACCOUNTKEY INTO #CTE FROM ACCOUNTS

GO

GO

ALTER TABLE #CTE ADD [NewColumn] INT;

GO

SELECT [ACCOUNTKEY], [NewColumn] FROM #CTE