SQL Server Insert and Update a View

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.
sowikiAsked:
Who is Participating?
 
k_rasuriCommented:
I still didnt understand what you getting at..but seems like a Trigger can work well for this case. Below is the example

CREATE TRIGGER ut_Trigger1
ON Table1
FOR update AS
    IF (COLUMNS_UPDATED() & 6) > 0
   BEGIN
    INSERT INTO NewTable1
        SELECT 'OLD', del.Date, del.[Name], del.Address FROM inserted insl
   END

when ever you insert some records in Table1, the new table NewTable1 will get the column values you want
0
 
k_rasuriCommented:
can u put some sample data and also how you want your results to look like.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sowikiAuthor Commented:
Thank you for the reply EugeneZ and k rasuri-

EugeneZ:
I'll look deeper at the links, but I'm not sure if it's exactly what I'm looking for. I already have existing Views, and (I think) I've been able to Insert a new row. Sorry for the "(I think)",  but I've tried a lot of variations and the brain is getting muddled. :) The problem is grabbing the new row for update. I don't have a column to compare @@Identity with. When I SELECT * FROM ViewName  it "looks" like I have duplicate rows.

k rasuri:
Even a sample data would be too much to post. My steps are.. Make a copy of a row, then update some columns in the new row with new values.
0
 
sowikiAuthor Commented:
Correction to previous, a new row is not being inserted. Trigger is sqawking about PK constraint.
0
 
sowikiAuthor Commented:
Solutions accepted because the information is valid. However, the database I'm working with required a different approach. Thanks to everyone that responded.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.