Discussion between Randolph West and pmdci

pmdci

00:00

all I tried so far about the schema name id dynamic sql because based on my research it is the only way to achieve what i want.

Randolph West

tell me more about the schema problem

pmdci

the idea is that this code will become a store procedure, so I specify the schema name when calling the SP

this is an application we developed which different 'tenancies' for a client lies on different schema names.

and this is a little code that we will run at 01:00 on the first day of every month, to keep it neat.

Randolph West

ah!

pmdci

it works perfectly if I run it manually but ten I need to change the schema name all the time.

Randolph West

multitenancy

ok, here goes ...

wait, hang on

pmdci

00:03

so the idea is that we have a stored procedure, then we have some sql agent jobs to run these...

Randolph West

SET @query = 'INSERT INTO [wl03].ExpectedActivity (period, genusID, subjectID, waitingStageID, value, savedOn, savedBy)
SELECT period, genusID, subjectID, waitingStageID, value, savedOn, savedBy
FROM (SELECT CAST(''' + @plus12Month + ''' AS DATE) AS period, genusId, subjectId, waitingStageId, value, CAST('''
+ CONVERT(CHAR(8), GETDATE(), 112)
+ ''' AS DATE) AS savedOn, ''<Automated>'' AS savedBy FROM [' + @schemaName
+ '].[ExpectedActivity] WHERE period in (''' + @plus11Month + ''')) pm
WHERE NOT EXISTS (SELECT 1

ok

this basically bangs the whole query together into one big piece of dynamic SQL

pmdci

'INSERT INTO [wl03].Exp <-- there is a hard coded schema name here

Randolph West

for the purposes of this example, the resulting code will look like this:

INSERT INTO [wl03].ExpectedActivity (period, genusID, subjectID, waitingStageID, value, savedOn, savedBy)
SELECT period, genusID, subjectID, waitingStageID, value, savedOn, savedBy
FROM (SELECT CAST('20180701' AS DATE) AS period, genusId, subjectId, waitingStageId, value, CAST('20170706' AS DATE) AS savedOn, '<Automated>' AS savedBy FROM [wl03].[ExpectedActivity] WHERE period in ('20180601')) pm
WHERE NOT EXISTS (SELECT 1
FROM [wl03].ExpectedActivity
WHERE period = pm.period
AND genusID = pm.genusID

you could parse out the schema name then ... I was using your sample code

pmdci

lemme try it out

Randolph West

SET @query = 'INSERT INTO [' + @schemaName + '].ExpectedActivity (period, genusID, subjectID, waitingStageID, value, savedOn, savedBy)
SELECT period, genusID, subjectID, waitingStageID, value, savedOn, savedBy
FROM (SELECT CAST(''' + @plus12Month + ''' AS DATE) AS period, genusId, subjectId, waitingStageId, value, CAST('''
+ CONVERT(CHAR(8), GETDATE(), 112)
+ ''' AS DATE) AS savedOn, ''<Automated>'' AS savedBy FROM [' + @schemaName
+ '].[ExpectedActivity] WHERE period in (''' + @plus11Month + ''')) pm

like that

pmdci

00:05

damn you're fast at this stuff!

:)

Randolph West

LOL

20 years

pmdci

humm

something wrong

Violation of PRIMARY KEY constraint 'PK_ExpectedActivity'. Cannot insert duplicate key in object 'wl01.ExpectedActivity'. The duplicate key value is (2018-07-01, 19, 90, 0).
The statement has been terminated.

my original code the WHERE NOT EXISTS statement to avoid such constraint issue

Randolph West

then your if exists needs work

maybe not all columns are accounted for in the PK

pmdci

they are...

the code works fine if it is not a dynamic sql

this is what puzzles me

Randolph West

show me code that works

pmdci

00:10

sure

Randolph West

could just be a minor blip in the dynamic SQL

pmdci

pastebin.com/Gdz2xppr

MSFT needs to come up with something easier than saving the entire query into a string... honestly ;)

Randolph West

hmmm, my code should work -- it's a derived table as opposed to a temp table.. let me switch back to a temp table

one sec

pmdci

so the idea of this code is... we are replicating the value of a month into the next month, IF AND ONLY IF we don't have a value for that month already. The granularity is there...

Randolph West

SET @query = 'SELECT ' + @plus12Month + ' AS period,
genusId,
subjectId,
waitingStageId,
value,
GETDATE() AS savedOn,
''<Automated>'' AS savedBy
INTO
#12thMonthExpectedActivity
FROM
' + QUOTENAME(@schemaName) + '.[ExpectedActivity]
WHERE
period IN (' + @plus11Month + ');

INSERT INTO ' + QUOTENAME(@schemaName) + '.ExpectedActivity
(
period,
genusID,
subjectID,
waitingStageID,
value,
savedOn,
savedBy
)
SELECT
period,
genusID,
subjectID,
waitingStageID,
value,
savedOn,
savedBy
FROM
#12thMonthExpectedActivity pm

this is less than elegant, but I think it'll work

don't forget to close the string at the end -- I've edited my answer but you may have copied before then

pmdci

00:18

lemme check

you mean less elegant just because of the formatting of the text (so many line breaks)?

Randolph West

no, that's easier to read :-)

I've changed your [ + schema + ] to use QUOTENAME() instead, by the way

pmdci

Msg 206, Level 16, State 2, Line 1
Operand type clash: date is incompatible with int

Randolph West

oh that's not escaping the IN() ... one sec

SET @query = 'SELECT ''' + @plus12Month + ''' AS period,
genusId,
subjectId,
waitingStageId,
value,
GETDATE() AS savedOn,
''<Automated>'' AS savedBy
INTO
#12thMonthExpectedActivity
FROM
' + QUOTENAME(@schemaName) + '.[ExpectedActivity]
WHERE
period IN (''' + @plus11Month + ''');

INSERT INTO ' + QUOTENAME(@schemaName) + '.ExpectedActivity
(
period,
genusID,
subjectID,
waitingStageID,
value,
savedOn,
savedBy
)
SELECT
period,
genusID,
subjectID,
waitingStageID,
value,
savedOn,
savedBy
FROM
#12thMonthExpectedActivity pm

give that a shot

pmdci

trying

it is alive :)

thanks a million! you don't do freelance work, do you? ;)

like on www.upwork.com or something like that?

Randolph West

I do, but I'm expensive

pmdci

00:23

what is your rate?

Randolph West

bornsql.ca FWIW

$200 CAD per hour, less if it's more than ten hours

I do forums for free, though ;-)

pmdci

well I'd like to keep you in mind :)

thanks a million for this, i appreciate it.

what is the purpose of quotename() tho?

Randolph West

heh, thanks ... I'm going to clean up the comments on that thread

ok so

QUOTENAME is a way to enforce the [ and ] automatically

docs.microsoft.com/en-us/sql/t-sql/functions/…

pmdci

ah! nice.

Randolph West

I use it for object names specifically

pmdci

00:25

so what was the problem with the constraint in the original code?

Randolph West

schema, table, column, etc.

I don't know what was wrong - probably the derived table needed the NOT EXISTS instead

pmdci

so the revised code went back to the temp table, right?

Randolph West

if I knew the structure better, I might have a better idea, but it's not important for right now

pmdci

that's why... cool

Randolph West

yeah

I have to run

pmdci

00:27

are you hardcore with SSAS as well?

Randolph West

I'm bad at SSAS

pmdci

ok thanks for all of that. I'll definitely keep you in mind for everything T-SQL in the future.

all the best

Randolph West

thanks :-)

Transcript for

Discussion between Randolph West and …