[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1488
  • 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
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.

 
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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