none
SQL SERVER error during insert

    Question

  • Hi,

    I am facing with multiple problems from creation of the table to inserting data into it. I am using SQLSERVER 9.0

    1) The issue while creating the table, I need two dates(timestamp) to be created, The error thrown is :

    Msg 2738, Level 16, State 2, Line 1
    A table can only have one timestamp column. Because table 'table_name' already has one, the column 'CHANGE_DATE' cannot be added.

    2)  I am resolving the above error by using only one timestamp field and making other as varchar for executing the code. The issue is with insert command. 

    insert into schema.dbo.table_name

    select * from schema.dbo.source_table;

    gives me an error.

    3) I tried to resolve this error by using the format of 

    insert into schema.dbo.table_name values (a,b,c)

    select a,b,c from schema.dbo.source_table;

    But this is followed by the error

    Msg 128, Level 15, State 1, Line 2
    The name "BRAND_ID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

    All these problems go away with using the statement 

    select * into schema.dbo.table_name from schema.dbo.source_table;

    Why is the traditional insert select causing problems in SQL SERVER 9.0?

    Friday, August 02, 2013 5:44 AM

Answers

  • The issue while creating the table, I need two dates(timestamp) to be created, The error thrown is :

    Hello,

    The old (and deprecated) data type "timestamp" isn't date time related, it's a binary value; the data type name is misleading, there it's new name is rowversion

    Use the data type "datetime" instead


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Gaurav86 Friday, August 02, 2013 8:54 AM
    Friday, August 02, 2013 6:32 AM

All replies

  • This Issue occurs if the Column Brand_ID is an Identity Column or it is a Calculated Column. Please check the Table Structure for the same. And the statement

    select * into schema.dbo.table_name from schema.dbo.source_table;

    is working fine because the Table

    schema.dbo.table_name
    is created during the statement execution with only the data and no calculations.

    --------------------------------------------------------

    Surender Singh Bhadauria

    My Blog

     

    Friday, August 02, 2013 6:30 AM
  • The issue while creating the table, I need two dates(timestamp) to be created, The error thrown is :

    Hello,

    The old (and deprecated) data type "timestamp" isn't date time related, it's a binary value; the data type name is misleading, there it's new name is rowversion

    Use the data type "datetime" instead


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Gaurav86 Friday, August 02, 2013 8:54 AM
    Friday, August 02, 2013 6:32 AM
  • Try this

    insert into schema.dbo.table_name (a,b,c)
    select a,b,c from schema.dbo.source_table;


    Satheesh

    Friday, August 02, 2013 8:22 AM
  • Thanks Olaf, I tried and it worked fine. Also Would you care to comment on Surinder's reply as above or you think he is right in assessing the columns as identity columns( I am not sure what that means) as the columns are not calculated columns?

    Friday, August 02, 2013 8:49 AM