Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server Insert and Update a View

Posted on 2007-11-16
6
Medium Priority
?
5,627 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

661 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