Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Insert into Table3 Select from table1, table2

Posted on 2004-10-22
8
Medium Priority
?
1,462 Views
Last Modified: 2012-05-05
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
Comment
Question by:Vasi04
[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
  • 3
  • 2
8 Comments
 
LVL 15

Assisted Solution

by:mcmonap
mcmonap earned 800 total points
ID: 12380456
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
 
LVL 17

Accepted Solution

by:
BillAn1 earned 1200 total points
ID: 12380502
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
 

Author Comment

by:Vasi04
ID: 12381357
Thanks both for your reply
Bill, I am using your solutions. I am testing. Can I get back to you on this?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:Vasi04
ID: 12381615
HI Bill
Does the code insert new rows in table3?
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12381796
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
 
LVL 15

Expert Comment

by:mcmonap
ID: 12388569
Don't worry about a split - reward the points to the answer that works for you!  Thanks though BillAn1
0
 

Author Comment

by:Vasi04
ID: 12392524
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
 
LVL 17

Expert Comment

by:BillAn1
ID: 12392855
Delete from table3 where key = ' '
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

609 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