467,915 Members | 1,263 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,915 developers. It's quick & easy.

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
Oct 9 '08 #1
  • viewed: 1169
Share:
2 Replies
OuTCasT
256MB
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 2GB
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.

Similar topics

10 posts views Thread by johnnyboy10017 | last post: by
2 posts views Thread by Geoffrey KRETZ | last post: by
7 posts views Thread by David Bear | last post: by
6 posts views Thread by rn5a | last post: by
2 posts views Thread by lenygold via DBMonster.com | last post: by
8 posts views Thread by Betikci Boris | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.