Using a stored procedure on SQL Server, I need to create a new business account from an existing account. With the exception of some columns, the new account will have the same data as the old account.
The basic functionality I'm trying to achieve is:
Insert into xTable Select * from xTable where acctID=@oldAcctID
Update xTable Set acctID=@newID, ... Where __?__ = @@Identity
I have single tables, plus Views consisting of numerous tables. Many tables have 200+ columns each (makes a nasty column list when going that route). I have an Identity column, but not a unique ID column I can query. Can I Insert and update a new row through a View? Do I need to Insert/Update each table in a View individually? I know someone out there has done this already. Although I haven't found a workable answer in previous questions posted.