backup script for a table in MSSQL 2005

[bhakti.thakkar]

hi all,
Very new this forum. i am using MSSQL 2005 and i want to create a backup script for a table [create and insert statements] without using mssql datapublishing wizard 2005. is it possible?? how can i do it

Best Regard

[bugmenot]

al = new ArrayList();
            dt = myHelper.GetDS("SELECT name FROM sys.tables ").Tables[0];
            foreach (DataRow r in dt.Rows)
            {
                al.Add(r["name"].ToString());
            }

            foreach (string i in al)
            {
                    DataTable proc = myHelper.GetDS("select * from " + i).Tables[0];
                    Tables.Add(dbToInsert(proc, i));
            }


        private static string dbToInsert(DataTable proc, string name)
        {
            string insert = "";
            if (isIndent(name)) insert = "SET IDENTITY_INSERT " + name + " ON ; " + "\n";

            string colList = "";

            foreach (DataColumn col in proc.Columns)
            {
                colList += col.ColumnName + ", ";
            }
            colList = colList.Substring(0, colList.Length - 2);

            foreach (DataRow row in proc.Rows)
            {
                insert += "insert into " + name + " (" + colList + ") values(";

                for (int i = 0; i <= proc.Columns.Count - 1; i += 1)
                {
                    string tempstr = row[i].ToString();
                    tempstr = tempstr.Replace("'", "");

                    if (tempstr == "")
                        insert += "NULL, ";
                    else if (proc.Columns[i].DataType == Type.GetType("System.String") ||
                             proc.Columns[i].DataType == Type.GetType("System.DateTime"))
                        insert += "'" + tempstr + "', ";
                    else if (tempstr == "False" || tempstr == "True")
                        insert += (tempstr == "False" ? 0 : 1) + ", ";
                    else
                        insert += tempstr + ", ";
                }
                insert = insert.Substring(0, insert.Length - 2) + "); " + "\n";
            }
            if (isIndent(name)) insert += "SET IDENTITY_INSERT " + name + " OFF ;" + "\n";

            return insert;
        }

        private static bool isIndent(string name)
        {
            string[] str = {
                               "AdminUsers", "ContractorToService", "ListingsToIndustries", "MaintenanceLimits",
                               "ProjectsToIndustries", "ZipcodeToRegion","UserFavorates","sysdiagrams","ListingPrice"
                           };
            foreach (string s in str)
            {
                if (s == name) return false;
            }
            return true;
        }

This code will do it. GetDs simply returns the dataset from the query, and dbToInsert will go through each row and dump the correct row data as an insert statement. IsIdent dumps back if the tables have an identity column. I should mention this will only work if the table has a primary key. I wrote this a long time ago so you may have to play with it, but itll get you started.
Let me know if you need any help at [e-mail] .

[bhakti.thakkar]

thanks a lot for your help. let me try it out and see what it returns.

al = new ArrayList();
            dt = myHelper.GetDS("SELECT name FROM sys.tables ").Tables[0];
            foreach (DataRow r in dt.Rows)
            {
                al.Add(r["name"].ToString());
            }

            foreach (string i in al)
            {
                    DataTable proc = myHelper.GetDS("select * from " + i).Tables[0];
                    Tables.Add(dbToInsert(proc, i));
            }


        private static string dbToInsert(DataTable proc, string name)
        {
            string insert = "";
            if (isIndent(name)) insert = "SET IDENTITY_INSERT " + name + " ON ; " + "\n";

            string colList = "";

            foreach (DataColumn col in proc.Columns)
            {
                colList += col.ColumnName + ", ";
            }
            colList = colList.Substring(0, colList.Length - 2);

            foreach (DataRow row in proc.Rows)
            {
                insert += "insert into " + name + " (" + colList + ") values(";

                for (int i = 0; i <= proc.Columns.Count - 1; i += 1)
                {
                    string tempstr = row[i].ToString();
                    tempstr = tempstr.Replace("'", "");

                    if (tempstr == "")
                        insert += "NULL, ";
                    else if (proc.Columns[i].DataType == Type.GetType("System.String") ||
                             proc.Columns[i].DataType == Type.GetType("System.DateTime"))
                        insert += "'" + tempstr + "', ";
                    else if (tempstr == "False" || tempstr == "True")
                        insert += (tempstr == "False" ? 0 : 1) + ", ";
                    else
                        insert += tempstr + ", ";
                }
                insert = insert.Substring(0, insert.Length - 2) + "); " + "\n";
            }
            if (isIndent(name)) insert += "SET IDENTITY_INSERT " + name + " OFF ;" + "\n";

            return insert;
        }

        private static bool isIndent(string name)
        {
            string[] str = {
                               "AdminUsers", "ContractorToService", "ListingsToIndustries", "MaintenanceLimits",
                               "ProjectsToIndustries", "ZipcodeToRegion","UserFavorates","sysdiagrams","ListingPrice"
                           };
            foreach (string s in str)
            {
                if (s == name) return false;
            }
            return true;
        }

This code will do it. GetDs simply returns the dataset from the query, and dbToInsert will go through each row and dump the correct row data as an insert statement. IsIdent dumps back if the tables have an identity column. I should mention this will only work if the table has a primary key. I wrote this a long time ago so you may have to play with it, but itll get you started.
Let me know if you need any help at [e-mail] .