TSQL for Insert

Hello All,

I have to write a insert statement to insert the records from existing table with different TranID

Can anyone help me with the SQL statement.

Thanks,
-B

TranMaster
TrainID  ID   
80       1    
81       1    
82       1
92       4
93       4
94       4

TranDetails
TranDetails TrainID  ID   Remarks
1           80       1    aaa 
2           81       1    bbb
3           82       1    ccc

I need to add
4           92       4    aaa
5           93       4    bbb
6           94       4    ccc

These records need to be added in the Trandetails table where ID = 1 for ID = 4 but with the corrosponding TrainID from TranMaster (ID = 4)

Open in new window

BrookKAsked:
Who is Participating?
 
lofConnect With a Mentor Commented:
Ok...

It took a while but here is your solution. It is working however the mechanics of it may not be that obvious. You have to use MERGE statement to capture the newly generated keys from TranMaster table together with remarks which where not inserted.

I think it could be worth to write and article explaining how and why it works so please have a look at the code and if you need me to explain any part of it now, just let me know.

Oh, and if you could give me some more background it may help me with the article. How did you come to such problem, why are you copying the rows? Could you give more real life story to the query?
declare @newValues table (TrainId int, Id int, Remarks varchar(32))

merge TranMaster as target
using (
	select 
		 TrainId
		,4
		,Remarks 
	from TranDetails
	) as source(TrainId, Id, Remarks)
On (0=1)
WHEN NOT MATCHED THEN
	insert (Id)
	values (source.Id)
	output inserted.TrainId, source.Id, source.Remarks
	into @newValues;
	
insert into TranDetails 
select * from @newValues

Open in new window

0
 
lofCommented:
the following code with insert all missing TrainID into the details table

insert into TranDetails (TrainID, ID)
select TrainID, ID
from TranMaster where TrainId not in (select TrainId from TranDetails)
0
 
YZlatCommented:
can you please clarify what do you mean by  where ID = 1 for ID = 4 but with the corrosponding TrainID from TranMaster (ID = 4) ?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
BrookKAuthor Commented:
Thanks lof. It is not the missing once I need to insert. I need to insert all the records in TranDetails for TrainID = 4 where TranID = 1
0
 
YZlatCommented:
First, set TranDetails in TranDetails table to be incremented automatically - as identity seed

Then use the following INSERT INTO ... SELECT FROM :

INSERT INTO TranDetails(TrainID, ID)
SELECT TrainID, ID
FROM TranMaster
WHERE TrainID NOT IN (SELECT TrainID FROM TranDetails)

the above will insert

TranDetails TrainID  ID  
4          92       4    
5          93       4    
6          94       4    

into TranDetails table

I am still not sure where you are getting Remarks field from
0
 
YZlatCommented:
":Thanks lof. It is not the missing once I need to insert. I need to insert all the records in TranDetails for TrainID = 4 where TranID = 1"

Don't you mean you need to UPDATE TranDetails table?
0
 
YZlatCommented:
lof, sorry, I didn't see you already posted that. Didn't mean to replicate your post
0
 
lofCommented:
i see... but I am still not sure what you mean. What is the reason to put Master table in the example? If you Simply want to insert the same records into details table that code will do

if you want to insert

insert into TranDetails (TrainID, ID, Remarks)
select TrainID, 4, Remarks
from TranDetails where ID = 1

if, however, you need to generate new TrainIDs and that identity field in the Master table, let me know and I'll produce a sample code for you
0
 
lofCommented:
No problem YZlat, it happens to me sometimes as well.
0
 
BrookKAuthor Commented:
Lof,

The TranDetailsID is identity. In you code above the MainID of ID = 1 will get copied. I want the MainID of TranMaster..MainID where ID = 4 to be copied instead.

Thanks,
-B
0
 
YZlatCommented:
records with ID=1 won't be copied from TransMaster since they are already in TranDetails
WHERE TrainID NOT IN (SELECT TrainID FROM TranDetails)

takes care of that
0
 
BrookKAuthor Commented:
But it will copy all other records as well. I am interested only in copying the ID = 1 to ID = 4.

Your suggestion will copy all the records from Master. I am interested only in perticular records. And the Remarks column needs to be copied from TransDetails table where ID = 1 for ID = 4.

INSERT INTO TranDetails(TrainID, ID)
SELECT TrainID, ID
FROM TranMaster
WHERE TrainID NOT IN (SELECT TrainID FROM TranDetails)

Am I clear now? Sorry for the confusion guys.
0
 
lofCommented:
Ok, let's try to understand what you ask for...

