Set Based INSERT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Set Based INSERT

Author

Topic

CY
Starting Member

3 Posts

Posted - 2009-10-09 : 12:01:23

I am looking for a set based T-SQL solution to insert data into a parent table and related data in another table at the same time. The problem is not knowing the primary key values to link the data in related table. Below is a simplified version of what I'm trying to do. The temp table represents data gathered from a query from other tables in the database. The code values are not unique.

I have searched high and low for a solution but have not found anything. Any help is appreciated.


CREATE TABLE Parent
(
  ID INT IDENTITY(1,1),
  Code1 CHAR(1),
  CONSTRAINT PK_Parent_ID PRIMARY KEY (ID)
)

CREATE TABLE Related
(
  ID INT IDENTITY(1,1),
  ParentID INT,
  Code2 CHAR(2),
  CONSTRAINT PK_Related_ID PRIMARY KEY (ID),
  CONSTRAINT FK_Related_ParentID 
     FOREIGN KEY(ParentID) 
     REFERENCES Parent (ID)
)

CREATE TABLE #TmpOther
(
  Code1 CHAR(1),
  Code2 CHAR(2)
)

INSERT #TmpOther VALUES('A','A1')
INSERT #TmpOther VALUES('D','D2')
INSERT #TmpOther VALUES('J','J2')
INSERT #TmpOther VALUES('F','F3')

INSERT Parent
   SELECT Code1
   FROM   #TmpOther

SELECT * FROM Parent

-- Set based INSERT for table Related??

DROP TABLE #TmpOther
DROP TABLE Related
DROP TABLE Parent

insert into related(ParentId,Code2)
Select Parent.Id,
       Code2
  from Parent,
       #TmpOther
 where Parent.Code1 = #TmpOther.Code1

/* 
data for table Parent
ID   Code1
1    A
2    D
3    J
4    F

Desired data for table Related
ID   ParentID   Code2
1    1          A1
2    2          D2
3    3          J2
4    4          F3
*/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-09 : 14:17:20

I dont think there's an easier way in sql 2000. Are you by any chance using sql 2005?

CY
Starting Member

3 Posts

Posted - 2009-10-09 : 14:51:24

I am slowly coming to the same realization. Unfortunately I'm still in the Dark Ages - 2000!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-09 : 14:58:08

the only other option is to put your last logic inside trigger and do insert on child inside.but i myself don't like that approach and wont advise anyone to do that

CY
Starting Member

3 Posts

Posted - 2009-10-09 : 16:09:59

I did think about that approach, but like you, don't like it either. Thanks for your comments.

scott_leseman
Starting Member

5 Posts

Posted - 2009-10-15 : 12:17:44

What about adding an additional field to your Parent table such as a timestamp and selecting records based on the timestamp?

DECLARE @RUNDATE DATETIME
SET @RUNDATE = GETDATE()

.......

CREATE TABLE Parent
(
ID INT IDENTITY(1,1),
Code1 CHAR(1),
RUNDATE DATETIME,
CONSTRAINT PK_Parent_ID PRIMARY KEY (ID)
)

.......

INSERT Parent
SELECT Code1, @RUNDATE
FROM #TmpOther

.......

Select Parent.Id,
Code2
from Parent,
#TmpOther
where Parent.Code1 = #TmpOther.Code1
AND RUNDATE = @RUNDATE

...........

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources