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

Insert into Table3 Select from table1, table2

I need sql code for one time insertion for this condition. Thanks so  much in advance
-------
Table1
id, code
1, a11,
2, x22,
3, x33,
4, z11,
5, b11,

Table2
id, txt1, txt2, txt3,
1, fsfd, dffdf, df43
2, eer, wer, erf
3, 943f, f0jf, 9ef,
4, efe, efr4, 34r
5, 43rr, 45fr, 45fr

Table3
key, txt1, txt2, txt3

---------------------------------------
1. I need to SELECT rows from table2 where table2.id = table1.id AND where first character in table1.code = x, y or z
2. Insert these rows in table3 and insert values in column "key" = k1
3. Then SELECT rows from table2 where table2.id = table1.id AND where first character in table1.code != x, y or z
4. Again insert these rows in table3 and insert values in column "key" = k2

I guess I need to use substring in my where clause to get first charatcer?

0
Vasi04
Asked:
Vasi04
  • 3
  • 3
  • 2
2 Solutions
 
mcmonapCommented:
Hi Vasi04,

Ithink that something like this should do the job for you:

INSERT INTO table3 ([Key], txt1, txt2, txt3)
SELECT 'k1',t2.txt1,t2.txt2, t2.txt3
FROM tabl1 t1
          JOIN table2 t2 ON t1.[id] = t2.[id] AND LEFT(t1.code,1) IN ('x','y','z')

INSERT INTO table3 ([Key], txt1, txt2, txt3)
SELECT 'k2',t2.txt1,t2.txt2, t2.txt3
FROM tabl1 t1
          JOIN table2 t2 ON t1.[id] = t2.[id] AND LEFT(t1.code,1) NOT IN ('x','y','z')
0
 
BillAn1Commented:
Vasi04,
try this -
before you run the insert, just try the SELECT without the INSERT bit, to verify that it will insert the data you expect !

insert into Table3 (key, txt1, txt2, txt3)
select
case when left(code,1) in ('x','y','z') then 'k1' else 'k2' end,
txt1, txt2, txt3
from table1 join table2 on table1.id = table2.id
0
 
Vasi04Author Commented:
Thanks both for your reply
Bill, I am using your solutions. I am testing. Can I get back to you on this?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Vasi04Author Commented:
HI Bill
Does the code insert new rows in table3?
0
 
BillAn1Commented:
Yep,
I suggest you run the select part -
"select
case when left(code,1) in ('x','y','z') then 'k1' else 'k2' end,
txt1, txt2, txt3
from table1 join table2 on table1.id = table2.id "
initially.
If you like what you see, then add the "insert into Table3 (key, txt1, txt2, txt3)" bit to the front of it, and it will insert these rows into the table for you.

and for the record, mcmonap's queries will do the same thing, so at a minimum you should split the points, or feel free to accept his/her answer.
0
 
mcmonapCommented:
Don't worry about a split - reward the points to the answer that works for you!  Thanks though BillAn1
0
 
Vasi04Author Commented:
May be you both want to help me on this:
I want to delete all rows in table3 where key = ' ' (single space)

Delete * from table3 where key = ' ' ??
Thanks
0
 
BillAn1Commented:
Delete from table3 where key = ' '
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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