?
Solved

TSQL for Insert

Posted on 2009-12-29
28
Medium Priority
?
678 Views
Last Modified: 2012-05-08
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

0
Comment
Question by:BrookK
  • 12
  • 11
  • 5
28 Comments
 
LVL 10

Expert Comment

by:lof
ID: 26140895
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
 
LVL 35

Expert Comment

by:YZlat
ID: 26140908
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
 

Author Comment

by:BrookK
ID: 26140917
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 35

Expert Comment

by:YZlat
ID: 26140938
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
 
LVL 35

Expert Comment

by:YZlat
ID: 26140958
":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
 
LVL 35

Expert Comment

by:YZlat
ID: 26140969
lof, sorry, I didn't see you already posted that. Didn't mean to replicate your post
0
 
LVL 10

Expert Comment

by:lof
ID: 26140981
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
 
LVL 10

Expert Comment

by:lof
ID: 26141004
No problem YZlat, it happens to me sometimes as well.
0
 

Author Comment

by:BrookK
ID: 26141067
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
 
LVL 35

Expert Comment

by:YZlat
ID: 26141120
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
 

Author Comment

by:BrookK
ID: 26141251
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
 
LVL 10

Expert Comment

by:lof
ID: 26141343
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
 

Author Comment

by:BrookK
ID: 26141709
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
 
LVL 10

Expert Comment

by:lof
ID: 26141875
Ok, now I believe I understand the question.
It will be more tricky than I thought.
I should have a sample ready soon.
0
 

Author Comment

by:BrookK
ID: 26141883
Sorry it took a while. No problem.

Thanks,
-B
0
 
LVL 10

Accepted Solution

by:
lof earned 500 total points
ID: 26141967
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
 

Author Comment

by:BrookK
ID: 26142178
lof, Let me know if you have any further questions.

Thanks,
-B
0
 
LVL 10

Expert Comment

by:lof
ID: 26142189
Did you see the code I produced?
And how about the real-life background to the problem?
0
 
LVL 10

Expert Comment

by:lof
ID: 26142698
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
 

Author Comment

by:BrookK
ID: 26142882
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
 

Author Closing Comment

by:BrookK
ID: 31670914
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
 
LVL 10

Expert Comment

by:lof
ID: 26143993
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
 

Author Comment

by:BrookK
ID: 26145079
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
 
LVL 10

Expert Comment

by:lof
ID: 26145120
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
 

Author Comment

by:BrookK
ID: 26145494
I need to have remarks/description column to be populated as well.
0
 
LVL 10

Expert Comment

by:lof
ID: 26145799
but if you will take data from MasterTable and not from DetailsTable you don't have the information about the remarks.
0
 

Author Comment

by:BrookK
ID: 26147136
That is when I got lost.

I need MainID from Master for ID = 4 and Remark from TransDetails where ID = 1.
0
 
LVL 10

Expert Comment

by:lof
ID: 26147254
do the records from Details where ID=1 point with MainId to Master where ID=4?

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question