August 25, 2011 at 4:10 am
I have given interim (clumsy) instructions for running a set of migration scripts, until I create a more elegant solution. The migration scripts are broken up into 6 files which must be executed in a specific order and are labelled 0-6.
Most of these files comprise of MERGE statements, however, because for two tables, these scripts must be capable of bringing over only data for certain years, I packaged these scripts as parameterized stored procedures which must be executed with the year that needs to come over. Eg. exec procedure select_Eyear 2010
Files 1 and 4 contain create procedure statements which must be run first to create the procedure, and then executed (and finally dropped).
These are the directions I have given :
To run file 0 just hit Execute
To run file 1 you will need to first highlight the script that creates the select_EngagementYear sp, before executing it with: exec select_EngagementYear + the year you want to bring over in format '2011'. Then drop sp.
To run file 2 just hit Execute
To run file 3 just hit Execute
To run file 4 you will again need to first highlight the script that creates the select_vendoryear sp, before executing it with: exec select_vendoryear exec select_EngagementYear + the year you want to bring over in format '2011'. Then drop the sp.
To run file 5 just hit Execute
To run file 6 just hit Execute
It would be better if the tester only had to pull the files into Query Editor and hit execute in all cases. I realize I can position the create statements for the procedures in files 1 and 4, so that the procs are created automatically. But, I want the procedure to run automatically as well.
It was advised I create a temp table to store the value of the year that the tester wants to import, so that the stored procedures can retrieve this @year value automatically. I don't have an idea of what this would look like.
RECAP: I would like to store the year value that the tester wants to bring over in a temp table and then retrieve that value whenever the stored procedure runs, so that the tester only has to specify the year once and just hit 'Execute' for all the scripts.
Thanks.
August 25, 2011 at 8:18 am
Wouldn't this be a lot easier if you used a single file?
These are the directions I have given :
To run file 0 just hit Execute
To run file 1 you will need to first highlight the script that creates the select_EngagementYear sp, before executing it with: exec select_EngagementYear + the year you want to bring over in format '2011'. Then drop sp.
To run file 2 just hit Execute
To run file 3 just hit Execute
To run file 4 you will again need to first highlight the script that creates the select_vendoryear sp, before executing it with: exec select_vendoryear exec select_EngagementYear + the year you want to bring over in format '2011'. Then drop the sp.
To run file 5 just hit Execute
To run file 6 just hit Execute
Add your batch separator between sections. Unless you have changed it in your system it is "go"
Seems like you could make this a LOT easier. Declare your engagement year as a variable. Set the value and execute the whole thing at once.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 25, 2011 at 10:32 am
OK, so what you are saying is combine all files into one file. The batch seperator 'GO' makes certain to execute each individual script seperately anyway, and I can declare the variables at the beginning of the file.
What if I want to maintain the migration scripts in 6 seperate files....just to stay with this department's conventions? You know what I mean? It isn't necessarily more/less logical.
Then would you know how to advise me on the temp table solution (which I would drop in the last file)?
Helen
August 25, 2011 at 10:55 am
Here is a skeleton psuedocode of what I was thinking.
declare @EngagementYear int
set @EngagementYear = [YourValueHere]
--paste contents of file 0 here
GO
--paste contents of file 1 that creates select_EngagementYear
--create procedure....
GO
exec select_EngagementYear @EngagementYear
GO
drop procedure select_EngagementYear
GO
--paste contents of file 2 here
--paste contents of file 3 here
--paste contents of file 4 here that creates select_vendoryear
--create procedure....
GO
exec select_vendoryear @EngagementYear
GO
drop procedure select_vendoryear
GO
--paste contents of file 5 here
--paste contents of file 6 here
--New instructions
-- 1)Open .sql file
-- 2)Put the correct Year in line 2
-- 3)F5
Now if want to keep all your files separate you will have to use either a global temp table or a permanent table. Seems like a global temp table would work in this case. global temp tables are created with ## instead of a single # and they are visible to all connections. You would need to make sure you drop the global temp table at the end of your run or it could stay around a lot longer than you want. One caveat is that the global temp table will be destroyed when the connection that created it is closed. So if the user opens a tab with the first file (and that creates your global temp table) but the user closes that tab (and connection) the global temp table will be gone. It sounds like this approach is pretty risky in your case.
Here is some code you can use to test this.
Open a new tab in SSMS
create table ##MyTable
(
MyValue varchar(20)
)
insert ##MyTable select 'Some Value'
select * from ##MyTable
So far this looks like a normal temp table. Keeping this tab open, open a new tab and run this query.
select * from ##MyTable
You will get results as the global temp table exists.
Now close the original tab (with the create table ##MyTable code).
Try running the select from the second tab again. Invalid object name '##MyTable'.
This is why I suggested putting all your script logic into a single file. It is a LOT less prone to user error especially with people who less technical.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 25, 2011 at 11:00 am
thank you very much. That is a very good argument for not using the global temp table as many tabs are going to be opened and there is a high risk of closing the one declaring the temp table.
Suprrrr, great info. i will digest your pseudocode now.
August 25, 2011 at 12:30 pm
If you get stuck feel free to post back what you have and I will see if we can get it dialed in.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 25, 2011 at 1:49 pm
Maybe you can have them use osql or sqlcmd instead of management studio to execute the scripts. You can then provide them with the 6 files, plus a batch file that executes osql giving the 6 files as parameters to the command line util osql.exe.
August 26, 2011 at 11:31 am
Sean, would you kindly look at this?
I used your pseudocode and added a temp table because I am going to glue all scripts together AND create a temp table to hold the values of the declared variables.
I am almost there but still missing something because I am getting error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@Eyear"
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@Vyear".
declare @Eyear smallint
declare @Vyear smallint
set @Eyear = 2011
set @Vyear = 2011
create table #tempYear
(
Eyear smallint
,Vyear smallint
);
insert into #tempYear (Eyear, Vyear) values (@Eyear, @Vyear);
--paste contents of file 0 here
GO
--paste contents of file 1 that creates select_EngagementYear
--create procedure....
GO
declare @Eyear smallint
set @Eyear = select Eyear from #tempYear
exec select_EngagementYear @EngagementYear
GO
drop procedure select_EngagementYear
GO
--paste contents of file 2 here
--paste contents of file 3 here
--paste contents of file 4 here that creates select_vendoryear
--create procedure....
GO
declare @Vyear = select Vyear from #tempYear;
exec select_vendoryear @EngagementYear
GO
drop procedure select_vendoryear
GO
--paste contents of file 5 here--paste contents of file 6 here
Main question: How do I declare a variable equal to a select statement?
August 26, 2011 at 11:46 am
You don't need a temp table for this at all. It is just adding unnecessary complexity. You have only a single row in this table populated by two variables.
To answer your question about populating a variable, your syntax was close...
select @Eyear = select Eyear from #tempYear
But seriously, if stop and look at what you have done it a lot of overhead.
1) You declare a variable.
2) You populate that variable with 2011
3) You insert that variables value into a temp table.
4) You populate the same variable with the value that you just put into the temp table. (The value is still the same as it was and there is no need to change the value to be the same thing).
I removed most of the extra stuff from your script. Try this and see if this makes sense.
declare @Eyear smallint
declare @Vyear smallint
set @Eyear = 2011
set @Vyear = 2011
--paste contents of file 0 here
GO
--paste contents of file 1 that creates select_EngagementYear
--create procedure....
GO
exec select_EngagementYear @Eyear
GO
drop procedure select_EngagementYear
GO
--paste contents of file 2 here
--paste contents of file 3 here
--paste contents of file 4 here that creates select_vendoryear
--create procedure....
GO
exec select_vendoryear @Vyear
GO
drop procedure select_vendoryear
GO
--paste contents of file 5 here--paste contents of file 6 here
Hope that helps.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 26, 2011 at 11:47 am
And of course if the two variables are always going to have the same value....just use one of them and you don't need both of them. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 26, 2011 at 12:02 pm
solution was to put the select statement into parenthesis.
declare @Eyear smallint
set @Eyear = (select Eyear from #tempYear)
woohoo. Thanks Sean for getting me there. Thanks to both for help.
August 26, 2011 at 12:15 pm
That syntax will work too. I still think you can drop the temp table entirely but that is up to you. 😛 Glad you got it to work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 26, 2011 at 12:16 pm
hxkresl (8/26/2011)
solution was to put the select statement into parenthesis.declare @Eyear smallint
set @Eyear = (select Eyear from #tempYear)
woohoo. Thanks Sean for getting me there. Thanks to both for help.
One last thing that is important to know and understand. In this case you will be fine but if your table has more than one record you need to identify which record to get. This is typically done with either a top 1 and an order by OR with a where clause that will filter to a single record. Otherwise you may not which record you getting the value from for your variable.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 26, 2011 at 12:36 pm
Sean, OK. I had intially tried it without a temp table but was getting an error message indicating the declare and set statements were out of scope.
Now I am able to declare and set them at the top, and be able to call them further down in the scripts. (there are many GOs in between)
August 26, 2011 at 1:03 pm
oh yeah....forgot about needing to break up all the batches like you were doing. Pay no attention to the man behind the screen giving you bad advice. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply