Copying Rows On The Same Table and updating Only some fields (TSQL)

We will analyze different scenarios on which we need to copy or clone rows from one table to the same table, only changing the value of one know field, which in this case is the Id field, Using Microsoft SQL Server.

Supposing we are provided the OldId (original Id from were we want to copy the rows, in this case this Id can be on several rows or just one) and a NewId ( the Id we want to put on the row we are going to copy/clone)


If we know the columns and table names before hand:

The first scenario would be a table on which we know before hand the name of the table and all the fields involved, so we can easily write the following query.

DECLARE @OldId int
DECLARE @NewId int
SET @OldId =13456 -- i.e
SET @NewId =45687 -- i.e

INSERT INTO MyTable(Id,ColumnA,ColumnB,ColumnC)
SELECT @NewId,ColumnA,ColumnB,ColumnC
FROM MyTable WHERE Id=@OldId


If we know the table name, but no the column names:

In this case we are forced to use a temp table to be able to update the Id before copying the rows, this is the only way I found since the columns are unknown to us:

DECLARE @OldId int
DECLARE @NewId int
SET @OldId =13456 -- i.e
SET @NewId =45687 -- i.e

--Copy the rows we want to the temp table
SELECT * INTO #Temp FROM MyTable WHERE Id=@OldId

--Update the Id on the temp table    
UPDATE #Temp SET Id = @NewId

--Copy the rows with the new Id back to MyTable
INSERT INTO MyTable SELECT * FROM #Temp

--Drop the temp table     
if object_id(N'tempdb..#Temp', N'U') is not null  DROP TABLE #Temp
     
     
If we ignore both the table name and the columns names:

This scenario forces us to use a dynamic query to build the query string adding the table name and our first attempt would be something like this (and it will FAIL)

DECLARE @TableName varchar(32)
DECLARE @OldId int
DECLARE @NewId int

SET @TableName ='MyTable' --i.e could arrive as a parameter
SET @OldId =13456 -- i.e
SET @NewId =45687 -- i.e

SET @v_SQL = 'SELECT * INTO #Temp FROM ' + @TableName + ' WHERE Id='+  CAST(@OldId as varchar) 
EXEC(@v_SQL)
    
SET @v_SQL = 'UPDATE #Temp SET Id = '+  CAST(@NewId as varchar) 
EXEC(@v_SQL)

SET @v_SQL = 'INSERT INTO ' + @TableName + ' SELECT * FROM #Temp'
EXEC(@v_SQL)

--Drop the temp table     
SET @v_SQL = 'if object_id(N''tempdb..#Temp'', N''U'') is not null  DROP TABLE #Temp'
EXEC(@v_SQL)

     
It fails with error because the Exec() command has its own little scope (that's selfish!) so the temp table is not available for the next Exec statements.

The solution is to use a global temp table "##"  so it will be available for the scope of the rest of the Exec commands. 
This creates another problem on multi user systems, because two users could be running the process at the same time and mix their temp table or get an error. So we have to add something to the temp table name so it will be unique to our process, In this case we use the new Id to differentiate the temp table from other users that may be running the process, but you could use your own (more robust) id generator for that code if you wish.

So the final solution was the following:

DECLARE @TableName varchar(32)
DECLARE @OldId int
DECLARE @NewId int

SET @TableName ='MyTable' --i.e could arrive as a parameter
SET @OldId =13456 -- i.e
SET @NewId =45687 -- i.e

DECLARE @v_SQL varchar(1024)

SET @v_SQL = 'SELECT * INTO ##Temp'+ CAST(@NewId as varchar) +' FROM ' + @TableName + ' WHERE Id = '+  CAST(@OldId as varchar) 
EXEC(@v_SQL)
    
SET @v_SQL = 'UPDATE ##Temp'+ CAST(@NewId as varchar) +' SET Id = '+  CAST(@NewId as varchar) 
EXEC(@v_SQL)

SET @v_SQL = 'INSERT INTO ' + @TableName + ' SELECT * FROM ##Temp'+ CAST(@NewId as varchar)
EXEC(@v_SQL)
     
SET @v_SQL = 'if object_id(N''tempdb..##Temp'+ CAST(@NewId as varchar) +''', N''U'') is not null  DROP TABLE ##Temp'+ CAST(@NewId as varchar)  
EXEC(@v_SQL)
GO


Lets put that as a Store procedure:

CREATE PROCEDURE dbo.CopyWithNewId
(
  @TableName varchar(32),
  @NewId int,
  @OldId int
)
AS
  SET NOCOUNT ON
BEGIN
DECLARE @v_SQL varchar(1024)

SET @v_SQL = 'SELECT * INTO ##Temp'+ CAST(@NewId as varchar) +' FROM ' + @TableName + ' WHERE Id = '+  CAST(@OldId as varchar) 
EXEC(@v_SQL)
    
SET @v_SQL = 'UPDATE ##Temp'+ CAST(@NewId as varchar) +' SET Id = '+  CAST(@NewId as varchar) 
EXEC(@v_SQL)

SET @v_SQL = 'INSERT INTO ' + @TableName + ' SELECT * FROM ##Temp'+ CAST(@NewId as varchar)
EXEC(@v_SQL)
     
SET @v_SQL = 'if object_id(N''tempdb..##Temp'+ CAST(@NewId as varchar) +''', N''U'') is not null  DROP TABLE ##Temp'+ CAST(@NewId as varchar)  
EXEC(@v_SQL)
     
