January 2, 2013 at 7:26 am
Hi,
I'm totally new to SQL Server *.sql command files. I'd like to create a SQL file that runs the initialisation of the whole DB, as I need it often. Something like deleting values in certain tables, updating values in others, so that after running it the DB looks exactly like before use.
My main doubt is: Can all these commands be placed one after another, like:
DELETE FROM tblIDNames
....
UPDATE tblIDValues SET IDNumber=0, .....
etc
or is anything else needed for this to work (vars, declarations....)?
A link or a simple example would be appreciated. Thanks in advance, A.
January 2, 2013 at 8:12 am
Why in that way? Why don't you use backup and restore? Or you can make script for DB creation, with or without data from SSMS.
January 2, 2013 at 9:37 am
a_ud (1/2/2013)
My main doubt is: Can all these commands be placed one after another, like:DELETE FROM tblIDNames
....
UPDATE tblIDValues SET IDNumber=0, .....
etc
Sure. Use a batch separator to separate different "chunks" of code that you want to run together. e.g.
DECLARE @variable_name INT;
SET @variable_name = 10;
DELETE FROM tblIDNames WHERE nameID < @variable_name;
GO -- << GO is the default batch separator for SSMS query windows and sqlcmd.exe scripts
UPDATE tblIDValues SET IDNumber=0
WHERE nameID < @variable_name; -- this would generate an error because @variable_name does not exist in this scope, it was declared in a previous batch
GO
When were you thinking of running your script? Many times I restore a copy of a production database into a development environment and I want to run a script after the restore completes to cleanse sensitive data as well as change settings in parameters tables to reflect the development environment.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 2, 2013 at 10:03 am
Thaks opc.three, that makes it clearer.
For me scripts are way more practical than restores. With the scripts all I want is to delete dummy data that I use while developing the DB.
January 2, 2013 at 10:10 am
Makes perfect sense. You're quite welcome.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 7, 2013 at 10:51 pm
Why not use:
TRUNCATE TABLE tblIDNames
Instead of:
DELETE FROM tblIDNames
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 8, 2013 at 2:18 am
I just didn't think of it, but thank you. I've checked the TRUNCATE command and apparently one of the best things about it is that it doesn't fire the triggers (which my DB has in abondance!).
January 8, 2013 at 2:20 am
a_ud (1/8/2013)
I just didn't think of it, but thank you. I've checked the TRUNCATE command and apparently one of the best things about it is that it doesn't fire the triggers (which my DB has in abondance!).
Yes and it will generally run faster than DELETE because it doesn't update the logs either.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 8, 2013 at 7:53 am
dwain.c (1/8/2013)
a_ud (1/8/2013)
I just didn't think of it, but thank you. I've checked the TRUNCATE command and apparently one of the best things about it is that it doesn't fire the triggers (which my DB has in abondance!).Yes and it will generally run faster than DELETE because it doesn't update the logs either.
It is true that TRUNCATE TABLE will generally run faster than DELETE for non-trivial amounts of data but all activity associated with the statement is still written to the transaction log. With TRUNCATE TABLE it is only page deallocations that are logged instead of the actual row deletions on those pages which is what makes it faster than DELETE.
There are a couple behavioral things to be aware of with TRUNCATE that I thought I would pass along:
1. TRUNCATE TABLE resets an IDENTITY column to its initial seed value, which may be a good thing in a test environment, but maybe not.
2. You cannot truncate a table that is referenced by a foreign key.
USE tempdb;
GO
CREATE TABLE a (id INT NOT NULL PRIMARY KEY);
GO
CREATE TABLE b (id INT NOT NULL REFERENCES a(id));
GO
TRUNCATE TABLE a;
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 8, 2013 at 5:23 pm
opc.three (1/8/2013)
dwain.c (1/8/2013)
a_ud (1/8/2013)
I just didn't think of it, but thank you. I've checked the TRUNCATE command and apparently one of the best things about it is that it doesn't fire the triggers (which my DB has in abondance!).Yes and it will generally run faster than DELETE because it doesn't update the logs either.
It is true that TRUNCATE TABLE will generally run faster than DELETE for non-trivial amounts of data but all activity associated with the statement is still written to the transaction log. With TRUNCATE TABLE it is only page deallocations that are logged instead of the actual row deletions on those pages which is what makes it faster than DELETE.
Good point of clarification that I forgot about.
opc.three (1/8/2013)
There are a couple behavioral things to be aware of with TRUNCATE that I thought I would pass along:1. TRUNCATE TABLE resets an IDENTITY column to its initial seed value, which may be a good thing in a test environment, but maybe not.
2. You cannot truncate a table that is referenced by a foreign key.
USE tempdb;GO
CREATE TABLE a (id INT NOT NULL PRIMARY KEY);
GO
CREATE TABLE b (id INT NOT NULL REFERENCES a(id));
GO
TRUNCATE TABLE a;
GO
But isn't it true that you can TRUNCATE TABLE b first and then TRUNCATE TABLE a?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 8, 2013 at 5:28 pm
dwain.c (1/8/2013)
But isn't it true that you can TRUNCATE TABLE b first and then TRUNCATE TABLE a?
No. In my repro code both tables are empty. I am thinking it has to do with the fact that since TRUNCATE TABLE operates on pages and not rows that FKs are not available to be checked (and they didn't code up an exception) so the operation is completely disallowed.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 8, 2013 at 6:27 pm
opc.three (1/8/2013)
dwain.c (1/8/2013)
But isn't it true that you can TRUNCATE TABLE b first and then TRUNCATE TABLE a?No. In my repro code both tables are empty. I am thinking it has to do with the fact that since TRUNCATE TABLE operates on pages and not rows that FKs are not available to be checked (and they didn't code up an exception) so the operation is completely disallowed.
That's an interesting constraint that I did not know about.
There is a (somewhat messy) workaround:
CREATE TABLE a (id INT NOT NULL PRIMARY KEY);
GO
CREATE TABLE b (id INT NOT NULL);
GO
ALTER TABLE b
ADD CONSTRAINT fk_a
FOREIGN KEY (id)
REFERENCES a(id)
INSERT a SELECT 1 UNION ALL SELECT 2
INSERT b SELECT 1 UNION ALL SELECT 2
TRUNCATE TABLE b;
ALTER TABLE b
DROP CONSTRAINT fk_a
TRUNCATE TABLE a;
ALTER TABLE b
ADD CONSTRAINT fk_a
FOREIGN KEY (id)
REFERENCES a(id)
DROP TABLE b;
DROP TABLE a;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 9, 2013 at 8:00 am
Needing to know the DDL is a pain. That part could be made to be dynamic although I would be compelled to drop 'somewhat' from 'somewhat messy' at that point 😀 If you're in a test environment and you need to clear tons of data, what you showed might be the best option.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply