AutoIncrement Field in Select Into 1

[pollo4]

Hi!
Does anybody know how to add an autoincrement field in a Select Into statement?
This is what im doing, but doesn work:

SELECT email, AUTOINCREMENT(long) AS id
INTO NewTable
FROM UsersTable

Thanks in advance!

Pollo4

 

[jimmythegeek]

Your syntax looks correct, I ran the following statement, and it worked fine.

SELECT InputDate, InputID AS ID
INTO NewTable
FROM tblInput

Is AUTOINCREMENT the name of your AutoNumber field?

Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[pollo4]

No, what I want is to create an autoincremental field for the new table not using the input's one.

What I mean is, for example, if the input table is:

id email name
1 a@a.com abcd
2 b@b.com bcde
3 c@c.com efgh
4 d@d.com bcde

And the select statement is:

SELECT email, AUTOINCREMENT(long) AS id
INTO NewTable
FROM UsersTable
WHERE (name = 'bcde')

What I need is:

id email
1 b@b.com
2 d@d.com

If I do what you say the ids will be 2 and 4. I need that the id field counts the fields in the newtable.

Do you understand my problem?

Thanks a lot.
Pollo!

 

[jimmythegeek]

I understand what you are trying to do now. Unfortunately, you can't do it with an INSERT statement. I was going to suggest you Create a table using the CREATE TABLE method, then just append your records into it, but that method will not allow you to create an AutoNumber field in the new table, only a Long Integer.

The only way I can think of, is to create the new table with CREATE TABLE, pull a recordset of all the records you want to append to it, then loop through it inserting each time, a sequential variable that increments each time through the loop. It can be done, but it is kinda messy.

Is this a one time deal, or something you will be doing on a regular basis? If it will be repeated, will the table get dropped and recreated, or a new table created each time. If you are interested in this way of doing it, let me know, and we can walk through it.

Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[pollo4]

As a matter of fact I already solve it. I created the table with the Select Into and then add the autoincrement field with an Alter Table statement.
Thanks a lot anyway.

 

[r937]

actually, yes you can create an autonumber with access DDL, it's called COUNTER (it goes all the way back to access 2.0)

see Fundamental Microsoft Jet SQL for Access 2000

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acfundsql.asp

rudy