Update Query

[JCAA]

Hi,

I got a new SQL Server database and are in the process of porting my Access databases to my new SQL Server. I have worked with Access and Oracle in the past but I am new to the SQL Server environment. I have a tool called ConVersion that is helping me rewrite my queries in Access into SQL Server procedures. I got a few of them to be error free but when I run them I get a message stating that there is already data in a table with that name. Why is it not overwriting my previous data if I have a create table statement? What can I do to overwrite my old data and repopulate it in Access I could just have a create table and it would overwrite my old data with new data, is this not possible in SQL Server? Do I have to have two procedures one delete procedure and one create to get this done? I would apprecate all the help I can get.

Thanks,

JCA

 

[SQLSister]

OK this depends on what you are trying to do. It is not generally a good idea to be continually dropping and recreating tables.

If you are creating a temporary data set, then use table variables or temp tables within your stored procedure. This will ensure that mulitple users don't imterfere with each other's use of the temporary data set.

If you are importing new data on a regular schedule, then use truncate table to empty the table without logging the deletions rather than dropping and recreating the table, and then the insert statement. You can also do this in a DTS package. You need to consider the effect of continually getting rid of data and replacing it. Will there be tables with relationships that will be affected by this? If so, you may need to drop the realtionship first, import the data and then recreate the relationship. If you do this though, then you need to check to make sure you don't have any orphaned rows in the related table because the main row was eliminated.

Do you really need to do that or can you update existing rows and then add any new rows and then delete any old rows which are not in the import data? This can be a more effective strategy if you have a relatively easy way to determine that a row has been changed such as a ModifiedDate field. In this case maybe you only need to update a few rows and add a few. The data is always still available to the user while you are making the change which it isn't if you drop and recreate the table. However, if you have a large data set and no way of telling what changed besides checking everyone fo several hundred fields, it may be faster to truncate the data nd replace it.

You do not need two stored procedures to accomplish your tasks, you can have many steps in one stored procedure. If you want us to tell you what the problem is with your current sp, you would have to post it, so we can see what is is actually doing vice what you might think it is doing.



Questions about posting. See faq183-874