Solved

MS SQL 2008 Adding Records to multiple tables at once

Posted on 2013-02-05
8
114 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
  • 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now