END
GO

That's It !, I hope it can be of any help.

Entity Framework DataAnnotations Cheat Sheet

I have been looking everywhere for a Cheat Sheet like this but could not find anything, So I had to make my own, I hope you find it useful:

DataAnnotations Cheat Sheet v1.0

Name: AssociationAttribute
Description: Specifies that an entity member represents a data relationship, such as a foreign key relationship.
Usage: [Association(Name, OtherKey, ThisKey)]
Example:

[Table(Name = "Customers")]
public partial class Customer
{
    [Column(IsPrimaryKey = true)]
    public string CustomerID;
    // ...
    private EntitySet<Order> _Orders;
    [Association(Storage = "_Orders", OtherKey = "CustomerID")]
    public EntitySet<Order> Orders
    {
        get { return this._Orders; }
        set { this._Orders.Assign(value); }
    }
}

Name: BindableTypeAttribute
Description: Specifies whether a type is typically used for binding.
Usage: [BindableType(IsBindable = bool)]
Example:

  [BindableType(IsBindable = false)]
  public enum EntityState
  {
    Detached = 1,
    Unchanged = 2,
    Added = 4,
    Deleted = 8,
    Modified = 16,
  }

Name: CompareAttribute
Description: Provides an attribute that compares two properties.
Usage: [Compare(OtherProperty)]
Example:

public class Visitor
{
    public string EmailAddress { get; set; }
    [Compare("EmailAddress", ErrorMessage="Email Addresses Do Not Match." )]
    public string ConfirmEmailAddress { get; set; }
}

Name: ConcurrencyCheckAttribute
Description: Specifies that a property participates in optimistic concurrency checks. ConcurrencyCheckAttribute is used to specify that a property/column has a concurrency mode of “fixed” in the EDM model. A fixed concurrency mode means that this property is part of the concurrency check of the entity during save operations and applies to scalar properties only.
Usage: [ConcurrencyCheckAttribute]
Example:

  public class  Book
    {
        public long BookId { get; set; }
        public virtual string Title { get; set; }
        public Author Author { get; set; }
        [ConcurrencyCheckAttribute]
        [TimestampAttribute]
        public byte[] TimeStamp { get; set; }
    }

Name: CreditCardAttribute
Description: Specifies that a data field value is a credit card number.
Usage: [CreditCardAttribute]
Example:

public class Buyer
{
    public string Nombre { get; set; }
    [CreditCardAttribute]
    public string CardNumber { get; set; }
}

Name: CustomValidationAttribute
Description: Specifies a custom validation method that is used to validate a property or class instance.
Usage: [CustomValidation(typeof(Field), "Eval_function")]
Example:

public class CategoryMetadata
   {
      [CustomValidation(typeof(Category), "TestCategoryName")]
      public object CategoryName { get; set; }
   }
 
