• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 556
  • Last Modified:

Adding the new records from one table to another table

I have two tables namely tblRequest(requestid,requestdesc,requestorid) and tblSupport(requestid,resquestorid,year,month) with the following data:

tblRequest
R101   Service   C101
R102   Update    C101
R103   Service   C102
R104   Request  C101 and so on

tblSupport
R101   C101   2009   02
R103   C102   2009   03

I have to create a SQL statement that will extract all the record C101 in tblRequest and insert those records that are not currently available in tblSupport as follows:

tblSupport
R101   C101   2009   02
R103   C102   2009   03
R102   C101   2009   04
R104   C101   2009   04

Please advice me on could I create this SQL statement to achieve the above-mentioned requirement.


0
bpyeo
Asked:
bpyeo
  • 5
  • 5
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Hope your Request Id is the primary key in tblRequest table.
Replace the column names accordingly and how do you derive the year and month information for tblSupport table.

Try this one out:
Insert into tblSupport (reqid, col2, col3, col4)
select reqid, col2, col3, col4
from tblRequest 
where reqid in (
select reqid from tblRequest 
except
select reqid from tblSupport )

Open in new window

0
 
bpyeoAuthor Commented:
tblRequest has only 3 fields. The year and month could be determined manually during the execution of the SQL statement.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
What's the logic to determine year and month so that I can give you the SQL for that
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
bpyeoAuthor Commented:
I will specify the year and month when I execute this SQL statement.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Then this would suffice:
Insert into tblSupport (reqid, col2, col3, col4)
select reqid, col2, year, month
from tblRequest 
where reqid in (
select reqid from tblRequest 
except
select reqid from tblSupport )

Open in new window

0
 
bpyeoAuthor Commented:
I have tried the above SQL and an error has returned when I execute this SQL statement (I am using SQL Server 2005):

The EXCEPT SQL construct or statement is not supported.

Please advice.
0
 
bpyeoAuthor Commented:
I have managed to get the SQL statement running. However if the tblSupport does not have the requestid and no record will be inserted.

Please advice me on how to set the default year (say 2009) and month (say 04) to the existing SQL statement so that the sql statement will insert the records regardless of whether the requestid is found in the tblSupport..

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
<< Hope your Request Id is the primary key in tblRequest table. >>

 I have mentioned this as an assumption for this. If you dont have Request ID as the primary key of that table then replace reqid using that Pk in that query and will work out for you.

<< The EXCEPT SQL construct or statement is not supported. >>

EXCEPT is a valid command in SQL Server.
0
 
bpyeoAuthor Commented:
Yes, reqid is the PK for the tblRequest and reqid,resquestorid,year,month are the PK for tblSupport.

I have modified the SQL a little to include the tblSupport table as follows:

Insert into tblSupport (reqid, col2, col3, col4)
select reqid, col2, year, month
from tblRequest, tblSupport
where reqid in (
select reqid from tblRequest
except
select reqid from tblSupport )

If I do not include the tblSupport, the execution of the SQL statement will return year and month as invalid column as it does not exist in the tblRequest.

Because of this modification, if the requestorid does not exist in the tblSupport, it will extract empty record and hence insert no record. This SQL works well if the requestorid exist in the tblSupport.

Hence I would like to check with you how could I add the year (say 2009) and month (04) manually to the SQL statement to overcome this issue.

Please advice.
0
 
ThorSG1Commented:
This should work for you.

INSERT INTO tblSupport (requestid, requestorid, year, month)
SELECT r.requestid, r.requestorid, '2009', '02'
FROM tblRequest r
LEFT OUTER JOIN tblSupport s ON r.requestid = s.requestid
WHERE s.requestid IS NULL
 
-- You could also do this
 
DECLARE @Year varchar(4)
DECLARE @Month varchar(2)
SET @Year = '2009'
SET @Month = '02'
 
INSERT INTO tblSupport (requestid, requestorid, year, month)
SELECT r.requestid, r.requestorid, @Year, @Month
FROM tblRequest r
LEFT OUTER JOIN tblSupport s ON r.requestid = s.requestid
WHERE s.requestid IS NULL

Open in new window

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
This should help you out:

Hope your year and month columns are integer datatype. otherwise use single quotes.

No need to join tblSupport at the location you added.
Insert into tblSupport (reqid, col2, col3, col4)
select reqid, col2, 2009, 04
from tblRequest 
where reqid in (
select reqid from tblRequest 
except
select reqid from tblSupport )

Open in new window

0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now