April 8, 2009 at 9:11 am
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........
April 8, 2009 at 10:13 am
You need to list the columns you are inserting into unless you are inserting into every column in the table, like this:
INSERT INTO tblMyTable
(
Value01,
Value02,
Value03,
Value04
)
SELECT
Value01,
Value02,
Value03,
Value04
FROM
vwMyView
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 8, 2009 at 10:18 am
Ah Thanks Jack.
I have to get into that way of thinking rather then the old DestinationField = SourceField way.
Thanks again.
April 9, 2009 at 8:45 am
Best Practicse is always have list of columns INSERT and SELECT statement to avoid any confusion when you are copying the data.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply