How can I faketable and then insert a record with an identity column with tSqlT

[Dave Hall]

I'm sure I'm missing something here but can't find any documentation to point me in the right direction, I'm trying to fake a number of upstream tables but the stored procedure won't allow me to create it (seemingly as it's not taking the fake table into account):

EXEC tSQLt.NewTestClass 'DepartmentTests';

GO

Drop PROC if EXISTS DepartmentTests.testInsertIntoDepartments

GO

CREATE PROCEDURE DepartmentTests.testInsertIntoDepartments

AS

BEGIN

Exec tSQLt.FakeTable 'Funds'

Exec tSQLt.FakeTable 'Ledgers'

Exec tSQLt.FakeTable 'Activities'

Insert into Funds

Select 1, 'TEST', 'Test Fund', 1

Insert into Ledgers

Select 1, 'TEST', 'Test Ledger', 1

Insert into Activities

Select 1, 'TEST', 'Test Activity', 1

Declare @Code varchar(max) = 'TestDepartment'

Declare @Title varchar(max) = 'Test Department'

Declare @FundId int = 1

Declare @LedgerId int = 1

Declare @Enabled bit = 1

Declare @AvailableToAllSchools bit = 1

Declare @DefaultFinanceCodeId int = NULL

Declare @ActivityId int = 1

Drop table if EXISTS Expected

Select *

into Expected

From Departments

Insert into Expected

Select 2, @Code, @Title, @FundId, @LedgerId, @Enabled, @AvailableToAllSchools, 

@DefaultFinanceCodeId, @ActivityId

Insert into Departments

Select @Code, @Title, @FundId, @LedgerId, @Enabled, @AvailableToAllSchools, @DefaultFinanceCodeId, @ActivityId

EXEC tSQLt.AssertEquals 'Departments', 'Expected'

END;

GO

EXEC tSQLt.RunAll

On the proc creation I'm getting:

Msg 8101, Level 16, State 1, Procedure testInsertIntoDepartments, Line 10 [Batch Start Line 5]

An explicit value for the identity column in table 'Funds' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Where am I going wrong? I can get it to work if I go for @identity = 1 and exclude the primary keys in the inserts but I'd rather get it working without so as to make more complex tests simpler.

[Dave Hall]

Hiya, cracked it, just need to give the inserts to fake tables a column list and all works as expected. Might be worth a note to the  [@Identity = ]  section on https://tsqlt.org/user-guide/isolating-dependencies/faketable/, is that something in GitHub? Happy to submit a PR if so.

[Sebastian Meine]

Hi Dave,

Glad you figured it out. 

On a side note, you always want to have a column list in your tests to make sure that they keep working even if you add an unrelated column to that table later. 

About the instructions, we’ve been planning to move them to GitHub pages for a while, but haven’t gotten to it yet. Do you know someone who might be interested in helping getting that done?

Thanks

Sebastian

--
You received this message because you are subscribed to the Google Groups "tSQLt" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tsqlt+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/362e517b-e267-485d-a87f-f1c3764d7474n%40googlegroups.com.

--

Thanks,

Sebastian

[Bryant McClellan]

Dave,

I was about to say the same. Omitting a column list is like SELECT *. Sure, it may save a couple of keystrokes today but later changes cause things to break for the wrong reasons. And I'm not just referring to tests.

G Bryant McClellan
Senior Solutions Architect
260.748.5393 (direct)

Do it Best Corp.
6502 Nelson Road | Fort Wayne, IN 46803

To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/CAC7EguVZ4awyBO83nV%3DQ8VhhG3j-wVQPoPbzeoQd2dkKp4SJ_Q%40mail.gmail.com.

[Dave Hall]

Thanks Sebastian, GitHub pages isn't something I've used before, but can't see it being unlearnable so happy to have a stab.

Dave.

[Sebastian Meine]

Hi Dave,

Thank you for offering to collaborate with us on that. We just created a docs folder with an initial index.md file. The new "site" is accessible under http://docs.tsqlt.org (https pending).

Do you want to take a stab at creating a single entry (for example for tSQLt.FakeTable) and linking it from the main index page?

Thanks

Sebastian

To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/9a77649c-cd94-44a8-a5ec-c43b8c68745fn%40googlegroups.com.

[Dave Hall]

Lovely, will do, thanks!