Solved

SQL Server Insert and Update a View

Posted on 2007-11-16
6
5,613 Views
Last Modified: 2010-04-21
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.
0
Comment
Question by:sowiki
  • 3
  • 2
6 Comments
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 200 total points
ID: 20300166
0
 
LVL 8

Expert Comment

by:k_rasuri
ID: 20300169
can u put some sample data and also how you want your results to look like.
0
 

Author Comment

by:sowiki
ID: 20300371
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:sowiki
ID: 20300553
Correction to previous, a new row is not being inserted. Trigger is sqawking about PK constraint.
0
 
LVL 8

Accepted Solution

by:
k_rasuri earned 300 total points
ID: 20300786
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
 

Author Closing Comment

by:sowiki
ID: 31409598
Solutions accepted because the information is valid. However, the database I'm working with required a different approach. Thanks to everyone that responded.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question