what do you mean by copying only the ID = 1 to ID = 4.
 a) you want to copy the rows and in the meantime change value of ID from 1 to 4
 b) you want to copy records with value of ID between 1 and 4

what do you mean by 'I need to insert all the records _in_ TranDetails for...'
 a) you need to copy all the records _from_ TranDetails and insert them into TranDetails
 b) you need to copy records from TranMaster _into_ TranDetails

I still cannot figure our why do we need TranMaster in the example you provided
0
 
BrookKAuthor Commented:
Hi lof,

what do you mean by copying only the ID = 1 to ID = 4.
a) - correct
what do you mean by 'I need to insert all the records _in_ TranDetails for...'
I need to copy the records from TransDetails and insert them into TransDetails (columns - TransMaster..MainID, 4, TransDetails..Remark where TransMaster..ID = 4 and TransDetail..ID = 4)where ID = 1 for ID = 4. But at the same time I need to populate the MainID column from TransMaster where ID = 4 in TransDetails.

I need TransMater to get the MainID values for ID = 4.

Inserting only 3 records
It should insert these three rows.

4           92       4    aaa 
5           93       4    bbb 
6           94       4    ccc

Does that make sense?

Thanks,
-B

Open in new window

0
 
lofCommented:
Ok, now I believe I understand the question.
It will be more tricky than I thought.
I should have a sample ready soon.
0
 
BrookKAuthor Commented:
Sorry it took a while. No problem.

Thanks,
-B
0
 
BrookKAuthor Commented:
lof, Let me know if you have any further questions.

Thanks,
-B
0
 
lofCommented:
Did you see the code I produced?
And how about the real-life background to the problem?
0
 
lofCommented:
As I said I turned it into an article which you may find here

http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/How-to-duplicate-a-Master-Detail-structure.html

While writing it I have discovered that the code I posted here as a solution will work with your simple sample where master-detail relation is one-to-one only. In the article version I have modified it to handle one-to-many scenario as well.

I hope it is helpful
0
 
BrookKAuthor Commented:
Hi lof,

Sorry for the delay. I could not try your reply because it is in SQL Server 2005 not 2008.

The story behind this is, we use Microsoft TFS for the IT chnage requests, logs, issues etc. I had created reports to access this data during this phase I had to create my own small database with few tables and these two Master and Detail tables are part of it. Use wanted to copy the data from one Issue to another issue and then modify only the needed stuff. That is where this problem occured.

I will try to create this code in SQL Server 2005 as you said in your article by using cursor.

Thank you so much for all your help.
0
 
BrookKAuthor Commented:
I coudl not try this solution as it does not support on SQL 2005. But the user was very prompt and the solution could be correct if tried in SQL 2008.
0
 
lofCommented:
I missed that bit, that you use 2005.



declare @Remarks varchar(30)
declare @oldMasterId int
declare @newMasterId int
declare @oldAndNew table (old int, new int)


declare cur cursor for
select detailMaster, detailValue
from DetailsTable
where detailType = 'original'

open cur

fetch next from cur into @oldMasterId, @Remarks
while @@FETCH_STATUS = 0
begin
      if not exists(select * from @oldAndNew where old=@oldMasterId)
      begin
            insert into MasterTable values ('duplicate')
            set @newMasterId = @@IDENTITY  
            insert into @oldAndNew values (@oldMasterId, @newMasterId)
      end
      insert into DetailsTable values (@newMasterId, 'duplicate', @Remarks)
      
      fetch next from cur into @oldMasterId, @Remarks
end

close cur
deallocate cur

that's the cursor version based on tables from the article
0
 
BrookKAuthor Commented:
Thanks lof.

What if I donot want to enter the records in Master from Detail. I just wanted to insert the records from master to Detail. How woudl I get the @newMasterId? Right now it is coming from Master table once the record is inserted. But my case is only one way. Any solution?
0
 
lofCommented:
that is much simpler but in this case you won't have remarks or value

insert into DetailsTable (detailMaster, detailType, detailValue)
select masterId, masterType, null
from MasterTable
where (a condition here)
0
 
BrookKAuthor Commented:
I need to have remarks/description column to be populated as well.
0
 
lofCommented:
but if you will take data from MasterTable and not from DetailsTable you don't have the information about the remarks.
0
 
BrookKAuthor Commented:
That is when I got lost.

I need MainID from Master for ID = 4 and Remark from TransDetails where ID = 1.
0
 
lofCommented:
do the records from Details where ID=1 point with MainId to Master where ID=4?

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.