Category Archives: general

Stored Procedure to Generate INSERT Statements for All Table Rows Under SQL Server 2005

If you need to create a “data dump” for Microsoft SQL Server, here’s a good free tool:

http://vyaskn.tripod.com/code.htm#inserts

The one thing I haven’t gotten working is any way to automatically add the “SET INDENTITY_INSERT ON/OFF” statements bracketing a table which contains an identity when not using the @ommit_identity = 1 switch. It might be in the comments, I just didn’t dig through it.

I’ll attach them in case that page ever goes away.

SQL 2000 Version

SQL 2005 Version

80/18/2

I’ve come up with a new 80/20 rule for working with Microsoft development products (but it may be universal):

  • The first 80% is easy, if not already done for you.
  • The second 18% is hard, often requiring an undocumented set of hacks to accomplish.
  • The last 2% is impossible.

In my experience, somewhere around 83% you hit diminishing returns. At 97% you’re bleeding from the eyes.

Copy a SQL Record with LINQ Using Object Shallow Copy

I wish I had kept my reference material on this, so if anyone recognizes borrowed code here, please let me know…

The basic idea is, I needed to “copy” a SQL record within the same table, avoiding some fields. With SQL this could be accomplished with something like:

INSERT INTO ShippingInfo (Name,Value) SELECT Name,Value FROM ShippingInfo where id=5

But imagine there are 20 fields or so. I couldn’t find a native way to do this with LINQ. What I came up with is an object shallow copy with the ability to punch out (omit) fields by name. Here’s the method:

public static class AnyObject<t> where T : class, new()
{
    public static T ShallowCopy(T item)
    {
        return ShallowCopy(item, "");
    }
    
    public static T ShallowCopy(T item, string OmitPropeties)
    {
        if (item == null)
        {
            return null;
        }
        T newItem = new T();
        foreach (System.Reflection.PropertyInfo prop in item.GetType().GetProperties())
        {
            if ((prop.PropertyType.IsValueType || prop.PropertyType.Name == "String") && 
                !prop.PropertyType.Name.StartsWith("EntitySet") &&
                !("|"+OmitPropeties+"|").ToLower().Contains("|"+prop.Name.ToLower()+"|"))
            {
                System.Reflection.PropertyInfo prop2 = item.GetType().GetProperty(prop.Name);
                prop2.SetValue(newItem, prop.GetValue(item, null), null);
            }
        }
        return newItem;
    }
}

Then you call it like…

// get the source record
ShippingInfo TempShipping;
TempShipping = (from s in db.ShippingInfos 
          where s.Id == tempShippingId select s).FirstOrDefault();

// shallow copy the object
ShippingInfo NewShipping = Helpers.AnyObject<shippinginfo>.ShallowCopy(TempShipping, 
                                              "Id|ModifiedBy|ModifiedTime");

// override some copied fields
NewShipping.CreatedBy = PageContext.UserId;
NewShipping.CreatedTime = DateTime.Now;

// insert it
db.ShippingInfos.InsertOnSubmit(NewShipping);
db.SubmitChanges();

Waiting for the Singularity

Someone has finally figured out the real goal over at Google: to create an omniscient AI. Interestingly, I was just thinking about a book I read back in the 70s called The Adolescence of P-1, the first book I encountered that said AI could happen right here, today, and almost accidentally. I wonder if this book actually inspired a generation of computer geeks. I wonder if they ended up as disillusioned as I when it didn’t actually happen. I’ve said it before and I’ll say it again: go Google, go!