Bulk Insert Related Tables - PKs

Hi all,
I am in the process of building a SP to copy data from 3 temp tables into
production tables.
Say I have the following tables:
TempTable 1
TempTable 2
TempTable 3
TempTable 1 is related to TempTable 2 and TempTable 3 via a PK.
If I were to bulk Insert TempTable 1 into another table - how do I grab the
newly created PKs so I can fetch, bulk insert the related rows in TempTable
2 and TempTable 3?
I believe in SQLXML this feature is called ID propogation? Is it supported
in standard SQL?
Or do I basically have to loop through each of the entires in TempTable
2/3?
Thanks.Spam Catcher wrote:
> Hi all,
> I am in the process of building a SP to copy data from 3 temp tables into
> production tables.
> Say I have the following tables:
> TempTable 1
> TempTable 2
> TempTable 3
>
> TempTable 1 is related to TempTable 2 and TempTable 3 via a PK.
> If I were to bulk Insert TempTable 1 into another table - how do I grab th
e
> newly created PKs so I can fetch, bulk insert the related rows in TempTabl
e
> 2 and TempTable 3?
> I believe in SQLXML this feature is called ID propogation? Is it supported
> in standard SQL?
> Or do I basically have to loop through each of the entires in TempTable
> 2/3?
> Thanks.
Here's an example using Employees and Departments as the related
tables.
CREATE TABLE Departments (deptid INTEGER IDENTITY PRIMARY KEY, deptname
VARCHAR(30) NOT NULL UNIQUE);
CREATE TABLE Employees (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL, deptid
INTEGER NOT NULL REFERENCES Departments (deptid));
CREATE TABLE New_Departments (deptid INTEGER IDENTITY PRIMARY KEY,
deptname VARCHAR(30) NOT NULL UNIQUE);
CREATE TABLE New_Employees (employeeid INTEGER IDENTITY PRIMARY KEY,
ssn CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL, deptid
INTEGER NOT NULL REFERENCES New_Departments (deptid));
INSERT INTO New_Departments (deptname)
SELECT D.deptname
FROM Departments AS D ;
INSERT INTO New_Employees (ssn, employeename, deptid)
SELECT E1.ssn, E1.employeename, D2.deptid
FROM Employees AS E1
JOIN Departments AS D1
ON E1.deptid = D1.deptid
JOIN New_Departments AS D2
ON D1.deptname = D2.deptname ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
news:1144304833.967938.155300@.v46g2000cwv.googlegroups.com:

> Here's an example using Employees and Departments as the related
> tables.
> CREATE TABLE Departments (deptid INTEGER IDENTITY PRIMARY KEY,
> deptname VARCHAR(30) NOT NULL UNIQUE);
> CREATE TABLE Employees (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
> CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL, deptid
> INTEGER NOT NULL REFERENCES Departments (deptid));
> CREATE TABLE New_Departments (deptid INTEGER IDENTITY PRIMARY KEY,
> deptname VARCHAR(30) NOT NULL UNIQUE);
> CREATE TABLE New_Employees (employeeid INTEGER IDENTITY PRIMARY KEY,
> ssn CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL,
> deptid INTEGER NOT NULL REFERENCES New_Departments (deptid));
> INSERT INTO New_Departments (deptname)
> SELECT D.deptname
> FROM Departments AS D ;
> INSERT INTO New_Employees (ssn, employeename, deptid)
> SELECT E1.ssn, E1.employeename, D2.deptid
> FROM Employees AS E1
> JOIN Departments AS D1
> ON E1.deptid = D1.deptid
> JOIN New_Departments AS D2
> ON D1.deptname = D2.deptname ;
>
Thanks for your help. So this assumes the original tables had unique
data... what if I'm relying on the original temp PK to be unique - rather
than something like the department name?
In this case, would I have to loop over each record?
Thanks : )|||Spam Catcher wrote:
> Thanks for your help. So this assumes the original tables had unique
> data... what if I'm relying on the original temp PK to be unique - rather
> than something like the department name?
> In this case, would I have to loop over each record?
>
Uniqueness in the source data isn't essential - you can clear that up
with DISTINCT. Of course you do need alternate keys in the target
tables. You should always have those in any case. IDENTITY should not
be the only key of a table if you've got your logical design correct.
If you are forced to make a mess of it then looping is probably one way
to do it ;-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--