Insert Query - 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

Insert Query

Author

Topic

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2010-01-26 : 04:18:28

Hi experts,

I have 3 tables:

Table: Report
==================
ReportID: (PK) int, autoincrement
Title: varchar(50)

Table: Row
===================
RowID: (PK) int, autoincrement
ReportID: (FK to Header table)
RowText: varchar(50)

Table: RowItems
===================
RowItemsID: (PK), int, autoincrement
RowID: (FK to Row table)
RowItemsDetails : varchar(50)

I want to copy a Report with all corresponding Row(s) and RowItem(s) given a ReportID to copy from as an arguement

How do I do that?

Any help is very much appreciated. Thank you and have a wonderful day!

-------------------------------------------------------------------

P.S. My attempts below - for those who wants to know what I have tried where I am struck in.

I am able to do it automatically for the RowID

-- Assumes @ReportID is the parameter that is passed down

-- Copy from the @ReportID into the report table
Insert into Report (Title)
Select Title from Report where ReportID = @ReportID

-- Retrieve the latest ReportID
Declare @new_ReportID int
Set @new_ReportID = Select Max(ReportID) from Report

-- Copy the data for row
Insert into Row (ReportID, RowText)
Select @new_ReportID, RowText from Row where ReportID = @ReportID

Now I am struck - how do I get all the new IDs so that I can fill in the RowItems Table? The problem is that there could be multiple Rows for each Report and I could not get the new rowID Keys...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 04:25:15

can i ask why you're adding same report again to table?

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2010-01-26 : 04:32:56

Hi Visakh 16,

Thank you for your reply.

I want to copy an existing report - so that the management can use it.

For example, in a report "Balance Sheet year 2009" and "Balance Sheet year 2008" are similar except for the title, but is counted as two separate reports. Having the copy function will help to prevent the need to retype everything for the user.

Having them in separate reports helps to ensure that in the event that year 2009 has any changes (after I copied, I could edit the report), the report for year 2008 is still the same for backwards data retrieval.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 04:39:49

are you using sql 2005?

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2010-01-26 : 04:41:46

Hi Visakh16

Yes, I am using SQL 2005

:) Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 04:50:48

[code]
-- Assumes @ReportID is the parameter that is passed down

-- Copy from the @ReportID into the report table
Insert into Report (Title)
Select Title from Report where ReportID = @ReportID

-- Retrieve the latest ReportID
Declare @new_ReportID int
Set @new_ReportID = SCOPE_IDENTITY()
DECLARE @Inserted_Rows table
(
NewRowID int,
OldRowID int
)

-- Copy the data for row
Insert into Row (ReportID, RowText)
OUTPUT INSERTED.RowID,r.RowID INTO @Inserted_Rows
Select @new_ReportID, r.RowText from Row r
where r.ReportID = @ReportID

--Now row items
-- Copy the data for row
Insert into RowItems(RowID, RowItemsDetails)
Select r.NewRowID, ri.RowText
from RowItems ri
inner join @Inserted_Rows r
on r.OldRowID = ri.RowID
[/code]

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2010-01-26 : 04:57:18

Dear Visakh16,

Ahhh, A temporary table as a solution! I did not consider this option. Thank you for enlightening me! Now I understand how to resolve these types of problem in future.

Regards,

Eugene

check out my blog at http://www.aquariumlore.blogspot.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 05:04:26

you're welcome

Glad i could help you out

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2010-01-27 : 04:35:35

Hi

-- Copy the data for row
Insert into Row (ReportID, RowText)
OUTPUT INSERTED.RowID,r.RowID INTO @Inserted_Rows
Select @new_ReportID, r.RowText from Row r
where r.ReportID = @ReportID

This returns an error "Msg 4104, Level 16, State 1, Procedure CopyReport,
The multi-part identifier "r.RowID" could not be bound."

It looks fine to me, so I am not sure why this breaks. Any help is very much appreciated.

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2010-01-27 : 06:08:17

Is there any other workaround I can try so as to achieve the same objective of copying data? The above error means I am struck. I appreciate any suggestions, hints on how to resolve this problem and will cheerfully try it on my own if I got a clue on how to resolve this.

Thank you in advance for any help rendered.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources