Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Data import SQL (Read 2743 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Data import SQL
03.12.08 at 17:42:44
Data import SQL
Data can be imported into a database using SQL or without using SQL.

  • Data import using SQL:
    SQL stands for Simple Query Language. SQL is a command language to have interaction with databases. SQL commands can be written to insert data into a database. To import data into a database using SQL you need to transform the data into SQL insert statements.
    The SQL insert statement is quite simpel:
    insert into mytable ( <<column_list>> ) values ( <<value_list>> );
    In the insert statement <<column_list>> needs to be substituted by a list of column names separated by a comma's, e.g.: empno, ename, sal. The text <<value_list>> needs to be replaced with the data values, e.g.: 1234, 'Jones', 1000.
    The task is therefore to place the data into the lists of values you need in the insert statements.
    To plug the data into the insert statements is something tools can help with. I like to use Excel for these tasks and read the values into a spreadsheet and then use the text concatenate function to embed the data values into the insert statement.
    Suppose the data to insert is 1,'Jones', 1000. The data needs to be transformed into the following insert statement:
    insert into mytable (empno, ename, sal) values (1, 'Jones', 1000);
    This can be achieved using the Excel concatenate function:
    =concatenate("insert into mytable (empno, ename, sal) values (";A1;",'";B1;"',";C1;");")
    In the SQL*XL software we offer this can be simplified using the SQLCreateInsertStatement Excel function which yields the same SQL insert statement:
    The beauty of using this SQL*XL Excel function is that database specifics are hidden from the user. E.g. if Oracle is used a date needs to be entered differently then when a SQL Server database is used. The SQL*XL software takes care of this for you.
    Why not give SQL*XL a try today.
    SQL*XL: SQL Excel software

    Download SQL*XL now!

    When a set of SQL insert statements is created you need to execute these commands against your database. This can be done using database tools that your database vendor supplied such as SQL*PLus for Oracle or Query Analyser of SQL Server. You can again use the SQL*XL software for this too. SQL*XL is a versatile SQL tool that works within Excel. You can simply select the SQL statements in your spreadsheet and send them to the database in one click.
  • Data import without using SQL:
    It is more user friendly to insert SQL without using SQL. Tools exist to import data into a database without needing to write a single line of code. SQL*XL is such a tool. SQL*XL can use SQL but if you want comfort you can use the SQL*XL features to import data into the database without having to write any code. Simply select the block of data and use the insert multiple rows screen in SQL*XL. It is all done for you.
    Using tools also the complications of data formats is dealt with. As you can read above, when inserting a text you need to put quotes around the text. Now suppose the text itself contains a quote as e.g. in Mary's. There is a problem as the single quote will be seen by the database as an end of text marker and it renders the SQL insert statement invalid. Tools like SQL*XL can bind a value to a statement avoiding these difficulties.
Back to top
« Last Edit: 08.01.09 at 09:53:01 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1