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

    Question

  • 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;

     

     

     

    Friday, September 07, 2007 7:47 PM

Answers

  • 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 08, 2007 4:29 AM
  • 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;

    Saturday, September 08, 2007 3:53 PM
  • 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

     

     

     

    Sunday, September 09, 2007 6:43 AM
  • Explaining myself might take a while. So to save time I'll just explain the code. Wink

     

    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

    Wednesday, October 24, 2007 11:21 AM
  • 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

     

     

     

    Tuesday, October 30, 2007 7:49 PM

All replies

  • 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 08, 2007 4:29 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'.

     

     

     

    Saturday, September 08, 2007 6:30 AM
  • 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;

    Saturday, September 08, 2007 3:53 PM
  • 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 09, 2007 6:11 AM
  • 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

     

     

     

    Sunday, September 09, 2007 6:43 AM
  • I am just curious. What version and servicepack are you using?

    Sunday, September 09, 2007 4:46 PM
  • 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.

    Monday, September 10, 2007 4:23 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 4:25 AM
  • Hi Adam, thanks for answering. Could you explain your self, please?

    Thanks,

    Aldo.

     

    Wednesday, October 24, 2007 8:13 AM
  • Explaining myself might take a while. So to save time I'll just explain the code. Wink

     

    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

    Wednesday, October 24, 2007 11:21 AM
  • 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

     

     

     

    Tuesday, October 30, 2007 7:49 PM
  • Thanks!

     

    Wednesday, October 31, 2007 5:58 AM
  • 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

     

     

    Monday, November 26, 2007 10:50 PM