Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 555
  • Last Modified:

MSaccess Select identity

Hello folks I've this statment and I need to retrieve the identity after I do an SELECT INTO. Im using UNION ALL

Thanks for any help
SELECT  IDENTITY(int, 1, 1) as aa, * 
INTO newTable
FROM (Table A
UNION ALL
TABLE B)

Open in new window

0
nkoriginal
Asked:
nkoriginal
2 Solutions
 
Daniel WilsonCommented:
please try:

SELECT  @@IDENTITY as aa, * 
INTO newTable
FROM (Table A
UNION ALL
TABLE B)

Open in new window

0
 
nkoriginalAuthor Commented:
Thanks.!!
It's works but the column "aa" has a ZERO value.
How I can do to insert in that column (aa) an autoincrement value??

Thanks
0
 
peter57rCommented:
There isn't any way to specify an autoincrement field inan Access Make-table query.
You would have to run another quer/ execute another SQL string afterwards to Alter the table and column to set the autoincrement property.

ALTER TABLE table_name ALTER COLUMN column_name Counter

Alternativley use a pre-built table which includes an autonumber field, and use delete and append queries instead.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now