INSERT INTO SQL syntax (Access 2000 Win 2000 SR-1)

Topic

New Reply

I am wopping out a really LOONG SQL statement for an Insert into Access and wanted to know if there’s a syntax that allows me to clearly map the inserted value to a column name in a table. I’ve tried a couple of approaches, but getting no real success:

INSERT INTO db (colname1, colname2, colname3) VALUES ((colname1=colname1value), (colname2=colname2value), (colname3=colname3value)) and again using AND in place of commas in the VALUE statement or not nesting the VALUE parenthesis and I even tried eliminating the column names in the INSERT INTO section and having the mapping in the VALUE part of the statement.

The issue here is a way to easily understand what value goes into what column for a SQL containing 22 column names and values — which will obviously be hard to maintain without some way to tag what the actual value will be mapped to a column.

any ideas?

Reply | Quote

Replies

The syntax to insert one record is

INSERT INTO tablename (colname1, colname2, colname3) VALUES (colname1value, colname2value, colname3value)

The VALUES part contains a comma-separated list of values, in the same order as the field names in the first part after INSERT INTO tablename. If you are creating the SQL in code, just think carefully about what you are doing. You are the programmer, so you are responsible for keeping track of what goes where.

Reply | Quote

well, I didn’t know if there was a way to specify the VALUES section with the column name. I thought I saw a syntax like VALUE (ColName=ColNameValue) somewhere but I may have misread something. generally I just do some looping and concatenating to build SQL statements automatically, but in this particular case I have to deliminate some values with apostrophese (those with text and spaces), one with # (a date field) and others with no apostrophes (number values) so I have to hand-code the SQL. Since there’s going to be some hefty SQL statements coming out of this project, I was hoping to be able to index the value portion to make it easier to confirm the right value is going to the right column. Perhaps what I want isn’t a consideration in ANSI SQL. Perhaps my unconscious created a method that doesn’t exist!

It would also be really keen in frontpage’s code view supported word-wrap…

Reply | Quote

You’re thinking of the SELECT syntax:

INSERT INTO tablename (colname1, colname2, colname3) SELECT T1.colname1value, T1.colname2value, T1.colname3value FROM othertablename AS T1;

Reply | Quote

Yes, that’s absolutely correct. I was wondering where I picked up the syntax for INSERT INTO and, this morning as I was driving into work, realized I was using the SELECT statement style & thought it might work with INSERT INTO. which apparently it doesn’t.

Reply | Quote

There are actually two distinct valid forms of the INSERT INTO statement, the one you used and the one pointed out by Charlotte. The most general form is the one mentioned by her:

INSERT INTO ThisTable (Field1, Field2, Field3) SELECT Field1, Field2, Field3 FROM ThatTable

There can also be a WHERE clause after the SELECT … FROM ThatTable. This form can insert many records in one go. The other form inserts only one record and uses constant values instead of field names:

INSERT INTO ThisTable (Field1, Field2, Field3) VALUES (Value1, Value2, Value 3)

Reply | Quote

This SQL statement

INSERT INTO ThisTable (Field1, Field2, Field3) SELECT Field1, Field2, Field3 FROM ThatTable

Sounds like a method for transferring data from one table to another, in which case the actual values for the fields are specified in the WHERE conditions applied to the source table, so you don’t list the actual values in the SQL statement.

In the case I am working on, you are calling up data using ASP and inserting that data (portions depending on user interaction) into another database — basically, I am building a transfer routine to update old records from an old db to a new db (with a much greater number of data points). This is on a case-by-case basis and what I am ending up with is several potential SQL strings built from sections of data specified in a record from the old db. Therefore, I will be mapping values from one system to the tables and fields of another system so I will have some very long INSERT statements and wanted to be able to ‘tag’ the VALUES to the FIELDS so it will be easy to see what data is going where. I may still be able to get the system to generate the SQL automatically but it has been a bit difficult so I was working on hand-coding the SQL info when this issue came up of using the SELECT style for the INSERT INTO method. In my opinion, it would be very nice to be able to do even something like this:

INSERT INTO table VALUES (field1.field1value, field2.field2value etc)

instead of

INSERT INTO table(field1, field2 etc) VALUES (field1.field1value, field2.field2value etc)

but either is better than

INSERT INTO table(field1, field2 etc) VALUES (value1, value2 etc) when you are dealing with a lot of fields and values.

HOWEVER, if ACCESS SQL doesn’t support that kind of syntax, there’s nothing to be done about it.

Reply | Quote