Solved

MS SQL 2008 Adding Records to multiple tables at once

Posted on 2013-02-05
8
115 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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
GRANT, REVOKE, DENY 4 38
Script to randomly create characters in MS SQL 6 32
BULK INSERT most recent CSV 19 45
how to install/upgrade the Blitz responder kit 8 29
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

863 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

24 Experts available now in Live!

Get 1:1 Help Now