Advertisement
Guest User

Untitled

a guest
Oct 21st, 2014
271
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.92 KB | None | 0 0
  1. DECLARE @MyTableVar TABLE (ID INT IDENTITY(1,1), SomeData NVARCHAR(300))
  2.  
  3. DELETE FROM @MyTableVar
  4.  
  5. insert into @MyTableVar Values("TestData")
  6. select * from @MyTableVar
  7.  
  8. Insert @MyTableVar( Id, Value )
  9. Select Row_Number() Over ( Order By Value )
  10. , Value
  11. From SomeOtherTable
  12.  
  13. Insert @MyTableVar( Id, Value )
  14. Select Row_Number() Over ( Order By Value )
  15. , Value
  16. From SomeOtherTable
  17.  
  18. Insert @MyTableVar( Id, Value )
  19. Select Row_Number() Over ( Order By Value )
  20. , Value
  21. From SomeOtherTable
  22.  
  23. SELECT * FROM @MyTableVar;
  24.  
  25. SELECT ID = ROW_NUMBER() OVER (ORDER BY ID), SomeData FROM @MyTableVar;
  26.  
  27. CREATE TABLE #MyTableVar (ID INT IDENTITY(1,1), SomeData NVARCHAR(300))
  28.  
  29. insert #MyTableVar(SomeData) values ('test1'), ('test2')
  30.  
  31. ---doesn't work
  32. DELETE FROM #MyTableVar
  33.  
  34. insert #MyTableVar(SomeData) values ('test3'), ('test4')
  35. select * from #MyTableVar
  36.  
  37. --resets the identity
  38. truncate table #MyTableVar
  39. insert #MyTableVar(SomeData) values ('test3'), ('test4')
  40. select * from #MyTableVar
  41.  
  42. You cannot use TRUNCATE TABLE on tables that:
  43.  
  44. Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)
  45.  
  46. Participate in an indexed view.
  47.  
  48. Are published by using transactional replication or merge replication.
  49.  
  50. You cannot truncate a table variable.
  51.  
  52. TRUNCATE TABLE
  53. [ { database_name .[ schema_name ] . | schema_name . } ]
  54. table_name
  55. [ ; ]
  56.  
  57. declare @Mytablevar table
  58. (
  59. id int identity(1,1)
  60. ,id1 int
  61. somedata nvarchar(300)
  62. )
  63.  
  64. -- insert your data as you would. After insert is finished, do the following:
  65.  
  66. update @mytablevar set id1 = case when id > 250 then id % 250 else id end
  67.  
  68. DBCC CHECKIDENT('TableName', RESEED, NewValue)
  69.  
  70. DBCC CHECKIDENT(#MyTableVar, RESEED, 0)
  71.  
  72. NewValue= NewIdentity-1;
  73.  
  74. CREATE TABLE #TAB(ID INT IDENTITY,VALUE VARCHAR(10))
  75. DECLARE @RESEED INT = 32
  76. DBCC CHECKIDENT(#TAB,RESEED,@RESEED)
  77. INSERT INTO #TAB
  78. SELECT 'TEST'
  79. SELECT * FROM #TAB
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement