SQL Server Performance

Openrowset (INSERT) error Insert Error: Column name or number of supplied values does not match table definition.

Discussion in 'General Developer Questions' started by bbasir, Mar 24, 2008.

  1. bbasir New Member

    Hi:
    I want to create teh column names dynamically from my sql that is being passed and not to hard code the column names in my template excel file.
    When I do select * from table and also set Select * from sheet1, it gives me an erro, my code is below. I am not sure whatI am doing wrong? I just want my column headers to be created dynamically also usining INSERT openrowset and dynamic sql passed. I am also using multi worksheets.
    set @provider = 'Microsoft.Jet.OLEDB.4.0'
    set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn

    exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT *
    FROM [Sheet1$]'')
    '+ @sqlRIDBID + '')

    exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT *
    FROM [Sheet2$]'')
    '+ @sqlRELID + ' ')


    sqlRIDBID ='select * from table1'
    sqlRELID ='select * from table2'

    the above gives me an error....


    Server: Msg 213, Level 16, State 5, Line 1
    Insert Error: Column name or number of supplied values does not match table definition.
    Server: Msg 213, Level 16, State 1, Line 1
    Insert Error: Column name or number of supplied values does not match table definition.

    even when I change the above two sql statments with the column names, then it also gives me an error, ??? the same one as above...

    I have added the hdr=yes as suggested as well.

    It seems that the only way it works is that as long as column names are there in the template file??? I donot want to hard code the headers in the template file as want to generate them dynamically from the sql that passed to the open rowset.

  2. satya Moderator

    Why not use a staging table to insert all these with a column name, then use a query to get those columns to main table.
    Run the following to get column name
    select column_name + ',' from information_schema.columns
    where table_name='user_table_b'
    order by ordinal_position
  3. bbasir New Member

    Thanks Satya for the reply, however how will I ntegrate the above information scheme with my Openrowset INSERT?
    Lets take this simple pubs database query...I am not sure where will I use the information schema in this query?
    INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=YES;Database=C:pubsTest.xls', 'SELECT * FROM [Sheet1$]')
    SELECT * FROM pubs.dbo.authors

    Server: Msg 213, Level 16, State 5, Line 1
    Insert Error: Column name or number of supplied values does not match table definition.
  4. satya Moderator

    Find what are the column names and datatypes of the table, and how many columns worth of data is in the sheet?
    Do the datatypes match in relative order with the table?
    Is there an identity or computed column in the table?
  5. bbasir New Member

    Thanks Satya for your help, but in open rowset there are only two selects...
    1. from the excel... select * from sheet1$
    2. from the database... select * from pubs.dbo.authors
    where will or can I use the selact from the information schema?
  6. fafam123 New Member

    hiyou must write field name in excel file so that in 'SELECT * FROM [Sheet1$]' command return currectly

Share This Page