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.
| Author |
Topic |
|
Rakhi
Starting Member
2 Posts |
 Posted - 2007-09-10 : 04:23:41
|
| Hi,I am facing problem while inserting data in a table having identity seed. DECLARE @varSQL VARCHAR(8000)DECLARE @VARe VARCHAR(400)SET @VARe = 'Tmp_r429_sig' SET @varSQL ='SET IDENTITY_INSERT ' + @VARe + ' ON' EXEC (@varSQL) INSERT INTO Tmp_r429_sig ([NAME],ID) VALUES('test',1) SET @varSQL ='SET IDENTITY_INSERT ' + @VARe + ' OFF' EXEC (@varSQL)The table name need to be a variable.I ma getting following error:Server: Msg 544, Level 16, State 1, Line 1Cannot insert explicit value for identity column in table 'Tmp_r429_sig' when IDENTITY_INSERT is set to OFF.Can someone throw some light on how to execute SET IDENTITY_INSERT as a dynamic query. |
|
|
PeterNeo
Constraint Violating Yak Guru
357 Posts |
Posted - 2007-09-10 : 05:06:20
|
| Hi u can't execute them in sepate.try some thing like DECLARE @varSQL VARCHAR(8000)DECLARE @VARe VARCHAR(400)SET @VARe = 'Tmp_r429_sig'SET @varSQL ='SET IDENTITY_INSERT ' + @VARe + ' ON INSERT INTO Tmp_r429_sig ([NAME],ID) VALUES(''test'',1) SET IDENTITY_INSERT ' + @VARe + ' OFF'EXEC (@varSQL)but, y do u want dynamic query for this |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-10 : 06:16:26
|
Because table name is variable.Keep both statements in same execution!DECLARE @varSQL VARCHAR(8000)DECLARE @VARe VARCHAR(400)SET @VARe = 'Tmp_r429_sig'SET @varSQL ='SET IDENTITY_INSERT ' + @VARe + ' ON;'set @varSQL = @varSQL + 'INSERT INTO ' + quotename(@vare) + ' ([NAME],ID) VALUES(''test'',1)'EXEC (@varSQL)
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Rakhi
Starting Member
2 Posts |
Posted - 2007-09-10 : 06:53:56
|
| Thnaks PeterNeo !!It worked.I need to this for many tables and want it to be generalised.Thanks |
|
|
|
|
|
|
problem with SET IDENTITY_INSERT