|
I have to do an upgrade of MSSQL database like this :
I have folders that each contain an sql script
I must have a sql or batch script that allows me to upgrade my database by running scripts that exist in these folders depending on the version of the current database:
Example:
folders
1 Folder has the name: v2.00
2 Folder has the name: v3.00
3 Folder has the name: v4.00
the initial version of the database: v3.00
the version of the final database: v4.00
So I have to have a script (or batch) that allows me to:
execute just the proper scripts (in this case v3.00 and v4.00)
|
|
|
|
|
How do you determine the "version" of the database?
|
|
|
|
|
there is a table which contains this information 
Table "TVersion" and field "FVersion"
|
|
|
|
|
khaliloenit wrote: I must have a sql or batch script
In that case you'll need to write one.
You could try to do it completely from SQL, but that requires the xp_cmdshell sproc. Alternatively you write a batch-file for DOS, executing your scripts using the isql command.
Easiest way might be a powershell-script.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Have you an example how to do it ?
Best,
|
|
|
|
|
Which of the three versions?
..but no, not for that specific scenario.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
You can use the sqlcmd utility from a windows batch file e.g.
@ECHO OFF
FOR /F %%i IN ('sqlcmd -S YourDBInstance -Q"set nocount on;select CONVERT(DECIMAL(10,2),FVersion) from TVersion"') DO SET VNO=%%i
call .\v%VNO%\script.bat
@echo done.
Things to note
- you'll have to insert your own information for the -S parameter
- Do not put spaces around the = sign in SET VNO=%%i
- The conversion in the query is necessary to ensure the .00 is returned correctly
- you will need to substitute your script name for script.bat
|
|
|
|
|
I'm looking for a way of joining two tables using a function returning a ranking value where the rows joined would be decided on the returning value of a function.
Assume I'm joining table X and Table Y using a function f(X,Y)
If the values returned by the function from a cartesian join would be:
I would want this "excluding functional join" to return:
Y1,X3
Y2,X1
Y3,X2
Is this at all possible without using procedural code?
<edit>What I want is something similar to the Gale-Shapley[^] algorithm but using a special text search instead of simple preferences.
It's easy enough to do in procedural code, but I want to avoid an RBAR solution</edit>
modified 17-Jun-16 2:34am.
|
|
|
|
|
Jörgen Andersson wrote: without using procedural code
I do doubt it.
One way might be to create a separate view that generates the ranking (can't see the PK field) and use the view in the join.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: can't see the PK field
That's because they're of no use in this case.
I'm trying to map two tables in two different databases on their description fields, and some descriptions are basically the same but with added information for some rows, and those are the big problem.
I'm going to try to do it using a recursive CTE.
|
|
|
|
|
Jörgen Andersson wrote: m going to try to do it using a recursive CTE.
why am I not surprised by that. Your cte skills seem to be quite adequate. Let us know if you achieve a solution
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I believe I have found one, the proper test will be on Monday. But as it is done using a recursive CTE, it's not the most effective one.
I'm going to have another look at Richards version tonight.
|
|
|
|
|
In the end I ended up using a variant of Richards suggestion.
|
|
|
|
|
How about something like this:
- Cross-join the two tables;
- Generate the ranking value for each pair;
- Generate a
ROW_NUMBER, partitioned by the primary key of Y, and ordered by the ranking value (descending); - Select the rows where the row number is
1;
WITH cteRanked As
(
SELECT
X.PK As XPK,
X.OtherColumnsFromX,
Y.PK As YPK,
Y.OtherColumnsFromY,
RankingFunction(X.Value, Y.Value) As R
FROM
X CROSS JOIN Y
),
cteOrdered As
(
SELECT
XPK,
OtherColumnsFromX,
YPK,
OtherColumnsFromY,
ROW_NUMBER() OVER (PARTITION BY YPK ORDER BY R DESC) As RN
FROM
cteRanked
)
SELECT
XPK,
OtherColumnsFromX,
YPK,
OtherColumnsFromY
FROM
cteOrdered
WHERE
RN = 1
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Not bad, but it's not going all the way. I need to partition it by both X and Y.
|
|
|
|
|
I must be missing something - surely, if you partitioned by both X and Y, you'd end up with the Cartesian product of the two tables? 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Well, that, is exactly my problem! 
|
|
|
|
|
The data turned out to be a lot less ordered or rather more asymmetrical than expected (just like I should've expected  )
And the mapping between the databases also turned out to be important in only one direction.
So I ended up using a variant of your solution, but using RANK instead of ROW_NUMBER.
Now I'm working on refining the ranking function for whenever I get duplicate matches
|
|
|
|
|
I have a database table with the following schema
CREATE TABLE [dbo].[TestScores](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Student] [nvarchar](25) NOT NULL,
[English] [nvarchar](25) NULL,
[Physics] [nvarchar](25) NULL,
[Mathematics] [nvarchar](25) NULL,
[Engineering] [nvarchar](25) NULL,
PRIMARY KEY (ID)
)
// Sample Data
Insert Into TestScores (Student, English, Physics, Mathematics, Engineering)
Values ('Jane', A, B, A, A);
Insert Into TestScores (Student, English, Physics, Mathematics, Engineering)
Values ('Michelle', A, A, B, A);
Insert Into TestScores (Student, English, Physics, Mathematics, Engineering)
Values ('Dan, A, A, A, B);
I would like to display the results like the following
Student Jane Michelle Dan
English A A A
Physics B A A
Mathematics A B A
Engineering A A B
I wanted to first Unpivot the data then Pivot the data but I just could not use the Pivot operator on the given data.
Below is my query
Select * from TestScores
Unpivot(Course for Courses in (Student, English, Physics, Mathematics, Engineering))as upv
Pivot(course for Student in (Jane, Michelle, Dan)) as pv
modified 12-Jun-16 9:19am.
|
|
|
|
|
|
You have a problem with your Unpivot - try removing 'Student' from the column list i.e.
Unpivot(Course for Courses in (English, Physics, Mathematics, Engineering))as upv
There is also a problem with the Pivot - you need to use a summary function e.g.
Pivot(MAX(course) for Student in (Jane, Michelle, Dan)) as pv
Unfortunately if you fix those two problems you get the following results
ID Courses Jane Michelle Dan
1 Engineering A NULL NULL
2 Engineering NULL A NULL
3 Engineering NULL NULL B
1 English A NULL NULL
2 English NULL A NULL
3 English NULL NULL A
1 Mathematics A NULL NULL
2 Mathematics NULL B NULL
3 Mathematics NULL NULL A
1 Physics B NULL NULL
2 Physics NULL A NULL
3 Physics NULL NULL A
This is where the article mentioned by @Mycroft-Holmes comes into play OR you can fiddle it like this
select Courses, MAX(ISNULL(Jane,'')) as Jane, MAX(ISNULL(Michelle,'')) as Michelle, MAX(ISNULL(Dan,'')) as Dan
from
(Select * from TestScores
Unpivot(Course for Courses in (English, Physics, Mathematics, Engineering))
as upv ) UPV
Pivot(MAX(Course) for Student in (Jane, Michelle, Dan)) as pv
GROUP BY Courses
|
|
|
|
|
Thank you so much for replying. Your solution is 98 percent there, the only problem is that the courses in the result are listed out of order.
In my scenario the order of the courses in the result is trivial. However, in other cases where I have to let's say show companies' earnings by quarter, show companies' stocks by quarter, etc., it will be important.
How can I modify my solution to show the courses in their original order? Thanks again for your help.
modified 12-Jun-16 14:49pm.
|
|
|
|
|
The only solution that comes to mind at the moment (I'll keep thinking!) is to have a separate table listing the courses with an Id field that reflects the order you require e.g.
DECLARE @courseNames table (id int identity(1,1), course nvarchar(25))
insert into @courseNames values ('English'),('Physics'),('Mathematics'),('Engineering')
It could just as easily be a permanent table as a table variable. You could use such a table to generate dynamic SQL for the PIVOT if more courses are added).
You can then join the previous query to this table and ORDER BY the id on the course table e.g.
select cn.id, Courses, MAX(ISNULL(Jane,'')) as Jane, MAX(ISNULL(Michelle,'')) as Michelle, MAX(ISNULL(Dan,'')) as Dan
from
(Select * from TestScores
Unpivot(Course for Courses in (English, Physics, Mathematics, Engineering))
as upv ) UPV
Pivot(MAX(Course) for Student in (Jane, Michelle, Dan)) as pv
inner join @courseNames cn ON pv.Courses=cn.course
GROUP BY cn.id, Courses
ORDER BY cn.id or if you don't want to include the ID number in the output you could use a CTE ...
;with q as
(
select Courses, MAX(ISNULL(Jane,'')) as Jane, MAX(ISNULL(Michelle,'')) as Michelle, MAX(ISNULL(Dan,'')) as Dan
from
(Select * from TestScores
Unpivot(Course for Courses in (English, Physics, Mathematics, Engineering))
as upv ) UPV
Pivot(MAX(Course) for Student in (Jane, Michelle, Dan)) as pv
GROUP BY Courses
)
select q.Courses, q.Jane, q.Michelle, q.Dan
FROM q
inner join @courseNames cn ON q.Courses=cn.course
ORDER BY cn.id
|
|
|
|
|
Thank you for all your help, your solution using table variable works like a charm.
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
