Insert Multiple Records In SQL From Text File / Database

Posted on 2010-11-25
Last Modified: 2012-05-10
Bulk insert multiple records into an SQL table field2, where value matches ID from field1

We have an SQL database that contains a product catalogue of information.  We have a team of people collecting additional information that we would like to insert into the database.

Our database table is called Products and within this table there is a unique numeric identifier field p_id and the associated content would need to be inserted into the field  p_custom_content2 when the information to be inserted has a match on the value of p_id

What is the best way to save that additional information in the external datasource and how to import it in bulk?

For example, we are collecting html descriptions for products, so we could make an Excel spreadsheet file and have the p_id value in one column and the actual html to be inserted in a separate column called p_custom_content2 - would this work?  Alternatively, we might decide on saving the additional data in its own MySQL database since excel spreadsheets are a bit clunky with multiple line html records.  How can we regularly transfer the data from the external source into the main Products table?
Question by:thyros
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
LVL 19

Expert Comment

ID: 34213449
How is that additional information being collected?

You can make an app to insert directly collector's data into database, or you can load collectors info in several automatic ways (automated tasks, periodically fired scripts, queries run on demand, etc...)

Expert Comment

ID: 34214070
If you compiled an excel file you would approach the import as such

1. create a data import package in the sql management console to import the excel spreadsheet into a new table in your sql db
2. write an tsql script that will itterate through you new table row by row performing any matching of data against any other table in the db and take the appropriate action, ie insert the data into your content field when there is an id match

you will need to include the id field as a column in your excel file in order to be able to maintain type when importing in to sql server so your tsql script has something to grab onto

SQL server has a very powerful import tool , to access it open your ms sql management studio, locate your db server, drill down to the tables node, right click > tasks > import data
the rest will fall into place

Author Comment

ID: 34214090
Our web application is running on MS SQL 2008 standard edition, but we are hiring additional help to build up our catalogue resources but it has to be done in a controlled manner - so most likely we would expect them to save their content in their own MySQL database for example, and each time they would save a content element, they would enter the unique p_id value to associate that content with the record in our main database.

So from this perspective, what is the procedure for taking data from an external MySQL database, where the connecting link is the unique numeric id found in the p_id field?
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

LVL 19

Expert Comment

ID: 34214333
probably the best approach would be import data from SQL Server importing resources... you'll probably need to install some controllers to allow SQL Server to connect to MySql
LVL 20

Expert Comment

ID: 34217066
The only two ways that i can think of are

1. write a macro in Excel and allow it to import data regularly in your database.
2. Write an assembly in .net to read file with PId and Path to your custom HTML Files and insert the data in your DB. For this you need to understand CLR integration with SQL Server 2005/2008. Here is a link to assist you:-

Hope it helps

Author Comment

ID: 34418133
Thank you all for your comments but I have not been able to find a practical solution that we are actually able to implement as the current suggestions require knowledge of programs and development that I don't possess.

Ideally what would work was if we had a text based file called data.txt and it was comma/pipe delimited.  In one column we had the value for table field p_id and in the second column we had the value for table field p_custom_content2 - then if we could run an sql query from studio manager that said read data.txt and where it finds a match for p_id value then update the table field p_custom_content2 with the content from the second column.  

Essentially what this is, is a bulk insert of data from a pipe delimited text file, but I don't know how to execute it with an sql query.

Author Comment

ID: 34418153
Also, going to post the object notice to keep the question alive and prevent it from being automatically deleted..
LVL 19

Accepted Solution

Bardobrave earned 500 total points
ID: 34418275
Well... you can build a script that reads the file and, for each line on the file, writes this to a file:

"UPDATE Products set p_custom_content2 =" + p_customGotFromFile + " WHERE p_id = " + p_idGotFromFile

Then you can load this file in Query Analyzer and execute it... et voilá

Author Comment

ID: 34419935
So we would have hundreds of those snippets, each updating an individual product record - seems to make sense.  One concern I have..   the value of p_custom_content2 will contain many html markers, quotes, commas etc.  This would likely break the sql query - what is the solution to that?
LVL 19

Assisted Solution

Bardobrave earned 500 total points
ID: 34421143
The commas should be converted to quotes to avoid SQL from breaking, and the quotes probably have to be dealed with inside the script while constructing the result strings, depending on your scripting flavour the method will be different or maybe even unnecessary.

Author Comment

ID: 34660824
Could you please clarify how: "commas should be converted to quotes to avoid SQL from breaking"?

We know a perl developer that could help us with this but I just need to ensure the syntax is correct when processing this data.
LVL 69

Expert Comment

ID: 34671257
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
converting integer data type to time data type in sql 4 44
GeoClustering  and AOG 25 42
add criteria to query in VB, Access 2003 2 29
Not listening to where 1 17
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below.…

740 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