can not use truncate, need workaround for it

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)

can not use truncate, need workaround for it

Author

Topic

zion99
Posting Yak Master

141 Posts

Posted - 2008-08-05 : 01:30:32

Hi,
i have used truncate in my sql queries. unfortunately, i have come to know that truncate is not allowed in prod. environment. hence i will have to make change in my queries.

i use a temp table with one identity column say IDEN1 (which is also the primary key). Before loading from Source happens, this table is truncated everytime. hence the seed becomes zero.

then using this identity column IDEN1, i compare the records of this temp table with main table & update the main table.
Main table is the exact replica of temp table; the only exception being IDEN1 in main table is NOT an identity column.

if i use delete, seed wont become zero.
Can i use some auto-incrementer in the SELECT query itself as in

insert into TempTbl
select AUTO-INC, col1, col2 FROM SOURCE

a)this Auto-INC will always start from 1 and its last value will be the total count of records in Source table
b)this Auto-INC will also be copied to Main table.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-05 : 01:54:13

You can use Row_Number()

Webfred