Insert into MS SQL Table from formatted string

Dear Experts,

I want to insert a formatted text column from master table like: (50014EE20154;50014EE20155;50014EE20156;50014EE20157;50014EE20158;50014EE20159;50014EE20160;50014EE20161;50014EE20162;50014EE20163)

 into the detail table:
50014EE20154
50014EE20155
50014EE20156
50014EE20157
50014EE20158
50014EE20159
50014EE20160
50014EE20161
50014EE20162
50014EE20163

MS SQL 2012, C# or combination based solution would be highly appreciated.

Thanks.
JimiJ13I T ConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
You will need to create a UDF that will parse your string and return a table. There is one such UDF detailed at http://searchsqlserver.techtarget.com/tip/Split-delimiter-separated-lists-with-a-T-SQL-UDF
Gustav BrockCIOCommented:
Éric, Jim has an array to hold the values:

Populate Serial Number range

I would use a DataTableAdapter, but with only a dozen or so items, I guess a simple loop of append commands could be used. But I don't use SqlCommand etc. so I can't do it off head.

/gustav
Éric MoreauSenior .Net ConsultantCommented:
>>Jim has an array to hold the values

I wasn't aware of that part. The question is asking to split something into a master table. The split UDF is really the way to go if it is really the case.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Gustav BrockCIOCommented:
OK, can't tell what will fit Jim best.

/gustav
JimiJ13I T ConsultantAuthor Commented:
Thanks Eric and Gustav,

I ended up using foreach iterating SN list by calling the same SP every time.
Looks simple but seems inefficient specially when iterating thousands of SN:
   foreach (string NewSN in SNs)
                 {                        
                        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MARS_Connection"].ConnectionString))
                        {                                            
                            string command = "[usp_InsertSN]";
                            using (SqlCommand cmd = new SqlCommand(command, con))
                            {
                                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@InventorySN", iRec.InventoryUID);
                                cmd.Parameters.AddWithValue("@SerialNo", NewSN.ToUpper());
                                cmd.Parameters.AddWithValue("@ProductCode", iRec.ProductCode);
                                con.Open();
                                cmd.ExecuteNonQuery();
                            }
                          con.Close();
                        }                                        
                  }    

I wonder if there's anyone who can help me modify the above code, so we can use MARS connection to open once and close only when iterations is completed.

BTW, the SNs is the list of SN (Serial Numbers).

Thanks.
Gustav BrockCIOCommented:
Oh, but you mentioned a dozen or so SNs, not thousands.

However, you could start by moving the connection outside the loop. Open it first, then loop, then close connection.
Also, add the parameter name once, then set the value of these in the loop.

As mentioned, I don't use SqlCommand, so I won't attempt writing smart code here.

/gustav
JimiJ13I T ConsultantAuthor Commented:
Gustav,

Once we make it work, the next step is scalability - to give more than what is being asked. I have already tried, what you have suggested before my last posting, but it did not work. For now, this solution can suffice, but I will also find ways to optimize this.

Thanks.
Gustav BrockCIOCommented:
I tried this. It takes 5-10 seconds per 100 numbers via a slow connection:
using System;
using System.Data;
using System.Data.Linq;
using System.Data.SqlClient;

namespace AddRecords
{
    class Program
    {
        static void Main(string[] args)
        {
            SerialNumbers sn = new SerialNumbers();
            string[] sns = yourArrayWithSns;
            sn.InsertSns(sns);
        }
    }

    public class SerialNumbers
    {

        private SqlConnection Connection()
        {
            SqlConnectionStringBuilder sqlConnectionStringBuilder = new SqlConnectionStringBuilder();
            sqlConnectionStringBuilder.ConnectionString = "Initial Catalog=<yourdatabasename>;User id=<yourusername";
            sqlConnectionStringBuilder.DataSource = "<yourservername/hostaddress>";
            sqlConnectionStringBuilder.Password = "<yourpassword>";
            return new SqlConnection(sqlConnectionStringBuilder.ToString());
        }

        public void InsertSns(string[] sns)
        {
            IDbConnection connection;
            connection = Connection();

            DataContext db = new DataContext(connection);
            db.Connection.Open();
            db.Transaction = db.Connection.BeginTransaction();
            Console.WriteLine(db.Connection.ConnectionString.ToString());
            Console.WriteLine(db.DatabaseExists().ToString());
            Console.WriteLine(DateTime.Now.ToString());
            for (int i = 0; i < sns.Length; i++)
            {
                db.ExecuteCommand(@"insert into TestDate ([DateTime]) values ('" + sns[i] + "')");
            }
            db.Transaction.Commit();
            db.Dispose();
            Console.WriteLine(DateTime.Now.ToString());
            Console.ReadKey();
        }

    }

 }

Open in new window

/gustav
JimiJ13I T ConsultantAuthor Commented:
Hi Gustav,

Thanks for your effort!

Surprisingly, using my solution to insert 1500 items takes less than a second.
Gustav BrockCIOCommented:
As said, it was over a slow line.

But with a second for thousand inserts, I see no issue. I would leave it and spend my time on other tasks.

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JimiJ13I T ConsultantAuthor Commented:
Thanks for the timely interactions that indeed helped me arrived the best option.
Gustav BrockCIOCommented:
You are welcome!

/gustav
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.