Unique identifiers on my records...? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Unique identifiers on my records...?

Author

Topic

jfieck
Starting Member

6 Posts

Posted - 2005-06-02 : 19:44:51

I am working on trying to write a SQL statement in a MS Access 2000 query that will create and populate a new table with all the records returned by another query. The catch is that I need the new table to uniquely identify each record. The old table doesn't have unique identifiers on it's records. The syntax on this is killing me.

Psuedo scenario:
If I had a query called 'getData' that returned this table
FIRSTNAME LASTNAME
John Do
Jane Do

Can I do something like:
create table #temptable( uniqueid , getData )

and now have a table called #temptable that would look like this:
ID FIRSTNAME LASTNAME
1 John Do
2 John Do

?? Please help if you can. Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-02 : 20:00:03

You can use the IDENTITY function to do this via SELECT INTO.

SELECT IDENTITY(1, 1, int) AS NewTableID, FIRSTNAME, LASTNAME
INTO NewTable
FROM Table1
WHERE...

Another way:

CREATE TABLE NewTable (NewTableID int IDENTITY(1, 1), FIRSTNAME nvarchar(256), LASTNAME nvarchar(256))

INSERT INTO NewTable (FIRSTNAME, LASTNAME)
SELECT FIRSTNAME, LASTNAME
FROM Table1
WHERE...

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources