Post reply

SQL Problem w/insert into other server->table with calculated column

  • December 8, 2006 at 7:59 am

    #193436

    Hi,

    I added a calculated column to server1-table1. Then I ran a stored procedure to deploy this server1-table1 to server2-table2 using insert into server2-table2 select col1, col2, col3 from server1-table1. It errored out saying "Insert Error: Column name or number of supplied values does not match table definition."

    Both tables have the same layout.

    I added Null to the select to 'replace' the calculated column, it errored out because I have no permission to write to the calculated column.

    When I tested it from one table to another within the same server it didn't give me a problem, I didn't have to list the calculated column in the select, but I can't do this between servers. Is it because of permissions or you can't do an insert into to another server table if you have calculated columns?

    Thanks,

    K.

     

  • December 8, 2006 at 8:50 am

    #676936

    It would help to see the DDL for the tables.

  • December 8, 2006 at 9:02 am

    #676944

    do you have an identity on the tables ? if so you can't assume the sql will ignore this column

    try the following

    rather than

    insert into tablex select a,b,c from tabley

    use

    insert into tablex (columna,columnb,columnc) select a,b,c

    MVDBA

  • December 8, 2006 at 9:28 am

    #676965

    Hi,

    I couldn't find a reason why it didn't work so I removed the formula, but kept the column. I added this formula to the Select so now works fine.

    Thanks for your suggestions.

    K.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply