Insertion Error - 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

General SQL Server Forums

New to SQL Server Programming

Insertion Error

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-07 : 00:06:37

Hi,
I am making insertion into a table from other db.
now i have written a sp which fetches rows one by one and inserts into master and detail tables.
the problem is, the name column has duplicate vakues (say a single company may have multiple addresses).
i have to now store the name in the master only once and in details it must repeat. what condition can i give?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 00:32:14

Are you using SQL 2005?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-07 : 00:36:19

yes m using sql 2005
i am using cursor.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 00:53:01

no need of cursor, you can use set based solution as follows


DECLARE @INSERTED_ITEMS
(
ID int,
Name varchar(100),
.. other fields that make up primary key of your table
)

INSERT INTO Master
OUPUT INSERTED.ID,INSERTED.Name, INSERTED.other columns... INTO @INSERTED_ITEMS
SELECT DISTINCT Name,
other master value columns..
FROM OtherDb.dbo.Table

INSERT INTO Child
SELECT i.ID,t.other values... 
FROM @INSERTED_ITEMS i
JOIN OtherDb.dbo.Table t
ON t.Name= i.Name
AND ... other columns that were added to @INSERTED_ITEMS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-07 : 01:19:50

thanks visakh .. but how do i insert values into that temp table now?

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-07 : 01:21:21

can you explain that declaration of inserted_items?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 01:23:40

quote:
Originally posted by mrm23

can you explain that declaration of inserted_items?

its a temporary table to capture the new id generated for records after insertion to master table. you dont require any special inserts. just use OUPUT operator as shown in your master insert to populate the values into this temporary table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources