Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server Insert and Update a View

Posted on 2007-11-16
6
Medium Priority
?
5,636 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 43

Assisted Solution

by:Eugene Z
Eugene Z earned 600 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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 900 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

916 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