public static ValidationResult TestCategoryName(string pNewName, ValidationContext pValidationContext)
   {
      if (Regex.IsMatch(pNewName, @"^\d")) // cannot start with a digit
         return new ValidationResult("Cannot start with a digit", new List<string> { "CategoryName" });
      return ValidationResult.Success;
   }

Name: DataTypeAttribute
Description: Specifies the name of an additional type to associate with a data field.
Usage: [DataType(DataType.*)]
Example:

public class DataTypeEntity
{
    [DataType(DataType.Date, ErrorMessage = "Please enter a valid date (ex: 2/14/2011)")]
    public DateTime SaleDate { get; set; }

    [DataType(DataType.EmailAddress)]
    public string EmailAddress { get; set; }
}

Name: DisplayAttribute
Description: Provides a general-purpose attribute that lets you specify localizable strings for types and members of entity partial classes.
Usage: [Display(Name = "*")]
Example:

public class SalesMan
{
    [Required]
    [DisplayName("Full Name :")]
    public string Name { get; set; }

    [Display(Name = "Email address")]
    public string EmailAddress { get; set; }
}

Name: DisplayColumnAttribute
Description: Specifies the column that is displayed in the referred table as a foreign-key column.
Usage: [DisplayColumn("field to use as Foreign Key, Field to use for Sorting, ascending/descending)]
Example:

[DisplayColumn("City", "PostalCode", false)]
public partial class Address
{

}

Name: DisplayFormatAttribute
Description: Specifies how data fields are displayed and formatted by ASP.NET Dynamic Data.
Usage: [DisplayFormat(DataFormatString="*")]
Example:

public class Sale
{
    // Display currency data field in the format $1,345.50.
    [DisplayFormat(DataFormatString="{0:C}")]
    public object StandardCost;
    [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:dd/MM/yyyy hh:mm}")]
    public DateTime MyDate { get; set; }
}

Name: EditableAttribute
Description: Indicates whether a data field is editable.
Usage: [Editable(AllowEdit=bool,AllowInitialValue=bool)]
Example:

public class Person
{
[Editable(AllowEdit=false)]
 public object Name { get; set; }
 }

Name: EmailAddressAttribute
Description: Validates an email address.
Usage: [EmailAddress]
Example:

public class employee
{
public string Name{get; set;}
[EmailAddress]
public string Notification {get; set;}
}

Name: EnumDataTypeAttribute
Description: Enables a .NET Framework enumeration to be mapped to a data column.
Usage: [EnumDataType(typeof(Enum Type))]
Example:

public enum eReorderLvl {
        zero = 0,
        five = 5,
        ten=10,
        fifteen=15,
        twenty=20,
        twenty_five=25,
        thirty=30
    }
 public class Product_MD {
            [EnumDataType(typeof(eReorderLvl))]
            public object ReorderLevel { get; set; }
        }

Name: FileExtensionsAttribute
Description: Validates file name extensions.
Usage: [FileExtensions(Extensions="*")]
Example:

public class Cliente{
    [FileExtensions(Extensions = "csv,txt",ErrorMessage = "Must choose .csv file.")]
    public string ImportFile{ get; set; }
}

Name: FilterUIHintAttribute
Description: Represents an attribute that is used to specify the filtering behavior for a column.
Usage: [FilterUIHint("The name of the control to use for filtering.")]
Example:

MetadataType(typeof(Product_MD))]
public partial class Product {
    private class Product_MD {
        [FilterUIHint("MultiForeignKey")]
        public object Category { get; set; }
        [FilterUIHint("BooleanRadio")]
        public object Discontinued { get; set; }
    }
}

Name: KeyAttribute
Description: Denotes one or more properties that uniquely identify an entity.
Usage: [Key]
Example:

public class Cliente{
    [Key]
    public int Id { get; set; }
    public string name { get; set; }
}

Name: MaxLengthAttribute
Description: Specifies the maximum length of array or string data allowed in a property.
Usage: [MaxLength(Length)]
Example:

public class Customer
{
    public virtual string CustomerID { get; set; }
    [MaxLength(50, ErrorMessage="Name can not be longer than 50 characters." )]
    public virtual string CompanyName { get; set; }
}

Name: MetadataTypeAttribute
Description: Specifies the metadata class to associate with a data model class.It indicates that a data model class has an associated metadata class. The MetadataType attribute gets a type parameter to specify which type is holding the metadata for the class
Usage: [MetadataType(typeof(MetaData))]
Example:

public class CRMTypeMetadata
{
    [ScaffoldColumn(false)]
    public int TypeID { get; set; }
 
    [StringLength(100)]
    public string Url { get; set; }
}
 
[MetadataType(typeof(CRMTypeMetadata))]
public partial class CRMType
{
}

Name: MinLengthAttribute
Description: Specifies the minimum length of array or string data allowed in a property.
Usage: [MinLength(Length)]
Example:

public class Customer
{
    public virtual string CustomerID { get; set; }
    [MinLength(3, ErrorMessage="Name can not be shorter than 3 characters." )]
    public virtual string CompanyName { get; set; }
}

Name: PhoneAttribute
Description: Specifies that a data field value is a well-formed phone number using a regular expression for phone numbers.
Usage: [Phone]
Example:

public class Customer
{
    public virtual string CustomerID { get; set; }
    [Phone]
    public virtual string Phone_Number { get; set; }
}

Name: RangeAttribute
Description: Specifies the numeric range constraints for the value of a data field.
Usage: [Range(Double,Double)], [Range(Int32,Int32)], [Range(Type,String,String)]
Example:

public class Item
{
public int Id { get; set; }
[Range(10, 1000, ErrorMessage = "Value for {0} must be between {1} and {2}.")]
public Double Weight { get; set; };
}

Name: RegularExpressionAttribute
Description: Specifies that a data field value in ASP.NET Dynamic Data must match the specified regular expression.
Usage: [RegularExpression(@"Regular_Expresion")]
Example:

public class Customer
{
    [RegularExpression(@"[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,4}", ErrorMessage = "Please enter correct email")]
     public string Email { get; set; }
 }

Name: RequiredAttribute
Description: Specifies that a data field value is required.
Usage: [Required()]
Example:

public class Customer
{
    [Required]
    public virtual string CustomerID { get; set; }
    public virtual string CompanyName { get; set; }
}

Name: ScaffoldColumnAttribute
Description: Specifies whether a class or data column uses scaffolding.
Usage: [ScaffoldColumn(true)]
Example:

public class ProductMetadata
{
    [ScaffoldColumn(true)]
    public object ProductID;
    [ScaffoldColumn(false)]
    public object ThumbnailPhotoFileName;
}

Name: ScaffoldTableAttribute
Description: Specifies whether a class or data table uses scaffolding.
Usage: [ScaffoldTable(bool)]
Example:

[MetadataType (typeof(ErrorLogMetada))]
[ScaffoldTable(false)]
public partial class ErrorLog
{
 
}

public class ErrorLogMetada
{

}

Name: StringLengthAttribute
Description: Specifies the minimum and maximum length of characters that are allowed in a data field.
Usage: [StringLength(int maximumLength)]
Example:

public class ProductMetadata
{
    [StringLength(4, ErrorMessage = "The ThumbnailPhotoFileName value cannot exceed 4 characters. ")]
    public object ThumbnailPhotoFileName;

}

Name: TimestampAttribute
Description: Specifies the data type of the column as a row version.Also specifies that this column will be included in the Where clause of Update and Delete commands sent to the database, For concurrency cheking.
Usage: [Timestamp]
Example:

public class Person
{
    public Int64 Id { get; set; }
    public string Name { get; set; }
    [Timestamp]
    public Byte[] Timestamp { get; set; }
}

Name: UIHintAttribute
Description: Specifies the template or user control that Dynamic Data uses to display a data field.
Usage: [UIHintAttribute(string uiHint)]
Example:

public partial class ProductMetadata
{
    [UIHint("UnitsInStock")]
    [Range(100, 10000,
    ErrorMessage = "Units in stock should be between {1} and {2}.")]
    public object UnitsInStock;
}

Name: UrlAttribute
Description: Provides URL validation.
Usage: [Url]
Example:

public class MyModel
{
    [Url]
    public string ServerAddress {get; set;}
}

Any contributions to improve the examples would be very welcome.

CodeIgniter's Pagination with a Search Term

On this article I’ll try to explain how to use CodeIgniter’s pagination library with a search term in a way that that our links look like:

Controller\action\search_term\page

CodeIgniter’s pagination library makes it easy for us to show long data lists generating tha links for the corresponding pages automatically, But it lacks a way to include a search term on the paginated results.

The problem is that if we inlcude a search term with the pagination, there is no specific function to include this search teram on the links for the following pages. As an extra complication we will solve this problem including a subset of data, on which we will apply both the pagination and an optional search term. And getting the links like this:

Controller\accion\id\termino de búsqueda\pagina.

We have the following scenario:

 - A list of cities.

 - A list of people who belong to a city.

 - We want to list the people belonging to a determined city, and we want to paginate the results, but also we want to be able to search in this result set and have a paginated result, using the same view.

On the firts page, we have the cities listing:

 <a href="personas/ver/1"> Personas en Ciudad 1</a>

 <a href="personas/ver/2"> Personas en Ciudad 2</a>

 <a href="personas/ver/3"> Personas en Ciudad 3</a>

In person controller must have two actions "show" and "show_search", this second function serves as an intermediate function to pass the data from the search string in the case of a paged search and the first function in case we do not have a search string in which case it works as a normal paginated search.

The first function:

<?php

function show($id_city, $offset = 0){

       //her we verify if the we have to handle a serch term

       //or its comming from “show_search” with a seacr term

       if ( $this->session->userdata('var_search')){

             $search_term=$this->session->userdata('var_search');

             $this->session->unset_userdata('var_search');

       }else{

             $search_term ='';

       }

       $this->load->model('m_person');

      

       //Load the pagination library

       $this->load->library('pagination');

       //We defini the structure of the links in case we have a search term

if ($search_term ==''){

             $config['base_url'] = base_url().'/index.php/person/show/'.$id_city.'/';

             $config['uri_segment'] = '4';

       }else{

             $config['base_url'] = base_url().'/index.php/person/show_search/'.$id_city.'/'.$search_term.'/';

             $config['uri_segment'] = '4';

       }

//this function it’s up to you must count the results for the pagination

//library

       $config['total_rows'] = $this->m_person->number_of_persons($id_city,$seach_term);

       $config['per_page'] = 25;

       $config['num_links'] =5;

       $config['next_link'] = '>';

       $config['prev_link'] = '<';

       $config['first_link'] = '<<';

       $config['last_link'] = '>>';

       $this->pagination->initialize($config);

       $data["page_links"] = $this->pagination->create_links();

       //we obtain the list of people using the offset data and the number of records by page, Again this is up to you

$articulos=$this->m_personas->show_persons_paginated($id_city,$search_term,$offset,$config['per_page']);

       $data['persons']=$persons;

       $data['id_city']=$id_city;

       $this->load->vars($data);

       //cargamos nuestra vista

       $this->load->view('template');

}

?>

On this function we verified if we had a search term, and according to this we changed a Little bit the link we want the pagination to generate.

In case we don’t have a search term, the Base_url for the pagination is normal (and the pagination library will add the page number), In case of having a search term we use the “show_search” action which will be included in the links. This function is the following:

<?php

function show search($id_city,$search_term='',$offset = 0){

       if (empty($_POST)){

                   

       }else{

             try {

                    $search_term=$this->input->post(search_term);

             } catch (Exception $e) {

                    $search_term ='';

             }

       }

       $this->session->set_userdata('var_search_term', $search_term);

       redirect('/personas/show/'.$id_city.'/'.$offset);

}

?>

What this function does is, grab the search term and save it on a session variable, and then redirect to the normal function in which this session variable will be used. And so this little function solves the problem.

On the view in which we show the results and the pagination links, We must have some search form, like the following:

  <div align="center">

       <?php echo form_open('person/show_search/'.$id_city.'/'); ?>

       <?php echo form_input("search_term");?>

       <?php echo form_close();?>

 </div>

I hope this information can help you in the future, and if you have any questions fell free to ask.