INSERT INTO Query

I have a table with 8 fields (Field1 .. Field8) that I wish to use as a generic insert into table.

My problem is that sometimes the fields I wish to insert will be less than 8.

When I try this I get the following error:
Insert Error: Column name or number of supplied values does not match table definition.

Is there a way I cound fill out the unused fields with blanks or nulls

TIA
Hosh

re: INSERT INTO Query

If you are retrieving information from textboxes then u can do this


Then if any of the textbox values are null then it will get saved as such

re: INSERT INTO Query

Before doing anything else, here are some considerations:

1. Are the columns on your generic table have the same names with that of your source table?

2. When you say that there are cases that your source table sometimes have fewer columns, do you mean any columns could be missing or just the right most?

It would be easier if your target and source table have the same column names. If this is the case you could do this:

1. Get the column names of your source table and store it as a comma delimited string/varchar. Let's say you use a variable called @ColumnNames

2. Build a dynamic query that will look like:

EXEC ('INSERT INTO TARGET_TABLE (' + @ColumnNames + ') SELECT ' + @ColumnNames + ' FROM SOURCE_TABLE ')

Then you are sure that your source table will always fit your target table, with respect to number of columns (not the size of each).

Good luck

-- CK