Identities at the time of Import

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 Administration (2000)

Identities at the time of Import

Author

Topic

DOlivastro
Starting Member

41 Posts

Posted - 2005-08-04 : 10:48:01

Is it possible to import data and specify that an Identity should be added as a field?

For example, can I say that the output table should have a field "id" which is an identity with seed 1 and interval 1, and that this field has no corresponding field in the imported table?

If not, what is the best way to get this done? It seems I have to ...
1. Import the data
2. Create a second table
3. Add a field "id"
4. Insert all the records of the first table into the second.

Dom

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-04 : 11:00:08

Select identity(int,1,1) as Id , * into table2 from table1
Select * from table2

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-08-04 : 11:02:57

Something like:


CREATE TABLE MyTable
(
   MyID int identity(1,1) NOT NULL,
   MyCol1 int,
   MyCol2 int,
   PRIMARY KEY
   (
      MyID
   )
)
GO
INSERT INTO MyTable
(
   MyCol1,
   MyCol2
)
SELECT 100, 101 UNION ALL
SELECT 200, 201 UNION ALL
SELECT 300, 301
GO
SELECT *
FROM MyTable
GO
DROP TABLE MyTable
GO

Kristen

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-08-04 : 13:46:06

After you import, this works too I think

ALTER TABLE MyTable
ADD [ID] IDENTITY(1,1)

Don't let Brett see us using surrogate keys...

====================================================
Regards,
Sean Roussy

GENERAL ADVICE FOR EVERYONE: Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources