Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2006
    Posts
    26

    Unanswered: Adding new record with values from a view

    Good day all,
    I am currently re-writing an Access app in SQL Server 2000 so
    I am in a VBA mindset finding my way through MS SQL.
    I have a table which i'd like to automatically add a new record and a few values from a view in on e swift action. In VBA it would be a simple case of .AddNew, Dlookup value from query, so i'm trying to find the SQL alternative to that. From what I gather this should take care of the task.

    INSERT INTO tblMyTable
    SELECT Value01, Value02, Value03, Value04
    FROM vwMyView
    GO
    However it is throwing up this error message:

    Insert Error: Column name or number of supplied values does not match table definition.

    I have named the column names in the view the same as in the destination table however, the view only pulls four fields of the 20 or so that the destination table has, but I was hoping it would create the new record, insert the four values and leave the other values null until it is time to upddate those fields.
    Is it possible to create a new record with just four values in SQL, and if so how ?

    Thanks in advance,
    Mitch........

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Code:
    INSERT INTO tblMyTable (Value01, Value02, Value03, Value04)
    SELECT Value01, Value02, Value03, Value04
    FROM vwMyView
    GO

  3. #3
    Join Date
    Sep 2006
    Posts
    26
    Thank you Mr Crowley for a very quick reply.
    Worked wonders,
    Cheers again !

    Mitch............

  4. #4
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    58
    Just a note:

    Never do an
    Insert into table
    select * from table

    or

    Insert into table
    select col1, col2, col3, from table

    If you have indentity columns in your new table.

    Cheers,

Posting Permissions

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