Solved

Insert Multiple Records In SQL From Text File / Database

Posted on 2010-11-25
14
853 Views
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?
0
Comment
Question by:thyros
14 Comments
 
LVL 19

Expert Comment

by:Bardobrave
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...)
0
 
LVL 4

Expert Comment

by:Andre412
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
0
 

Author Comment

by:thyros
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?
0
 
LVL 19

Expert Comment

by:Bardobrave
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
0
 
LVL 20

Expert Comment

by:BuggyCoder
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:-

http://msdn.microsoft.com/en-us/library/ms254498(VS.80).aspx

Hope it helps
:-)
0
 

Author Comment

by:thyros
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.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

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

Accepted Solution

by:
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á
0
 

Author Comment

by:thyros
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?
0
 
LVL 19

Assisted Solution

by:Bardobrave
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.
0
 

Author Comment

by:thyros
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.
0
 
LVL 68

Expert Comment

by:Qlemo
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.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Creating and Managing Databases with phpMyAdmin in cPanel.
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

746 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

16 Experts available now in Live!

Get 1:1 Help Now