Problem inserting identity column

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

Transact-SQL (2008)

Problem inserting identity column

Author

Topic

dips255
Starting Member

17 Posts

Posted - 2011-01-19 : 06:35:06

I have a trigger (instead of insert) on the table users which does some validations and then inserts

DECLARE @ColumnList varchar(1000)
SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + COLUMN_NAME
FROM information_schema.columns WHERE table_name = 'Users' AND COLUMN_NAME<>'LOGINID' ORDER BY ORDINAL_POSITION

EXEC ('INSERT INTO dbo.USERS (' + @ColumnList + ') SELECT ' + @ColumnList + ' FROM inserted')

I get this run time error on above line
Msg 208, Level 16, State 1, Line 1
Invalid object name 'inserted'.

when i use this
SET IDENTITY_INSERT DBO.USERS ON
INSERT INTO USERS SELECT * FROM INSERTED
SET IDENTITY_INSERT DBO.USERS OFF

it gives error
An explicit value for the identity column in table 'USERS' can only be specified when a column list is used and IDENTITY_INSERT is ON.

please help

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-01-19 : 07:24:57

http://connectsql.blogspot.com/2010/12/sql-server-insert-explicit-value-for.html

--------------------------
http://connectsql.blogspot.com/

dips255
Starting Member

17 Posts

Posted - 2011-01-20 : 00:38:42

I tried with 'SELECT * INTO #INS FROM INSERTED'
now i get this error
INSTEAD OF triggers do not support direct recursion. The trigger execution failed.

alter TRIGGER UserValidate on USERS
INSTEAD OF INSERT
AS
begin
declare @Login varchar(20),@pass varchar(12),@name varchar(75),@city varchar(30),@country varchar(50),@email varchar(75),@mobile varchar(15),@category char(1),@err bit
SELECT * INTO #INS FROM INSERTED

set @err = 0
select @LoginName = loginName from #ins
select @pass = password from #ins
select @name = name from #ins
select @city = city from #ins
select @country = country from #ins
select @email = email from #ins
select @mobile = mobile from #ins
select @category = category from #ins

if (@LoginName = '' or @pass='' or @name='' or @category='' or @city='' or @country='' or (@email='' and @mobile=''))
begin
rollback
RAISERROR ('Required data missing', 16, 10)
set @err = 1
end

if exists(select loginid from users where loginName=@loginName)
begin
rollback
RAISERROR ('Login Name already exists', 16, 10)
set @err = 1
end

if @email<>''
begin
if exists(select loginid from users where email=@email )
begin
--print 'duplicate email'
rollback
RAISERROR ('Email ID already exists', 16, 10)
set @err = 1
end
end

if @mobile<>''
begin
if exists(select loginid from users where mobile=@mobile )
begin
--print 'duplicate mobile'
rollback
RAISERROR ('Mobile number already exists', 16, 10)
set @err = 1
end
end

if @err = 0
begin
print 'commit'

DECLARE @ColumnList varchar(2000)
SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + COLUMN_NAME
FROM information_schema.columns WHERE table_name = 'Users' AND COLUMN_NAME<>'LOGINID' ORDER BY ORDINAL_POSITION

EXEC ('SET IDENTITY_INSERT DBO.USERS ON; INSERT INTO dbo.USERS (' + @ColumnList + ') SELECT ' + @ColumnList + ' FROM #ins; SET IDENTITY_INSERT DBO.USERS OFF')

end

end

quote:
Originally posted by lionofdezert

http://connectsql.blogspot.com/2010/12/sql-server-insert-explicit-value-for.html

--------------------------
http://connectsql.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources