Solved

SQL Server Insert and Update a View

Posted on 2007-11-16
6
5,618 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 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Load Fact table in SQL Server SSIS package 14 47
sql how to insert a column with the data  in an existing table 4 36
SQL Query 9 29
Importing from CSV to SSMS 2 14
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

751 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