SQL for INSERT INTO

[1x2z3]

Want to archive records from 1 table to another by using the INSERT INTO sql in my form by clicking on a Archive command button, the record must be save into the archive table.

using : INSERT INTO Archive(Name, Surname) FROM SELECT Name, Surname FROM Addresses.

Must i declair a variable doens't want to word with my command button...

 

[dglienna]

Enclose reserverd words in brackets [name]
Same with variables with spaces

David

 

[1x2z3]

Thanx...

I tried all of the following , but it gives me following Error : "Syntex error in INSERT INTO statement".

'cn is a connection, which is already established.

Dim cn As New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\Addresses.mdb"
cn.Open

cn.Execute "INSERT INTO Archive([Name], [Surname]) FROM SELECT Name, Surname FROM Addresses"
cn.Execute "INSERT INTO Archive([Name], [Surname]) FROM SELECT [Name], [Surname] FROM Addresses"
cn.Execute "INSERT INTO Archive(Name, Surname) FROM SELECT [Name], [Surname] FROM Addresses"
cn.Execute "INSERT INTO Archive[Name], [Surname] FROM SELECT [Name], [Surname] FROM Addresses"
cn.Execute "INSERT INTO Archive[(Name[, Surname])] FROM SELECT (Name[, Surname]) FROM Addresses"

Is their a other solution to solve this.

 

[tsdragon]

Try adding a space between Archive and (

You might also try removing the word "FROM"

Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[johnwm]

Looks like you need to lose the first FROM

JetSQL Help gives:
INSERT INTO target [(field1[, field2[, ...]])] [IN externaldatabase]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

You should find JetSQL help at "C:\Program Files\Common Files\Microsoft Shared\OFFICE11\1033\JETSQL40.CHM"

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[1x2z3]

THANX A LOT GUYS...

The following code worked perfectly with or Without the space between
Archive and (
I just removed the word "FROM" before SELECT as u said.

Acctually a nice way to transfer Only certain fields from 1 table to nother :
cn.Execute " INSERT INTO Archive(Name, Surname) FROM SELECT Name, Surname FROM Addresses”

JUST SOME USEFULL CODE TO OTHER USERS :

Try this to Transfer all your data with only the name JAN in it:
cn.Execute "INSERT INTO Archive SELECT * FROM Addresses WHERE Name = 'JAN'"

Try this to Transfer all your data from 1 table 2 nother.
cn.Execute "INSERT INTO Archive SELECT * FROM Addresses'"

Try this to add specific data to spesific fields in a table.
cn.Execute Adresses (Name,Surname,) VALUES ('Thanx', 'aLot')

 

[tsdragon]

Be aware that trying to insert values into ALL the fields in a record won't work if one of the fields is an autoincrement or identity field. If you have one of those you MUST specify the names of the fields to insert values into, and you must omit the autoincrement/identify field. VERY annoying if your table has a lot of fields.

Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[gmmastros]

This is only general info for others reading this thread, since the original poster already found a solution to their problem.

SQL allows you to insert a record without specify the list of fields ONLY when you insert data for all the fields. Since you cannot insert a value for an identity/auto increment you cannot insert all the fields, and the SQL will fail.

There are generally 2 ways of inserting data in to a table.

Insert Into TableName(Field1, Field2, Field3)
Values (Value1, Value2, Value3)

Or

Insert Into TableName(Field1, Field2, Field3)
Select Field1, Field2, Field3
From SomeOtherTable
Where FieldX = Condition

For example, let's say we have a table named People with fields of PersonId, Name, FavoriteFood. You can do either of the following.

Insert Into People
Values(1, 'George', 'Philly Cheese Steak')

Insert Into People(PersonId, Name, FavoriteFood)
Values(2, 'Tracy', 'Ice Cream')

Or Even...
Insert Into People(PersonId, Name, FavoriteFood)
Select 3, 'Mom', 'Spaghetti'

Here's the problem.... When you decide to use the first method (without the field names listed), you are opening yourself to potential problems later. When you first write the app, test it, it seems to work flawlessly. Then, 6 months later, your boss says, "Let's store shoe size for everyone." Being a good programmer, you decide to put the field in to the people table. The first Method will fail with "Insert Error: Column name or number of supplied values does not match table definition."

When you don't list the fields, SQL assumes that you are inserting every record and the order or the field matches the ordinal value of the fields.

I noticed that you are using Access. The following script is designed for SQL Server. You could run it on Access but you will have to change the data types. (integer to long and VarChar to text).

Create Table TestData(PersonId Integer, Name VarChar(100), FavoriteFood VarChar(100))

Insert Into TestData 
Values(1, 'George', 'Philly Cheese Steak')

Insert Into TestData(PersonId, Name, FavoriteFood)
Values(2, 'Tracy', 'Ice Cream')

Insert Into TestData(PersonId, Name, FavoriteFood)
Select 3, 'Mom', 'Spaghetti'

select * from TestData

Alter table TestData Add ShoeSize Decimal(3,1)
go

Insert Into TestData 
Values(4, 'Dad', 'Apple Pie')

Insert Into TestData(PersonId, Name, FavoriteFood)
Values(5, 'Bob', 'Donut')

Drop Table TestData

In my opinion, it is poor programming practice to NOT specify the field list in an insert into.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom