0

We have a system that includes a DB with tens of tables, some with evolving DATA and some with rather static Steering information.

The system is now running within a number of environments, one is the DEVEL that the others serve various purposes (including "PRODUCTION").

On behalf of data security, there is no data-link between the PRODUCTION and any other and hence updates must be done using files.

There are some tables that can be completely copied from DEVEL to PRODUCTION white there are other tables that only a selected set of records should be copied.

Since the update process (e.g. to PRODUCTION) will take place many times in the future, I would very much like to write a simple script that will write another script to be ran on the target environment (e.g. PRODUCTION) with all the required updates for the selected set of tables (I already have a similar working script for the "PROGRAMMABILITY" part).

I'm looking for SQL Server (via Management Studio) commands I could use to generate a script with all the insert records for both cases (i.e. some tables without filters while others with).

Edit

Here follows some sample data (just to demonstrate what I am looking for).

Sample Data

In this example, the users will have access to the contents of this table to all the records belonging the the "Public" category, the "System" category will be hidden to them though parts of the system's functionality will be governed by this last category.

Whenever we update the functionality of the system, we may need to modify the contents of one or more collections under the "System" category. As such, it would be best if we could simply delete all the records with Categoty = "System" and Collection_Name = "Status" (in this example of course) and insert the new updated list, all that without touching the categories managed by the users ("Public" in the example).

If course, you would ask "Why don't you split it into two separate tables?" Good question, and the answer is we simply can't.

halfer
  • 19,824
  • 17
  • 99
  • 186
FDavidov
  • 3,505
  • 6
  • 23
  • 59
  • I think you're looking for SQL source control software – Charlieface Oct 24 '21 at 11:50
  • Hi @Charlieface, no, I'm not. Source control could serve for Programmability but (and here I'm not 100% that my information is up-to-date) it cannot resolve the problem of "partiality" for the case of tables copy. – FDavidov Oct 24 '21 at 11:59
  • You would just make a script that would load up those tables, place that script in source control. It can then load to whichever server you choose. – Charlieface Oct 24 '21 at 12:00
  • As charlieface writes - if every change you make to your development database is done via a script, then you simply apply those scripts in the correct order to your production database. Otherwise you are looking for a source-control system for your database as also already noted. However, tool recommendations are off-topic. Try searching "sql server data compare" and "sql server ssdt" as a start. – SMor Oct 24 '21 at 12:18
  • Dear @Smor, I truly appreciate your efforts to assist, but I think you are advocating changing my way of thinking instead of addressing my specific questions. There are very many reasons in the base of the presented approach, reasons that I cannot include in my post for it would be consider too long and not specific. So, again, if you have knowledge about the availability of commands are requested, please share it here. If not, I would still be thankful for your efforts. – FDavidov Oct 24 '21 at 12:45
  • @FDavidov can you please be more specific about your comment "white there are other tables that only a selected set of records should be copied."? How are you wishing to filter these records and why? – GrahamH Oct 24 '21 at 12:55
  • @GrahamH, there are some tables that host "collections" of information. Some of these collections contain stable steering data while others may be changed by selected users (in production environment). As such, the collections containing stable steering information may in some cases need to be updated while the other collections must remain untouched. And yes, I'm aware of the fact that those two groups could be deployed in different tables (for stable and not) but the system is already running (and hence too much effort to split) and some stable collections might become user-controllable. – FDavidov Oct 24 '21 at 13:06
  • @GrahamH (continued). The idea is to programmatically create (in SSMS) a script that include deletion of the stable collections from the table and then insert statements for all the deleted collections (with the updated contents). Hope this clarified things. – FDavidov Oct 24 '21 at 13:08
  • @FDavidov please provide soma sample table/data that demonstrates your problem with your collections / stable steering information. – GrahamH Oct 24 '21 at 13:20
  • @GrahamH, I'll EDIT the question with an example only (cannot share real data... sorry). It will take me some time though... – FDavidov Oct 24 '21 at 13:23
  • @FDavidov There are no "commands" that you can run that will do what you want. You may know what "stable", "collections", "user-controlled", etc mean but there is nothing obvious in your schema or database that can be used to automate this process. And "writing a simple script that will write a script ..." is usually very complicated task in tsql - "simple" just does not belong in that statement. And don't overlook the problem of aligning primary and foreign keys. – SMor Oct 24 '21 at 14:10
  • @SMor, thanks for your feedback. Matters like "foreign keys" and others were certainly considered while thinking about the mechanism. Besides that, sometimes it is worth making some efforts to build something that will be used over and over and make sure it is robust and reliable. As for "script that writes a script", I've done this type of things very many times, including cross languages. In many cases, it would appear (and in my case it was) to be the most elegant solution at hand (at that time at least). Last, as stated, I did build such script for the Programmability part and it works OK. – FDavidov Oct 25 '21 at 15:41
  • @GrahamH, I updated the question with some additional information. Hope things will be cleared. – FDavidov Oct 25 '21 at 16:18

0 Answers0