How to update database in sql server 2005

I have table in which I am inserting rows for employee but next time when I want to insert row I don't want to insert again data for that employee just want to update with required columns if it exits there if not then create new row How can we do this in SQL Server ? Isn't this better? Sakhile -- Yes it is -- so where is your answer in the Add a comment.

Active Oldest Votes. Employee Col1, NET code. Lee Taylor 6, 14 14 gold badges 28 28 silver badges 43 43 bronze badges. But something like that is definitely not advisable! I am right to assume that the Store Procedure won't handle concurrent requests. If two requests access the same time trying to insert the same primary key, and the primary key does not exist in the table yet, then, the both requests will simultaneously pass the first condition as the select can be run in parallel.

The INSERT, however, needs to be run in exclusion, so one of the queries will add the new record, and the other one will have an exception indicating that there is a duplicate primary key.

Show 2 more comments. To ensure serviceability and technical support, use the. Please see Redist. Doing this ensures that the components can be serviced in future by Microsoft Update.

Follow Microsoft Facebook Twitter. However as we do not have and this cost more I have been looking at the possibility of doing a back up and then a restore. I have tested this out with a very simple db and it seems to work. However I have read that I should change the compatibility level from use to be 90 to My question is, is the necessary? After all some of these old db's are old legacy databases which require very little change.

Is it really worth it? So what I will not be able to use the fancy new ways of doing stuff, but as these are old does that matter? After all its not like it is needed otherwise we would not have created these db.

I am yet to still test with a more complex db but wanted to make sure I am on the right path. Since you are migrating to a new server, you don't need to bring SQL Server into the mix. Paul states:. As for changing the compatibility level, I'd recommend not changing that for several weeks and let the dust settle from the upgrade.

After you're confident that everything is functioning properly from the upgrade, why not bump the compatibility level up to the maximum and monitor performance.

You might even see an improvement in performance. However, compatibility levels from SQL Server and above bring the new cardinality estimator into play, which might present some performance problems. Better business development solutions. Side-by-Side Upgrade : In this upgrade method, a new instance is created on the same server or in a new server. Choosing an Upgrade Method Lets discuss about the advantages and disadvantages of each method. In-place upgrade advantages: It's easier and faster, especially in small systems.

It's mostly an automated process The instance will be offline for a minimum amount of time. The resulting instance after upgrade will have the same name as the original, as the new setup will replace the older version. No additional hardware is required. Disadvantages: It's very complex to rollback. Not applicable in scenarios where you want to upgrade a part of system like upgrading just one single databases. You cannot run an upgrade comparison after doing the upgrade.

Side-by-Side Upgrade advantages More control over the upgrade, as you can upgrade the components, which you want to. You can keep you application running even when you are installing SQL as the old instance or server will be available.

Easy to do a rollback as the original database server is untouched. Manual intervention is required to migrate databases, Jobs, logins. There will be change in configuration settings, which are used by the application to connect to the database.

More time is required while moving VLDB to the new version of database. Preparing for Upgrade Upgrade Advisor Upgrade advisor is a tool available to find out your database compatibility and blocking issues while doing the migration. When you start upgrade advisor, you will see the welcome screen below. Fig 1. At the end of the wizard, you will have a chance to confirm all the options you have selected. Red X symbol means that this error needs to be fixed before upgrading.

A yellow triangle indicates that additional action needs to be taken once upgrade is done. For example in the previous screen shot, there is an error, which says, "Read-only Database cannot be upgraded". This can be done by clicking on link, "Show Affected objects" Things To Do I will break this section into three parts "Before upgrade", "During upgrade" and "After upgrade".

Take proper down time before starting the upgrade. The outage required would depend upon the size of the databases and the environment in which database servers are hosted. In my case we took 48 hours of downtime. Once all the issues and blockers are solved, and all applications are closed, take complete backup of all the databases including master, model and msdb. Also take transactional backup for all user databases and the disable all the jobs including jobs for taking full backups, jobs to defrag and other maintenance plans.

It is also recommended to take an image of your database server, this can be very handy in case you will have to rollback. If the operating system of the server is Windows , make sure that Windows service pack 1 is installed. This needs to be done on all the nodes of cluster in case the database server is on Failover Cluster environment.