Member Avatar for 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

  • 2 Contributors
  • 2 Replies
  • 122 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by bhakti.thakkar

Dani AI

Generated

asked for a way to produce CREATE + INSERT scripts for a table without using the Database Publishing Wizard. ’s C# approach that programmatically emits INSERT statements is a useful starting point, but there are several practical gaps to watch for when generating scripts yourself: handling binary/varbinary and XML columns, preserving Unicode, formatting datetimes reliably, representing NULLs, preserving GUIDs and identity values, and restoring tables in a dependency-safe order. For medium/large tables, emitting huge single-statement scripts is fragile and slow; a bulk-export/import method is usually better.

A simple, reliable workflow:

  1. Script DDL (CREATE TABLE, indexes, constraints) with SQL Server Management Studio’s Generate Scripts feature ().
  2. Export data using an approach that matches scale and fidelity: the BCP tool for fast, typed data files (BCP utility), SSIS when you need transformations, or a programmatic SMO solution when you must produce INSERT statements ().
  3. Test restore on a copy: load DDL, then data, taking care with constraints and rebuilds.

If you want quick INSERT text for small tables, build each VALUES clause carefully (escape single quotes by doubling them, prefix Unicode literals with N, format datetime with an unambiguous style, and render binary as hex). Example for a couple of columns:

SELECT 'INSERT INTO dbo.YourTable (Name, Age, Created) VALUES ('''
     + REPLACE(Name, '''', '''''') + ''','
     + CAST(Age AS VARCHAR(10)) + ','''
     + CONVERT(VARCHAR(23), Created, 121) + ''');'
FROM dbo.YourTable;

Troubleshooting tips: generate parent tables before children or temporarily disable constraints when loading multiple tables, process large tables in batches, and always verify collation/encoding and binary data handling. For production or repeatable jobs prefer BCP/SSIS or a tested SMO-based script rather than hand-rolled INSERT dumps.

Recommended Answers

All 2 Replies

Member Avatar for 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 .

Member Avatar for 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 .

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.