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

24 views
Skip to first unread message

Dave Hall

unread,
Aug 5, 2021, 3:51:54 AMAug 5
to tSQLt

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

unread,
Aug 11, 2021, 4:59:04 AMAug 11
to tSQLt
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

unread,
Aug 11, 2021, 8:05:12 AMAug 11
to ts...@googlegroups.com
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

unread,
Aug 11, 2021, 8:12:01 AMAug 11
to ts...@googlegroups.com
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




https://www.blueskyz.com/doitbest/sgselect.asp?eventid=212

Dave Hall

unread,
Aug 12, 2021, 10:54:07 AMAug 12
to tSQLt
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

unread,
Aug 14, 2021, 6:47:03 AMAug 14
to ts...@googlegroups.com
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


Dave Hall

unread,
Aug 16, 2021, 4:57:52 AMAug 16
to tSQLt
Lovely, will do, thanks!
Reply all
Reply to author
Forward
0 new messages