Solved

MS SQL 2008 Adding Records to multiple tables at once

Posted on 2013-02-05
8
119 Views
Last Modified: 2013-09-11
I am trying to build two tables (Table1 and Table2) from Master table all at once.  When a record is added to Table1 and the Identity field is populated I need that column, t1.Id, to be populated into t2.IdTable1 column, plus different fields from the Master table will fill columns in Table1 and Table2.

Does this make sense?  If you need further explination please let me know.

Thanks in advance for the help.

John
0
Comment
Question by:j_heck
[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
  • 4
  • 3
8 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38856176
not really why are you duplicating data....

is table2 in another database ... have you considered replication ?

or are you wanting to  have a trigger to populate/update this additional table...?
0
 

Author Comment

by:j_heck
ID: 38856563
Lowfatspread, I am not duplicating data.  Some of the columns in the Master are going into Table1 and a few others are going into Table2 (Table1 and Table2 are in a diffrent system and are used in different applications - trying to allow users to see the data using Table1 and Table2 in there old application while the new one is being built).  Table1 and Table2 are joined together by Table1.Id.

Does this help?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38856732
then consider maintaining the data via replication ...

will all updates to the table1 /2 data be from the same source is the data read-only in the existing applications?

are the tables in databases on the same instance?
(if on different instances are different version/editions of sql server involved?)
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:j_heck
ID: 38856887
Different servers are involved but that is not the problem.  The system I am trying to update is an older system.  Currently Table1/2 are built using loop code and when a Record in Table1 is written a record in Table2 is written using the ID (primary key and an identity) column in Table1 in Table2 for the link between the two tables.  That is what I am trying to accomplish using SQL?  Does this make sense or am I over thinking it?  

I can not change any of the date or definitions in Table1/2
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 38857294
possibly you are overthinking this ...

if you are trying to just have the one interaction with sql for the insert or combine several inserts into 1 (you want to insert 5 rows into table1 and have table2 automatically updated as a single action rather than the current loop)

then , and this is not Ideal if multiple databases/servers are involved you could either have a trigger coded on table 1 which will detect the changes and make the appropriate changes to table 2 for you ie. you app does 1 interaction with the database and behind the scenes the database the handles the updates...

or you can make your multiple inserts to Table1 and have an OUTPUT clause on the Insert statement which will case a Table of the "key" values you desire to be written
you can then reference that table when you make the insert for table2

or you can insert to table1 and set up replication from table1 to table2 which will occur in near real time after the end of the current unit of work (this would also give you application some indepence of the availability of table2 ... if you don't have to directly access that database for other reasons in the app..)
0
 

Author Comment

by:j_heck
ID: 38869391
I am trying to use the OUTPUT clause because that will allow me to create a record that will hold the ID field that I will need for the second table.  Though I am running into a problem in getting the StoreNumber into OUTPUT clause.  Where am I going wrong?

I have put a StoreNumber field in the output table, it is in the SELECT...FROM clause, and I need it to make sure I am grabbing the correct record when I build the BillBackStore table.
The link between the BillBackUPC and BillBackStore table is the BBUID column.  The OUTPUT clause does not like the Deleted.StoreNumber or even bbr.StoreNumber.

DECLARE @InsertOutput1 table
(
  BBUID int,
  BBPID int,
  UPC decimal(20,0),
  StoreNumber int,
  BeginDate Date,
  EndDate date,
  MfgName varchar(25),
  BBGrpId int  
);

INSERT INTO BillBackUPC
           ([BBPID]
           ,[UPC]
           ,[MFG]
           ,[Category]
           ,[Description]
           ,[Description2]
           ,[BeginDate]
           ,[EndDate]
           ,[Allowance]
           ,[BBGRPID])
Output
      Inserted.BBUID,
      Inserted.BBPID,
      inserted.UPC,
      deleted.storenumber,
      inserted.BeginDate,
      inserted.EndDate,
      inserted.Allowance,
      inserted.BBGRPID
  into @InsertOutPut1
SELECT top(10) 21
      ,UPC
      ,MfgName
      ,Category
      ,[Description]
      ,[description2]
      ,BeginDate
      ,EndDate
      ,Allowance
      ,0
  FROM BillBackRun bbr
where AdStartDate = '12/27/2012'
      and AdEndDate = '1/23/2013'
      and StoreNumber = 3
      and bbr.ppcbbgrpid = 0


BillBackStore table definition
           BBPID
           ,StoreNumber
           ,BBUID
           ,TotalQty
           ,BillBackAmt


Thanks.
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 38970680
First, DELETED values come from the old record when an UPDATE occurs, it is not available on an INSERT.
Second, you are not inserting StoreNumber so where is it supposed to be coming from?
0
 

Author Comment

by:j_heck
ID: 39131974
CGLuttrell,  you are correct, I should be getting the StoreNumber from BillBackRun table since that is where it comes from for the SELECT statement.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

734 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