bulk insert with default constraint

Question

I have two table tblPerson and tblGender

the coulmn 'GenderID'in tblPerson is a foriegn key for the coulmn 'ID'(primary) in the tblGender

here is a default contarint

Alter Table tblPerson
    Add constraint DF_tblPerson_GenderID
    Default 4 for GENDERID

now the insert query is

insert into tblPerson (ID,[Name],EmailID,GenderID)values
    (4,'Riya','[email protected]',2),
    (5,'Raj','[email protected]',3),
    (6,'Tisha','[email protected]',2),
    (7,'Stephen','[email protected]'); // here im getting the error

now as you can see in the 7 I'm trying to add nothing in the genderid column and i thought it will be considered as a default constraint

but it is giving me error 'The number of columns for each row in a table value constructor must be the same.'

Please edit the tags and tell us what kind of RDBS. SQL Server? Oracle? SQLLite? They probably all do this differently — Nick.Mc, Dec 08 '22 at 06:37

Nick.Mc · Accepted Answer · 2022-12-08T06:40:48.343

This is a T-SQL (SQL Server) specific answer. You should tag your question with the actual RDBMS. Your code looks like T-SQL with the [ and ] but who knows....

Since you can't change the column list, you need to use the default keyword to trigger a default.

INSERT INTO tblPerson (ID, [Name], EmailID, GenderID) VALUES
    (4, 'Riya', '[email protected]', 2),
    (5, 'Raj', '[email protected]', 3),
    (6, 'Tisha', '[email protected]', 2),
    (7, 'Stephen', '[email protected]',default)

See here for an example. This is a T-SQL answer for a SQLLite question.

How to insert default values in SQL table?

I can't find a clear example of this in the T-SQL docs. This has a very vague description

https://learn.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver16

bulk insert with default constraint

1 Answers1