Professional Web Applications Themes

Identity column - Microsoft SQL / MS SQL Server

Hi all, I am trying to insert value into an identity column. I got an error message something like that ".. can only be specified when a column list is used and IDENTITY_INSERT is ON." and then I tried (in Query yzer) Set Identity_Insert MyTable On Go Insert Into MyTable Select * From OldTable Go I still get the same error message. My Question is : What exactly is a column list? Thanks in advance, Frank Cheng...

  1. #1

    Default Identity column

    Hi all,

    I am trying to insert value into an identity column.
    I got an error message something like that

    ".. can only be specified when a column list is used and IDENTITY_INSERT is
    ON."

    and then I tried (in Query yzer)

    Set Identity_Insert MyTable On
    Go
    Insert Into MyTable Select * From OldTable
    Go

    I still get the same error message.

    My Question is : What exactly is a column list?

    Thanks in advance,

    Frank Cheng


    Frank Guest

  2. #2

    Default Identity column

    If you set identity insert on you need not specify the
    column value in insert statement.
    just exclude the column value while inserting
    for e.g
    if the products tables is having id and product columns
    with id as identity.
    INSERT INTO products (product) VALUES ('hammer')
     
    IDENTITY_INSERT is 
    Sriram Guest

  3. #3

    Default Re: Identity column

    I have two tables with the same exact structure.
    I was trying to copy data from one table to another.
    There are about a 100 fields in the table, but there
    is only one field (which is the identity column) I don't
    want to copy over. Is there a "select" statement to do that
    without explicitly listing all the column names in the "insert"
    statement? such as

    Select * Exclude myidcolumn from my table

    something like that?

    Thanks,

    Frank Cheng

    "Sriram" <co.in> wrote in message
    news:005a01c34c80$e5ceb540$gbl... 
    > IDENTITY_INSERT is [/ref]


    Frank Guest

Similar Threads

  1. Bound Column or Template Column (w dAdapater?) in DataGrid
    By Ravichandran Mahalingam in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: January 9th, 03:30 PM
  2. SET IDENTITY PROPERTY OF COLUMN?
    By Vishal in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 14th, 02:50 PM
  3. DB2 - OS390: Identity Column Problem
    By Tom Hiscox in forum IBM DB2
    Replies: 5
    Last Post: July 10th, 07:21 PM
  4. Replies: 0
    Last Post: July 7th, 08:32 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139