Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DECLARE @MyTableVar TABLE (ID INT IDENTITY(1,1), SomeData NVARCHAR(300))
- DELETE FROM @MyTableVar
- insert into @MyTableVar Values("TestData")
- select * from @MyTableVar
- Insert @MyTableVar( Id, Value )
- Select Row_Number() Over ( Order By Value )
- , Value
- From SomeOtherTable
- Insert @MyTableVar( Id, Value )
- Select Row_Number() Over ( Order By Value )
- , Value
- From SomeOtherTable
- Insert @MyTableVar( Id, Value )
- Select Row_Number() Over ( Order By Value )
- , Value
- From SomeOtherTable
- SELECT * FROM @MyTableVar;
- SELECT ID = ROW_NUMBER() OVER (ORDER BY ID), SomeData FROM @MyTableVar;
- CREATE TABLE #MyTableVar (ID INT IDENTITY(1,1), SomeData NVARCHAR(300))
- insert #MyTableVar(SomeData) values ('test1'), ('test2')
- ---doesn't work
- DELETE FROM #MyTableVar
- insert #MyTableVar(SomeData) values ('test3'), ('test4')
- select * from #MyTableVar
- --resets the identity
- truncate table #MyTableVar
- insert #MyTableVar(SomeData) values ('test3'), ('test4')
- select * from #MyTableVar
- You cannot use TRUNCATE TABLE on tables that:
- Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)
- Participate in an indexed view.
- Are published by using transactional replication or merge replication.
- You cannot truncate a table variable.
- TRUNCATE TABLE
- [ { database_name .[ schema_name ] . | schema_name . } ]
- table_name
- [ ; ]
- declare @Mytablevar table
- (
- id int identity(1,1)
- ,id1 int
- somedata nvarchar(300)
- )
- -- insert your data as you would. After insert is finished, do the following:
- update @mytablevar set id1 = case when id > 250 then id % 250 else id end
- DBCC CHECKIDENT('TableName', RESEED, NewValue)
- DBCC CHECKIDENT(#MyTableVar, RESEED, 0)
- NewValue= NewIdentity-1;
- CREATE TABLE #TAB(ID INT IDENTITY,VALUE VARCHAR(10))
- DECLARE @RESEED INT = 32
- DBCC CHECKIDENT(#TAB,RESEED,@RESEED)
- INSERT INTO #TAB
- SELECT 'TEST'
- SELECT * FROM #TAB
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement