Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: SQL Add to Multiple Tables
Top Forums Programming SQL Add to Multiple Tables Post 302296309 by flakblas on Tuesday 10th of March 2009 10:10:42 PM
Old 03-10-2009
SQL Add to Multiple Tables
I'm pretty new to the database world and I've run into a mental block of sorts. I've been unable to find the answer anywhere. Here's my problem: I have several tables and everything is as normalized as possible (as I've been lead to understand normalization.) Normalization has lead to some challenges though. Namely, how do I do an effective INSERT? So, let's say I have tbl_Customers, tbl_Addresses, tbl_States and tbl_Cities. Now if I want to add a new customer, with their own address and everything, I'll have to check tbl_States and tbl_Cities to see if the state and city already exist. If they do then set the FK in tbl_Address with them, otherwise do INSERTS on tbl_States and tbl_Cities. I could do all this in Perl but it's horribly ugly, inefficient and just feels wrong. From what I've read/heard though, I should be able to create a sotred function where I can just CALL AddCustomer(FName, LName, Street, City, State) and it would handle all that in the background. Any suggestions? Obviously the solution will onvolve multiple INSERTs but I know there has to be a better way than what I'm doing. Here's some of what I've come up with so far but without any luck.

Code:
CREATE PROCEDURE Insert_Big(IN in_FirstName   VARCHAR(40), 
                            IN in_LastName    VARCHAR(40), 
                            IN in_NickName    VARCHAR(40),
                            IN in_BirthDate   DATETIME,
                            IN in_Country     VARCHAR(40),
                            IN in_State       VARCHAR(40),
                            IN in_City        VARCHAR(40),
                            IN in_Street      VARCHAR(30),
                            IN in_HouseNumber VARCHAR(15),
                            IN in_EmailAddy   VARCHAR(255),
                            IN in_Phone       VARCHAR(30),
                            IN in_Company     VARCHAR(40))
    BEGIN
    DECLARE Found INT DEFAULT 0;

    SELECT Found FROM tbl_Cities WHERE CityName = in_City;
    IF Found = 0 THEN
        INSERT INTO tbl_cities (CityName) VALUES (in_city);
        SELECT LAST_INSERT_ID() AS CityRow;
    ELSE
        SELECT CityRow = CityID FROM tbl_Cities WHERE tbl_Cities.CityName = in_City;
    ENDIF;

 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Execute multiple SQL scripts from single SQL Plus connection

Hi! I would like to do a single connection to sqlplus and execute some querys. Actually I do for every query one connection to database i.e echo 'select STATUS from v$instance; exit' > $SQL_FILE sqlplus user/pass@sid @$SQL_FILE > $SELECT_RESULT echo 'select VERSION from v$instance;... (6 Replies)
Discussion started by: guif
6 Replies

2. Web Development

mysql query for multiple columns from multiple tables in a DB

Say I have two tables like below.. status HId sName dName StartTime EndTime 1 E E 9:10 10:10 2 E F 9:15 10:15 3 G H 9:17 10:00 logic Id devName capacity free Line 1 E 123 34 1 2 E 345 ... (3 Replies)
Discussion started by: ilan
3 Replies

3. Programming

Query to join two tables in SQL

Hi, I have 2 tables. Table1 name map_code data_code id aaa 2732C 3333B 10 bbb 1223F 2545v 11 ccc 12 Table2 name map_code data_code id text_description aaa 2732C 3333B 10 description 1 bbb 1223F 2545v 11 ... (3 Replies)
Discussion started by: vanitham
3 Replies

4. Shell Programming and Scripting

merge multiple tables with perl

Hi everyone, I once again got stuck with merging tables and was wondering if someone could help me out on that problem. I have a number of tab delimited tables which I need to merge into one big one. All tables have the same header but a different number of rows (this could be changed if... (6 Replies)
Discussion started by: TuAd
6 Replies

5. Programming

Dynamically checking rules in database tables using SQL

I want to check for rows in a table where all values (except the key) is empty. I am using MySQL 5.5. I plan to do this mechanically, so the approach should work for any table in my database schema. Suppose for illustration purposes I start with the following table: CREATE TABLE `sources` ( ... (4 Replies)
Discussion started by: figaro
4 Replies

6. Shell Programming and Scripting

Multiple files to load into different tables,

multiple files to load into different tables, I have a script show below, but this script loads data from txt file into a table, but i have multiple input files(xyzload.txt,xyz1load.txt,xyz2load.txt......) in the unix folder , can we load these files in diff tables (table 1, table2... (0 Replies)
Discussion started by: nani1984
0 Replies

7. Shell Programming and Scripting

Multiple files to load into different tables

multiple files to load into different tables, I have a script show below, but this script loads data from txt file into a table, but i have multiple input files(xyzload.txt,xyz1load.txt,xyz2load.txt......) in the unix folder , can we load these files in diff tables (table 1, table2... (1 Reply)
Discussion started by: nani1984
1 Replies

8. Shell Programming and Scripting

Storing multiple sql queries output into variable by running sql command only once

Hi All, I want to run multiple sql queries and store the data in variable but i want to use sql command only once. Is there a way without running sql command twice and storing.Please advise. Eg : Select 'Query 1 output' from dual; Select 'Query 2 output' from dual; I want to... (3 Replies)
Discussion started by: Rokkesh
3 Replies

9. UNIX for Advanced & Expert Users

Identify tables from Oracle sql scripts

Hi, Please let me know if you have any thoughts on how to read a table that has all the oracle sql files or shell scripts at the job and step level to identify all the tables that does merge, update, delete, insert, create, truncate, alter table (ALTER TABLE XYZ RENAME TO ABC) and call them out... (1 Reply)
Discussion started by: techmoris
1 Replies

10. UNIX for Beginners Questions & Answers

Export Oracle multiple tables to multiple csv files using UNIX shell scripting

Hello All, just wanted to export multiple tables from oracle sql using unix shell script to csv file and the below code is exporting only the first table. Can you please suggest why? or any better idea? export FILE="/abc/autom/file/geo_JOB.csv" Export= `sqlplus -s dev01/password@dEV3... (16 Replies)
Discussion started by: Hope
16 Replies

LEARN ABOUT CENTOS

qsql

QSql(3qt)																 QSql(3qt)

NAME

       QSql - Namespace for Qt SQL identifiers that need to be global-like

SYNOPSIS

       #include <qsql.h>

   Public Members
       QSql ()
       enum Op { None = -1, Insert = 0, Update = 1, Delete = 2 }
       enum Location { BeforeFirst = -1, AfterLast = -2 }
       enum Confirm { Cancel = -1, No = 0, Yes = 1 }
       enum ParameterType { In = 1, Out = 2, InOut = 3 }
       enum TableType { Tables = 0x01, SystemTables = 0x02, Views = 0x04, AllTables = 0xff }

DESCRIPTION

       The QSql class is a namespace for Qt SQL identifiers that need to be global-like.

       Normally, you can ignore this class. Several Qt SQL classes inherit it, so all the identifiers in the Qt SQL namespace are visible without
       qualification.

       See also Database Classes.

   Member Type Documentation
QSql::Confirm
       This enum type describes edit confirmations.

       QSql::Yes

       QSql::No

       QSql::Cancel

QSql::Location
       This enum type describes SQL navigation locations.

       QSql::BeforeFirst

       QSql::AfterLast

QSql::Op
       This enum type describes edit operations.

       QSql::None

       QSql::Insert

       QSql::Update

       QSql::Delete

QSql::ParameterType
       This enum is used to set the type of a bind parameter

       QSql::In - the bind parameter is used to put data into the database

       QSql::Out - the bind parameter is used to receive data from the database

       QSql::InOut - the bind parameter is used to put data into the database; it will be overwritten with output data on executing a query.

QSql::TableType
       This enum type describes types of tables

       QSql::Tables - All the tables visible to the user

       QSql::SystemTables - Internal tables used by the DBMS

       QSql::Views - All the views visible to the user

       QSql::AllTables - All of the above

MEMBER FUNCTION DOCUMENTATION

QSql::QSql ()
       Constructs a Qt SQL namespace class

SEE ALSO

       http://doc.trolltech.com/qsql.html http://www.trolltech.com/faq/tech.html

COPYRIGHT

       Copyright 1992-2007 Trolltech ASA, http://www.trolltech.com.  See the license file included in the distribution for a complete license
       statement.

AUTHOR

       Generated automatically from the source code.

BUGS

       If you find a bug in Qt, please report it as described in http://doc.trolltech.com/bughowto.html.  Good bug reports help us to help you.
       Thank you.

       The definitive Qt documentation is provided in HTML format; it is located at $QTDIR/doc/html and can be read using Qt Assistant or with a
       web browser. This man page is provided as a convenience for those users who prefer man pages, although this format is not officially
       supported by Trolltech.

       If you find errors in this manual page, please report them to qt-bugs@trolltech.com.  Please include the name of the manual page (qsql.3qt)
       and the Qt version (3.3.8).

Trolltech AS							  2 February 2007							 QSql(3qt)
All times are GMT -4. The time now is 09:23 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy