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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-05-18 : 07:33:01
|
| Benjamin writes "Hi to all Sifu out there,i have when through alot of site and unable to find a solution.I have a Prod table with SerialNo int Identity(1,1), ProdName, Char(10), Date Datetimeand ArchiveProd SerialNo Int, Prodname Char(10),Date Datetime.Im able to move all data from Prod to ArchiveProd for year 2002 and delete record of year 2002 from Prod.Problem arise when i want to restore from ArchiveProd to Prod for the year 2002 that is the SerialNo Identity. Im trying to create TSQL/ SP to run the restoration.this is a sample script that can by pass the Identity function but only for one transaction a time.-------------------------------set identity_insert Prod oninsert Prod (SerialNo, ProdName, [Date]) values (101, "ABC", '2002-05-01')--------------------------------when i use it this way, it doesnt work.----------------------------------set identity_insert Prod oninsert Prod select * from ArchiveProd-----------------------------------Could anyone help me on this?thanks" |
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2005-05-18 : 09:18:44
|
| Do the two tables have the same fields?Aj |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-05-18 : 09:50:05
|
| set identity_insert Prod oninsert Prod (SerialNo, ProdName, [Date]) select (SerialNo, ProdName, [Date]) from ArchiveProdset identity_insert Prod off==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Using Insert Select into a table that have IDENTITY