Solved

Insert into Table3 Select from table1, table2

Posted on 2004-10-22
1,444 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
Question by:Vasi04
    8 Comments
     
    LVL 15

    Assisted Solution

    by:mcmonap
    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:
    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
    Thanks both for your reply
    Bill, I am using your solutions. I am testing. Can I get back to you on this?
    0
     

    Author Comment

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

    Expert Comment

    by:BillAn1
    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
    Don't worry about a split - reward the points to the answer that works for you!  Thanks though BillAn1
    0
     

    Author Comment

    by:Vasi04
    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
    Delete from table3 where key = ' '
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    846 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

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now