Solved

Insert stored procedure

Posted on 2011-02-15
5
238 Views
Last Modified: 2012-05-11
I have two tables.
First table has the ff. structure

tID   tcode >>First table

Second table has

tID Name address>>Second table

What Id like to do is write an INSERT statement that would insert the value 38 in the tcode field of the First table under the following condition.

1. first if the 2 tables link based on the tID field.
2. If link exits,  check in the first table if there is a value of 38 in the tCode. If not exist, INSERT 28 in the tCode otherwise,ingnore.

Essentliall the first table can have many codes under 1 tID like eg.

tID     tcode
222    20
222    21
222    38
222   29


Thanks

I included vb.net because, it may need a code to loop to each record in the table of the database.


0
Comment
Question by:zachvaldez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 10

Expert Comment

by:PSSUser
ID: 34899026
If I've understood your question correctly I believe the below should do what you want:

INSERT INTO First_Table
SELECT Second_Table.tid, 38
FROM Second_Table
WHERE NOT EXISTS (SELECT T1.Tid FROM First_Table as T1 WHERE T1.Tid=Second_Table.Tid AND T1.TCode=38);
GO

Kind Regards
Chris
0
 

Author Comment

by:zachvaldez
ID: 34899436
I forgot to mention that there is datefield  value in 2nd table that I need to insert to 1st table tdatefield at the same time.
0
 
LVL 10

Expert Comment

by:PSSUser
ID: 34899504
Hi,

you should just be able to add the date field to the SELECT statement. I did miss the field list in the insert line and also, if the TID can occur more than once in Second_Table you may want a DISTINCT.

INSERT INTO First_Table (Tid, TCode, DateField)
SELECT DISTINCT Second_Table.tid, 38, Second_Table.DateField
FROM Second_Table
WHERE NOT EXISTS (SELECT T1.Tid FROM First_Table as T1 WHERE T1.Tid=Second_Table.Tid AND T1.TCode=38);
GO

If there are multiple entries for a TID in the second table, you can add conditions to select which record's date field is used or use a calculation such as MAX(DateField).

Kind Regards
Chris
0
 

Author Comment

by:zachvaldez
ID: 34913301
Question on insert procedure

Actually, the table structure for First table  are:
Comp
Emp
TID
TCode
DateField
NCode
DCode
CCode

Second_table structure

TID
TCode
DateCode

Im actually inserting 3  values from Second_table
So for the rest of the columns in First_table that are not in Second_table
Id like to insert the hard code  Values
Comp = 1
Emp = 30
NCode = 'LK'
DCode = 7
CCode = 78

How would I write the sql statement for the INSERT?
0
 
LVL 10

Accepted Solution

by:
PSSUser earned 400 total points
ID: 34914109
Hi,

It would depend whether the check/link on Tid between the 2 is enough or if it also needs to include the date field. I've assumed DateCode is just a normal date field the same as DateField. If one is a string you will want to add data type conversion.

Assuming I just need to check if any entry in the first table for the Tid has a TCode of 38:
INSERT INTO First_Table (Comp, Emp, Tid, TCode, DateField, NCode, DCode, CCode)
SELECT DISTINCT 1, 30, Second_Table.tid, 38, Second_Table.DateCode, 'LK', 7, 78
FROM Second_Table
WHERE NOT EXISTS (SELECT T1.Tid FROM First_Table as T1 WHERE T1.Tid=Second_Table.Tid AND T1.TCode=38);
GO

If you need to check if any entry in the first table for the Tid AND Date has a TCode of 38:
INSERT INTO First_Table (Comp, Emp, Tid, TCode, DateField, NCode, DCode, CCode)
SELECT DISTINCT 1, 30, Second_Table.tid, 38, Second_Table.DateCode, 'LK', 7, 78
FROM Second_Table
WHERE NOT EXISTS (SELECT T1.Tid FROM First_Table as T1 WHERE T1.Tid=Second_Table.Tid AND T1.TCode=38 AND T1.DateField=Second_Table.DateCode);
GO

Kind Regards,
Chris
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

691 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