Insert with auto generate key - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Insert with auto generate key

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2009-03-13 : 01:45:30

I have this as a test:

Create Table #t([FirstName] nvarchar(50), [MiddleName] nvarchar(50), [LastName] nvarchar(50), [Initials] nvarchar(50), [LoginName] nvarchar(50), [LoginPassword] nvarchar(50), [IsDeleted] bit, [ProfileXMLFile] varbinary(MAX), [UserImageFileName] nvarchar(MAX), [UserNotes] nvarchar(MAX), [LastLoginTime] DateTime)
Insert into #t
Select [FirstName], [MiddleName], [LastName], [Initials], [LoginName], [LoginPassword], [IsDeleted], [ProfileXMLFile], [UserImageFileName], [UserNotes], [LastLoginTime]
From UserDetails

Select * From #t

****Works upto here*****

****In the database the field before [FirstName] is [UserID] this is an auto generated key. How do I do this below to insert into my UserDetails table a unique key followed by all my fields?*********

SET IDENTITY_INSERT dbo.UserDetails ON
Insert into UserDetails
Select [FirstName], [MiddleName], [LastName], [Initials], [LoginName], [LoginPassword], [IsDeleted], [ProfileXMLFile], [UserImageFileName], [UserNotes], [LastLoginTime]
From #t

Drop Table #t

Select * From UserDetails

Parses but I get this error when run:

Msg 545, Level 16, State 1, Procedure Import_from_Excel_to_DB, Line 42
Explicit value must be specified for identity column in table 'UserDetails' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-13 : 02:32:50

did u kept identity function for userid column or not? check it once

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2009-03-13 : 02:45:05

Sorry bklr I do not understand what you mean? The table UserDetails has a key called UserID. This is set to Identity (1,1).

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-13 : 02:58:09

why r u using set identity_insert tablename on

with out that u can insert the values in to userdetails

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2009-03-13 : 03:06:14

Yes, I tried without SET IDENTITY_INSERT dbo.UserDetails ON but it doesnt work.

Create Table #t([FirstName] nvarchar(50), [MiddleName] nvarchar(50), [LastName] nvarchar(50), [Initials] nvarchar(50), [LoginName] nvarchar(50), [LoginPassword] nvarchar(50), [IsDeleted] bit, [ProfileXMLFile] varbinary(MAX), [UserImageFileName] nvarchar(MAX), [UserNotes] nvarchar(MAX), [LastLoginTime] DateTime)
Insert into #t
Select [FirstName], [MiddleName], [LastName], [Initials], [LoginName], [LoginPassword], [IsDeleted], [ProfileXMLFile], [UserImageFileName], [UserNotes], [LastLoginTime]
From UserDetails

Select * From #t

****Works upto here but note exclude UserID from UserDetails as this is the auto generated number*****

****Below I just need to insert the fields from my temp table back into UserDetails so at the end of the day the UserDetails should have the data for all field duplicated but the second lot fromthe temp table with new UserID keys****

Insert into UserDetails
Select [FirstName], [MiddleName], [LastName], [Initials], [LoginName], [LoginPassword], [IsDeleted], [ProfileXMLFile], [UserImageFileName], [UserNotes], [LastLoginTime]
From #t

Drop Table #t

Select * From UserDetails

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-13 : 03:18:35

did u get any error with out identity_insert on
if u get any error post it once

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2009-03-13 : 22:04:12

This is the error I get:

(3 row(s) affected)

(3 row(s) affected)
Msg 2601, Level 14, State 1, Procedure Import_from_Excel_to_DB, Line 42
Cannot insert duplicate key row in object 'dbo.UserDetails' with unique index 'IX_UserDetails'.
The statement has been terminated.

(3 row(s) affected)

(1 row(s) affected)

Result set 1:
FirstName MiddleName LastName Initials LoginName IsDeleted Profile UserImageName UserNotes LastLogin
NULL NULL tester Xtralis_$777888 0 NULL NULL NULL NULL
NULL NULL Admin password 0 NULL NULL NULL NULL
NULL NULL test test 0 NULL NULL NULL NULL

Result set 2:
UserID (key) FirstName MiddleName LastName Initials LoginName IsDeleted Profile UserImageName UserNotes LastLogin
1 NULL NULL tester Xtralis_$777888 0 NULL NULL NULL NULL
2 NULL NULL Admin password 0 NULL NULL NULL NULL
3 NULL NULL test test 0 NULL NULL NULL NULL

UserID Settings:

Identity Specification Yes
Is Identity Yes
Indentity Increment 1
Identitiy Seed 1

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-14 : 03:01:44

do u have any unique constraint on any column in ur table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources