October 8, 2007 at 8:51 am
Hello,
I have a table with order information (name, shipping info,item qty etc...) in one table.
I need to insert this info into 2 tables (master - detail) the master table has a primary key - an auto-generated ID
Some info for e.g., name, shipping info needs to go to the master table.
other info - item, qty etc... need to go to the detail table.
i would greatly appreciate if you could give me some help on how i could do this using a DTS package.
Many Thanks in advance!
adi
October 8, 2007 at 10:41 am
Just curious, why does it have to be through DTS?.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 8, 2007 at 10:44 am
sorry i put DTS in there - i'm open to any suggestions.
Thanks,
Adi
October 8, 2007 at 10:48 am
If you post your table structure for all three tables, and what you want to go where, it should be easy enough to figure out an insert trigger to capture the data realtime.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 8, 2007 at 10:59 am
Greg,
Here's a gist of the table structure -
[Source Table]
{Id}{Name}{Addr}{City}{State}{Zip}{Item}{Qty}
[Destination Tables]
[Master table]
{Id}{Name}{Addr}{City}{State}{Zip}
[Detail table]
{Id}{Item}{Qty}
Many Thanks!
October 8, 2007 at 12:36 pm
I made some assumptions on what you need to do, based on what little info you gave. Is this a new database you are setting up? If so I would suggest you read up on database design. And a major caveat: I am a relative beginner, so anything I tell you may be wrong, but it is a slow day for me so I figured what the heck. If this does not help you please post back with more details on what exactly you need to do. Copy and past the entirety of the below code in QA and run it.
--Create the test tables as per you description. I made
--assumptions as to the data types
IF OBJECT_ID('Master','u') IS NOT NULL
DROP TABLE Master
IF OBJECT_ID('Details','u') IS NOT NULL
DROP TABLE Details
IF OBJECT_ID('Source','u') IS NOT NULL
DROP TABLE Source
--Source table
CREATE TABLE Source
(
ID INT IDENTITY(1,1),
NAME VARCHAR(50) NOT NULL,
ADDRESS VARCHAR(50) NOT NULL,
CITY VARCHAR(50) NOT NULL,
STATE CHAR(2) NOT NULL,
ZIP VARCHAR(10) NOT NULL,
ITEM VARCHAR(50) NOT NULL,
QUANTITY INT NOT NULL,
PRIMARY KEY (ID)
)
--Master table
CREATE TABLE Master
(
ID INT IDENTITY(1,1),
NAME VARCHAR(50) NOT NULL,
ADDRESS VARCHAR(50)NOT NULL,
CITY VARCHAR(50) NOT NULL,
STATE CHAR(2) NOT NULL,
ZIP VARCHAR(10) NOT NULL,
PRIMARY KEY (ID)
)
--Details table
CREATE TABLE Details
(
ID INT IDENTITY(1,1),
ITEM VARCHAR(50) NOT NULL,
QTY INT NOT NULL,
PRIMARY KEY(ID)
)
GO
--Create an INSERT trigger on Source table to insert new entries
--into Master table. You can expand it to also insert new
--entries into Details table. Probably not the best way to do,
--but it works.
IF OBJECT_ID('tr_Source_Insert','tr') IS NOT NULL
DROP TRIGGER tr_Source_Insert
GO
CREATE TRIGGER tr_Source_Insert
ON Source
FOR INSERT
AS
BEGIN
SELECT *
INTO #Ins
FROM Inserted
INSERT INTO Master(Name,Address,City,State,Zip)
SELECT
--I am assuming you want to create some sort of master "Customers" table?
--If so I would suggest you add a column for Street, Street Suffix(st,rd.etc),
--house number, and unit(101,102,etc). When you run the code you will see why.
--"Bob" has made two orders, but he put different entries for his street, thereby
--creating two entries for him in the Master table. You should also split the name
--column into First, Last, Middle.
i.Name,
i.Address,
i.City,
i.State,
i.Zip
FROM #Ins i LEFT OUTER JOIN
Master m
ON i.Name = m.Name
AND i.Address = m.Address
AND i.Zip = m.Zip
WHERE m.Name IS NULL
AND m.Address IS NULL
AND m.Zip IS NULL
DROP TABLE #Ins
END
GO
INSERT INTO Source
SELECT 'Bob','1 Maple Ave','MyTown','VA','20175','Fried Hog Jowls','100' UNION ALL
SELECT 'Ann','2 Maple Ave.','MyTown','VA','20175','Fat Back','20' UNION ALL
SELECT 'Joe','1 Oak St','MyTown','VA','20175','Tripe','10' UNION ALL
SELECT 'Jill','2 Oak Street','MyTown','VA','20175','Beeg Tongue','30' UNION ALL
SELECT 'Amy','3 Oak St.','MyTown','VA','20175','Fried Hog Jowls','1'
SELECT *
FROM Master
ORDER BY Name
GO
INSERT INTO Source
SELECT 'Bob','1 Maple Avenue','MyTown','VA','20175','Pickled Pig Knuckles','20'
SELECT *
FROM Master
ORDER BY Name
--See the two entries for Bob?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 8, 2007 at 8:46 pm
Greg,
Why do you need #Ins?
It does not add anything, just causes another recompilation of the trigger.
_____________
Code for TallyGenerator
October 8, 2007 at 10:36 pm
Sergiy, I kept getting strange errors when I tried to insert directly from the inserted table. Now I wish I had made note of the errors. I do remember that in the results pane it would say '1 row affected' around 30 times in a row, then the error message, even when I only inserted the sixth record for Bob, so when I inserted into temp table errors went away. I will try to replicate what was happening tomorrow so I can be more specific. Should I be able to say something like the below? In essence treating the inserted(or deleted) tables like a normal table, as long as it it within the trigger? Assuming what I did is what the poster wanted, what would be the best way to do it? It seemed to me the way the original post was worded the only desire was to split the Source table into two separate tables, and if all fields are NOT NULL what would be the point?
INSERT INTO Master (Name,Address,City,State,ZIP)
SELECT
i.Name,
i.the rest
FROM Inserted I
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 8, 2007 at 11:48 pm
Sergiy is right why do u need #ins. BTW what about existing data ?
"Keep Trying"
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply