By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,414 Members | 1,586 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,414 IT Pros & Developers. It's quick & easy.

INSERT INTO Query

P: 1
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
Oct 9 '08 #1
Share this Question
Share on Google+
2 Replies


OuTCasT
100+
P: 374
If you are retrieving information from textboxes then u can do this

Expand|Select|Wrap|Line Numbers
  1. insert into [TableName](columns) Values (' " & textbox1.text & " ',' " & textbox2.text & " ')",sqlConnection

Then if any of the textbox values are null then it will get saved as such
Oct 9 '08 #2

ck9663
Expert 2.5K+
P: 2,878
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
Oct 9 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.