When testing database code in development, you usually have a lot of tests that aim to prove that the various processes such as ETL, reporting or the processing of an order, still work as they always did. If it is a changed process, you need to ensure that, from a known dataset, the process yields the result that the business expects. These tests happen as part of both development and deployment.<\/p>\n
These datasets are generally created for each release and maintained to keep in step with the current development, being saved on the local filesystem as native BCP files. Sometimes, all or part of the live data is used, pseudonymised or masked where necessary. It is also handy to generate data, especially when you need large datasets to check that the database can scale appropriately.<\/p>\n
Copying a database using a BCP dataset from a DOS script or from PowerShell is fairly quick and trivial, but what if the data is already in another copy of the database on the same instance? Imagine you have a database build that is a copy of an existing database on the same instance and you want to run a \u2018destructive\u2019 test on the data, and do it over and over again.<\/p>\n
Deleting all the data in a database should, you\u2019d have thought, be trivial. Truncation doesn\u2019t work because it doesn\u2019t like to truncate tables with foreign keys, even if they are disabled. DELETE is safer but you will need to then reset any identity columns. Deleting large tables in one chunk isn\u2019t scalable, so you have to do it in smaller chunks. I like to disable triggers while I do this as well. The only disadvantage comes if you have triggers that are supposed to be disabled because the simple code just re-enables them all.<\/p>\n
Basically, if you are prepared to use sp_MSforeachtable,<\/strong> then this code is all reasonably simple. This will delete all the data from Adventureworks in about half a minute. It does Pubs in a second! Don\u2019t use it without due care! This code is dangerous in untutored hands. You can try it out within a transaction and roll it back while you are testing.<\/p>\n <\/p>\n Now to get that data back in. you have a copy of the database on the same server so you use that. All you need to do, surely is to \u2026<\/p>\n \u2026 for all the tables in the database? No. For a start, you cannot insert into a timestamp column, and you need to SET IDENTITY INSERT TableA ON in order to insert into TableA, which has an identity column. If course, if you try this with a table that hasn\u2019t an identity column you get an error. Inserting XML into an XML column results in an error unless you do an explicit conversion (because of the possibility of a different XML Schema). You have to ignore calculated columns as well. You can\u2019t insert into a calculated column or a timestamp column. I have no idea whether it is possible or desirable to insert into a hidden column so I disallowed that too. Change it to taste.<\/p>\n Out of the box, this code doesn\u2019t work on older versions of SQL Server because I use the lovely string_agg<\/strong> function to generate lists of columns. You can change this to the classic XML trick to get the same result if you are stuck on an old version. Anything older than SQL Server 2016 didn\u2019t have the is_hidden<\/strong> attribute in the sys.columns<\/strong> system view<\/p>\n Here, I have it set, for the sake of demonstration, to delete all the data from a database called AdventureworksCopy, and fill it again from Adventureworks2016. You\u2019ll want to change that!<\/p>\n Am I recommending Kill and Fill? It depends on what sort of testing you are doing. A BCP process is always nice, quick and clean, though it still needs the kill part of the process, but it is best done from a Dos shell script or a PowerShell script. This Kill and Fill process is easy to set up though I don\u2019t think it is any quicker than a bulk process. I just thought I should make it available just in case anyone else needed a different way of filling a development database with data.<\/p>\n","protected":false},"excerpt":{"rendered":" When testing database code in development, you usually have a lot of tests that aim to prove that the various processes such as ETL, reporting or the processing of an order, still work as they always did. If it is a changed process, you need to ensure that, from a known dataset, the process yields……<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[6813],"class_list":["post-87164","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/87164","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=87164"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/87164\/revisions"}],"predecessor-version":[{"id":87167,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/87164\/revisions\/87167"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=87164"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=87164"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=87164"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=87164"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}---firstly delete all existing data\r\nDISABLE TRIGGER ALL ON DATABASE;\r\n--now disable all constraints\r\nEXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';\r\n--delete the data from each table in turn, doing tables with more than 10,000 rows in chuncks\r\nEXEC sp_MSforeachtable '\r\nPrint ''Deleting all the data from ?''\r\nSET QUOTED_IDENTIFIER ON;\r\nDECLARE @SoFarDeleted INT=1;\r\nWHILE (@SoFarDeleted > 0 and @@Error=0)\r\n BEGIN\r\n -- Delete a chunk of rows at a time\r\n DELETE TOP (10000) ?\r\n SET @SoFarDeleted = @@ROWCOUNT;\r\nEND ';\r\n--switch on all constraints\r\nEXEC sp_MSforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL';\r\n-- Reseed identity columns if the table has an identity column\r\nEXEC sp_MSforeachtable 'IF ObjectPropertyEx(Object_Id(''?''),''TableHasIdentity'')=1 DBCC CHECKIDENT (''?'', RESEED, 0)';\r\n-- and enable all triggers\r\nENABLE TRIGGER ALL ON DATABASE;\r\n<\/pre>\nFilling data<\/h2>\n
INSERT INTO tableA SELECT * FROM otherDatabase.tableA;<\/pre>\n
USE AdventureworksCopy;\r\nGO\r\nDECLARE @source sysname = 'Adventureworks2016';-- the source of the database\r\n\r\n---firstly delete all existing data\r\nDISABLE TRIGGER ALL ON DATABASE;\r\n--now disable all constraints\r\nEXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';\r\n--delete the data from each table in turn, doing tables with more than 10,000 rows in chuncks\r\nEXEC sp_MSforeachtable '\r\nPrint ''Deleting all the data from ?''\r\nSET QUOTED_IDENTIFIER ON;\r\nDECLARE @SoFarDeleted INT=1;\r\nWHILE (@SoFarDeleted > 0 and @@Error=0)\r\n BEGIN\r\n -- Delete a chunk of rows at a time\r\n DELETE TOP (10000) ?\r\n SET @SoFarDeleted = @@ROWCOUNT;\r\nEND ';\r\n--switch on all constraints\r\nEXEC sp_MSforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL';\r\n-- Reseed identity columns if the table has an identity column\r\nEXEC sp_MSforeachtable 'IF ObjectPropertyEx(Object_Id(''?''),''TableHasIdentity'')=1 DBCC CHECKIDENT (''?'', RESEED, 0)';\r\n-- and enable all triggers\r\nENABLE TRIGGER ALL ON DATABASE;\r\n--create a table variable giving all the table names so we can do each one\r\nDECLARE @tables TABLE\r\n (\r\n TableName_id INT IDENTITY,\r\n TheObject_ID INT NOT NULL,\r\n TheName sysname NOT NULL,\r\n TheSchema sysname NOT NULL\r\n );\r\n--fill the table with the names of the tables we need to fill\r\nINSERT INTO @tables (TheObject_ID, TheName, TheSchema)\r\n SELECT object_id, name, Object_Schema_Name(object_id) FROM sys.tables;\r\n--and to prevent a generated script getting too untidy ---\r\nDECLARE @CRLF CHAR(2) = '\r\n';\r\n--\tWe disable all constraints now\t \r\nDECLARE @Script NVARCHAR(MAX) =\r\n 'EXEC sp_msforeachtable \"ALTER TABLE ? NOCHECK CONSTRAINT ALL\"' + @CRLF;\r\nDECLARE @ii INT, @iiMax INT; ---our iteration counter\r\nDECLARE @TheTableName sysname, @TheSelectColumns NVARCHAR(MAX),\r\n @TheInsertColumns NVARCHAR(MAX), @TheTable_id INT, @HasIdentityCol INT;\r\n--set our iteration counters\r\nSELECT @ii = Min(TableName_id), @iiMax = Max(TableName_id) FROM @tables;\r\n--we need to have a table with all the source tables in it just in case we\r\n--have an extra table in the destination with no equivalent in the source\r\nDECLARE @sourceTables TABLE (TableName sysname);\r\nDECLARE @Expression NVARCHAR(MAX) = --the expression we execute\r\n N'USE ' + @source\r\n + ' SELECT QuoteName(Object_Schema_Name(t.object_id)) + ''.'' + QuoteName(t.name) FROM sys.tables t';\r\n--and get the list of the tables in the other database\r\nINSERT INTO @sourceTables EXECUTE (@Expression);\r\n--now we can get cracking and generate a script that fills each table in turn\r\nWHILE @ii <= @iiMax\r\n BEGIN\r\n SELECT @TheTable_id = TheObject_ID,\r\n @TheTableName = QuoteName(TheSchema) + '.' + QuoteName(TheName)\r\n FROM @tables\r\n WHERE TableName_id = @ii; --get the table name\r\n SELECT @ii = @ii + 1; --don't fill it if it isn't in the source\r\n IF @TheTableName NOT IN (SELECT TableName FROM @sourceTables) CONTINUE;\r\n SELECT @TheInsertColumns = --get the list of columns in the insert expression\r\n String_Agg(QuoteName(columns.name), ', ') WITHIN GROUP(\r\n ORDER BY column_id ASC),\r\n @TheSelectColumns =--get the list of columns in the select expression\r\n String_Agg( CASE WHEN types.name = 'xml' --convert XML types \r\n\t\t\t\t\tTHEN 'Convert(XML, ' + QuoteName(columns.name) + ')' \r\n\t\t\t\t\tELSE QuoteName(columns.name) END,\r\n ', '\r\n ) WITHIN GROUP(ORDER BY column_id ASC)\r\n FROM sys.columns\r\n INNER JOIN sys.types\r\n ON types.user_type_id = COLUMNS.user_type_id\r\n WHERE object_id = @TheTable_id\r\n AND is_hidden = 0 --don't use hidden columns\r\n AND is_computed = 0--don't use computed columns either\r\n AND types.name NOT LIKE 'timestamp';--don't use timestamp columns\r\n SELECT @HasIdentityCol =Convert(INT,ObjectPropertyEx(@TheTable_id ,'TableHasIdentity'))\r\n SELECT @Script =\r\n @Script\r\n + CASE WHEN @HasIdentityCol > 0 THEN \/*IF there is an identity column we \r\n\t need to set identity insert on for the table *\/\r\n 'SET IDENTITY_INSERT ' + @TheTableName + ' ON ' ELSE '' END\r\n + @CRLF + N'Print ''Adding data to table ' + @TheTableName + N' '''\r\n + @CRLF + N'INSERT INTO ' + @TheTableName + N'(' + @TheInsertColumns\r\n + N') ' + @CRLF + N'SELECT ' + @TheSelectColumns + N' FROM ' + @source\r\n + N'.' + @TheTableName + @CRLF\r\n + CASE WHEN @HasIdentityCol > 0 THEN \/*IF there is an identity column we \r\n\t switched identity insert on so we need to switch it off *\/\r\n 'SET IDENTITY_INSERT ' + @TheTableName + ' OFF' + @CRLF ELSE '' END\r\n + @CRLF;\r\n END;\r\nSELECT @Script =\r\n @Script\r\n + '\r\n EXEC sp_msforeachtable \"ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL\"';\r\n\r\nEXECUTE (@Script);\r\nGO\r\n<\/pre>\nConclusions<\/h2>\n