SQL

  • Import a large .sql file into SQL Server using the command line

    Import large sql file using command line

    A lot of the time we deal with small databases with small data. Importing a backup database is usually ok for these databases using the SQL Server Management Studio. We would normally just open the .sql file we want to import in Management Studio and then click on the Execute button to import it into a new or existing database. Sometimes this just isn't possible. Large databases or big tables mean that the overhead required for SQL to parse and run the scripts just isn't possible.

  • Copy data from one table to another in the same database

    There was an issue that cropped up in work today where we noticed there was a table that had been setup incorrectly that had no auto increment column for handling IDs. One of the junior developers found the bug and was telling me that he was going to export out all of the data from the table into an excelsheet, re-import the data into a new table and setup an auto increment column that way.

  • Hertz and Aerlingus team up for new project HertzFlyDrive.com, designed and developed by Dragnet Systems

    HertzFlyDrive.com - A new car rental site from Hertz and Aerlingus, designed and developed by Dragnet Systems

    After many months of work I'm proud to announce my latest project HertzFlyDrive.com. This website was designed from the ground up provide Hertz with a more effective way to manage their car rental booking information and help speed up the booking process for end users.

  • How to reset the primary key value in SQL Server

    Usually when testing a database I fill it with loads of dummy data. This helps both me and the client see what their site will look like once real data is in their system. Just before the site goes live it's always a good idea to remove the test data, especially if you have user accounts with test as the username and password!

    However, if you delete the contents of a table then you will notice that the primary ID values will continue to auto increment from the last number that was in your table. So if you had 100 rows of temp data and removed them then the next entry to the system will be 101. This should not be an issue for your database or code but sometimes it's nice to reset these values too. In SQL you can do this by running the following command:

    DBCC CHECKIDENT (tablename, reseed, 0)
    

    Tablename should obviously be the name of your table that you want to reset the primary key value in. It should be pointed out that this code isn't just for resetting to zero. You could put 350 as the number in place of 0 and SQL would start the next entry in your table to 351.

    As you can see setting the value of a primary key is quite an easy thing to do within SQL.

  • Limit the text returned from a field using SQL

    In most databases today there are columns that contain thousands of characters of text. In some cases this information needs to be returned to an end user on a busy section of your site, for example a search feature.

    If someone does a search on your site usually you will be displaying only a portion of that content when displaying the search results. You could very easily return the full content of all your columns to the end user and use your code to stip off the excess data but a much faster way is to strip off what you don't need in SQL before you pass back to your code. One way of doing this is to use the SubString method in SQL Server:

    SUBSTRING(ColumnName, start position, end position)

    Example below returns the value within PostDetails from the start position up to 150 characters long and calls this new 'field' PostSummary

    SUBSTRING(PostDetails,1,150) as PostSummary
    

    Full example:

    Select PosterName, PostDate, SUBSTRING(PostDetails,1,150) as PostSummary
    From Posts
    Where <<search conditions entered from search page>>
    

    You can easily call PostSummary just like you call PosterName or PostDate in your code view.

    SQL
  • Return Top Row from Left Join using SQL Server 2005

    I recently had to do a Left Join on my SQL statement to get images relating to houses in my database. The database results had to give me the properties, regardless of whether they had images or not hence the Left Join as apposed to an inner join. The problem I had was that just doing something like below would not work. SQL would return all your properties but you get the same image for every house:

     

    SELECT P.PropertyID, P.PropertyRef, PI.ImageFile
    FROM Property AS P  
    LEFT JOIN(
    Select TOP 1 ImageFile, PropertyIDFK
    From PropertyImages PI
    ) PI
    P.PropertyID = PI.PropertyIDFK
    

     

    What I needed to do in this situation was use an OUTER APPLY statement. This then allowed me to return all of my properties with the first image uploaded from the database. Very nice. For more information on the APPLY function in SQL Server 2005 you can read some blogs here or here.

    Below is the end SQL statement for using TOP function on an OUTER JOIN:

    SELECT P.PropertyID, P.PropertyRef, PI.ImageFile
    FROM Property AS P  
    OUTER APPLY (
    Select TOP 1 ImageFile, PropertyIDFK
    From PropertyImages PI
    WHERE P.PropertyID = PI.PropertyIDFK
    ORDER BY PI.PropertyImagesID
    ) PI
    

     

    SQL

Updates

Follow me online at TwitterFacebook or Flickr. If you want to get in touch with me try emailing me.

Latest Tweets