What is one to many relationship in database?

One to many is a relationship between two tables where each primary table record should be associated with zero or one or more than one record in the secondary table.

Table relationship

David is a citizen of India

He may have more than one mobile number or may not have even single mobile number.

The same way, he may have multiple set of shoes or may not have.

The same way, he may have many friends or may not have.

David doesn't have any car now. He may have one or more cars in feature.

How to implement this scenario in database level?

Step 1. Create the UserMaster table.(Primary/Master table).
                     CREATE TABLE [dbo].[UserMaster](
	                    [UserId] [int] IDENTITY(1,1) NOT NULL,
	                    [FirstName] [varchar](100) Not NULL,
	                    [LastName] [varchar](100) NULL,
	                    [DateOfBirth] [datetime] Not NULL,
                    PRIMARY KEY CLUSTERED 
                    (
	                    [Id] ASC
                    ))
                
Step 2. Create the ContactInfomation Table(Secondary table).
                       CREATE TABLE [dbo].[ContactInfomation](
	                        [ContactId] [int] IDENTITY(1,1) NOT NULL,
	                        [ContactNumber] [varchar](10) NOT NULL,
	                        [UserId] [int] NOT NULL,
                                CONSTRAINT [PK_AadharMaster_ContactId] PRIMARY KEY CLUSTERED 
	                        (
		                        [ContactId] ASC
	                        )
                        ) ON [PRIMARY]
                        GO
                    
Step 3. Create the Foreign key relationship between these two tables.
                        ALTER TABLE [dbo].[ContactInfomation]
                        WITH CHECK ADD CONSTRAINT [FK_ContactInfomation_UserId] FOREIGN KEY([UserId])
                        REFERENCES [dbo].[UserMaster] ([UserId])
                        GO
                        ALTER TABLE [dbo].[ContactInfomation] CHECK CONSTRAINT [FK_ContactInfomation_UserId]
                        GO
                    

Validating the tables

Step 1 : Insert the David's information in "UserMaster" table
                        Insert Into [dbo].[UserMaster]([FirstName],[LastName],[DateOfBirth])Values('David','J','1990-05-25')
                    
Step 2 : Insert the David's Contact information in "ContactInfomation" table
                    Insert Into [dbo].[ContactInfomation](UserId,ContactNumber)values(1,'917**5342*')
                    Insert Into [dbo].[ContactInfomation](UserId,ContactNumber)values(1,'842**5002*')
                    
Verify the data
one to many

We have implemented the one to many relationship with SQL tables.

Insert some more user data
                    
           Insert Into [dbo].[UserMaster]([FirstName],[LastName],[DateOfBirth])Values('Ramkumar','M','1990-01-08')
           Insert Into [dbo].[UserMaster]([FirstName],[LastName],[DateOfBirth])Values('John','A','1995-12-01')
           GO
           Select * from [dbo].[UserMaster]
           Select * from [dbo].[ContactInfomation]
           GO
           Insert Into [dbo].[ContactInfomation](UserId,ContactNumber)values(3,'9931*657**')
           GO
           Select * from [dbo].[UserMaster]
           Select * from [dbo].[ContactInfomation]
           GO
           
one to many with zero
Mobile number is unique across the users.
The data accuracy and consistency purpose, we have to create a unique index on "ContactNumber" column in "[dbo].[ContactInfomation]" table.
                                                                                                                         
                    CREATE UNIQUE INDEX Idxu_ContactInfomation_ContactNumber ON [dbo].[ContactInfomation] (ContactNumber);
                    GO
                    Insert Into [dbo].[ContactInfomation](UserId,ContactNumber)values(2,'9931*657**')
                    
We cannot insert same mobile number for more than one user. one to one
Getting record from [dbo].[UserMaster] and [dbo].[ContactInfomation] using join
                        Select * from [dbo].[UserMaster] u 
                    left join [dbo].[ContactInfomation] c on u.UserId = c.UserId                    
                    
one-to-many-with-join
Entity diagram with one to many relationship
one-to-many-entity-relationship

Complete script for one to many relation with sample data.

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MaHSN]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[MaHSN](
	[HSNId] [int] IDENTITY(1,1) NOT NULL,
	[HSNCode] [varchar](50) NOT NULL,
	[HSNDescription] [varchar](100) NULL,
 CONSTRAINT [PK_MaHSN] PRIMARY KEY CLUSTERED 
(
	[HSNId] ASC
))
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MaTax]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[MaTax](
	[TaxId] [int] IDENTITY(1,1) NOT NULL,
	[TaxName] [varchar](100) NOT NULL,
	[CGSTCaption] [varchar](50) NULL,
	[CGSTTaxRate] [numeric](18, 2) NULL,
	[SGSTCaption] [varchar](50) NULL,
	[SGSTTaxRate] [numeric](18, 2) NULL,
	[IGSTCaption] [varchar](50) NULL,
	[IGSTTaxRate] [numeric](18, 2) NULL,
	[UGSTCaption] [varchar](50) NULL,
	[UGSTTaxRate] [numeric](18, 2) NULL,
 CONSTRAINT [PK_MaTax] PRIMARY KEY CLUSTERED 
(
	[TaxId] ASC
)) 
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MaUnit]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[MaUnit](
	[UnitId] [int] IDENTITY(1,1) NOT NULL,
	[UnitDesc] [varchar](50) NOT NULL,
 CONSTRAINT [PK_UnitId] PRIMARY KEY CLUSTERED 
(
	[UnitId] ASC
)) 
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PrMaster]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[PrMaster](
	[PrId] [int] IDENTITY(1,1) NOT NULL,
	[PrCode] [varchar](50) NULL,
	[PrDesc] [varchar](500) NULL,
	[PrUnit] [varchar](50) NULL,
	[Pr_HSNId] [int] NULL,
	[Pr_TaxID] [int] NULL,
	[PrOpenBalance] [numeric](18, 2) NULL,
	[PrPurchaseRate] [numeric](18, 2) NULL,
	[PrSalesRate] [numeric](18, 2) NULL,
	[Pr_UnitId] [int] NULL,
 CONSTRAINT [PK_PrMaster] PRIMARY KEY CLUSTERED 
(
	[PrId] ASC
)) 
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MaState]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[MaState](
	[StateID] [int] IDENTITY(1,1) NOT NULL,
	[StateName] [varchar](100) NOT NULL,
	[StateCode] [varchar](10) NOT NULL,
 CONSTRAINT [PK_MaState] PRIMARY KEY CLUSTERED 
(
	[StateID] ASC
)) 
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MaParty]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[MaParty](
	[PaID] [int] IDENTITY(1,1) NOT NULL,
	[PaName] [varchar](50) NULL,
	[PaAddress1] [varchar](200) NULL,
	[PaAddress2] [varchar](200) NULL,
	[PaAddress3] [varchar](200) NULL,
	[PaPINCode] [varchar](10) NULL,
	[PaGSTN] [varchar](50) NULL,
	[PaPAN] [varchar](25) NULL,
	[PaMailId] [varchar](100) NULL,
	[PaStateID] [int] NULL,
	[PaTypeID] [int] NULL,
	[PaMobileNo] [varchar](50) NULL,
 CONSTRAINT [PK_MaParty] PRIMARY KEY CLUSTERED 
(
	[PaID] ASC
)) 
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InvoiceMain]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[InvoiceMain](
	[InvoiceId] [int] IDENTITY(1,1) NOT NULL,
	[InvoiceNo] [int] NOT NULL,
	[InvoiceDate] [datetime] NOT NULL,
	[InvoicePaID] [int] NOT NULL,
	[InvoiceGrossAmount] [numeric](18, 2) NOT NULL,
	[InvoiceTaxAmount] [numeric](18, 2) NOT NULL,
	[InvoiceRoundOff] [numeric](18, 2) NOT NULL,
	[InvoiceNetAmount] [numeric](18, 2) NOT NULL,
	[InvoiceInsertData] [datetime] NULL,
	[InvoiceStatus] [int] NULL,
 CONSTRAINT [PK_InvoiceMain] PRIMARY KEY CLUSTERED 
(
	[InvoiceId] ASC
)) 
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InvoiceSub]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[InvoiceSub](
	[InvoiceSubId] [int] IDENTITY(1,1) NOT NULL,
	[InvoiceId] [int] NOT NULL,
	[InvoicePrId] [int] NOT NULL,
	[InvoicePrQty] [numeric](18, 2) NOT NULL,
	[InvoicePrRate] [numeric](18, 2) NOT NULL,
	[InvoiceTaxId] [int] NOT NULL,
	[InvoiceCGSTAmount] [numeric](18, 2) NULL,
	[InvoiceSGSTAmount] [numeric](18, 2) NULL,
	[InvoiceIGSTAmount] [numeric](18, 2) NULL,
	[InvoiceUGSTAmount] [numeric](18, 2) NULL,
 CONSTRAINT [PK_InvoiceSub] PRIMARY KEY CLUSTERED 
(
	[InvoiceSubId] ASC
)) 
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CompanyInfo]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CompanyInfo](
	[CompanyId] [int] IDENTITY(1,1) NOT NULL,
	[CompanyName] [varchar](100) NULL,
	[CompanyHOAddress1] [varchar](100) NULL,
	[CompanyHOAddress2] [varchar](100) NULL,
	[CompanyHOAddress3] [varchar](100) NULL,
	[CompanyHOPINCode] [varchar](50) NULL,
	[CompanyBOAddress1] [varchar](100) NULL,
	[CompanyBOAddress2] [varchar](100) NULL,
	[CompanyBOAddress3] [varchar](100) NULL,
	[CompanyBOPINCode] [varchar](50) NULL,
	[CompanyMobileNo] [varchar](100) NULL,
	[CompanyEMailId] [varchar](150) NULL,
	[CompanyGSTN] [varchar](50) NULL,
	[CompanyBankName] [varchar](250) NULL,
	[CompanyBankACNo] [varchar](100) NULL,
	[CompanyBankIFSCCode] [varchar](100) NULL,
	[CompanyBankBranch] [varchar](100) NULL,
	[CompanyPAN] [varchar](100) NULL,
PRIMARY KEY CLUSTERED 
(
	[CompanyId] ASC
)) 
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Default_InvoiceStatus]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[InvoiceMain] ADD CONSTRAINT [Default_InvoiceStatus] DEFAULT ((1)) FOR [InvoiceStatus]
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Default_OpeningBalance]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[PrMaster] ADD CONSTRAINT [Default_OpeningBalance] DEFAULT ((0)) FOR [PrOpenBalance]
END
go
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PrMaster_MaHSN]') AND parent_object_id = OBJECT_ID(N'[dbo].[PrMaster]'))
ALTER TABLE [dbo].[PrMaster]  WITH CHECK ADD CONSTRAINT [FK_PrMaster_MaHSN] FOREIGN KEY([Pr_HSNId])
REFERENCES [dbo].[MaHSN] ([HSNId])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PrMaster_MaHSN]') AND parent_object_id = OBJECT_ID(N'[dbo].[PrMaster]'))
ALTER TABLE [dbo].[PrMaster] CHECK CONSTRAINT [FK_PrMaster_MaHSN]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PrMaster_MaTax]') AND parent_object_id = OBJECT_ID(N'[dbo].[PrMaster]'))
ALTER TABLE [dbo].[PrMaster]  WITH CHECK ADD  CONSTRAINT [FK_PrMaster_MaTax] FOREIGN KEY([Pr_TaxID])
REFERENCES [dbo].[MaTax] ([TaxId])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PrMaster_MaTax]') AND parent_object_id = OBJECT_ID(N'[dbo].[PrMaster]'))
ALTER TABLE [dbo].[PrMaster] CHECK CONSTRAINT [FK_PrMaster_MaTax]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PrMaster_MaUnit]') AND parent_object_id = OBJECT_ID(N'[dbo].[PrMaster]'))
ALTER TABLE [dbo].[PrMaster]  WITH CHECK ADD  CONSTRAINT [FK_PrMaster_MaUnit] FOREIGN KEY([Pr_UnitId])
REFERENCES [dbo].[MaUnit] ([UnitId])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PrMaster_MaUnit]') AND parent_object_id = OBJECT_ID(N'[dbo].[PrMaster]'))
ALTER TABLE [dbo].[PrMaster] CHECK CONSTRAINT [FK_PrMaster_MaUnit]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_InvoiceMain_MaParty]') AND parent_object_id = OBJECT_ID(N'[dbo].[InvoiceMain]'))
ALTER TABLE [dbo].[InvoiceMain]  WITH CHECK ADD  CONSTRAINT [FK_InvoiceMain_MaParty] FOREIGN KEY([InvoicePaID])
REFERENCES [dbo].[MaParty] ([PaID])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_InvoiceMain_MaParty]') AND parent_object_id = OBJECT_ID(N'[dbo].[InvoiceMain]'))
ALTER TABLE [dbo].[InvoiceMain] CHECK CONSTRAINT [FK_InvoiceMain_MaParty]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_InvoiceSub_InvoiceMain]') AND parent_object_id = OBJECT_ID(N'[dbo].[InvoiceSub]'))
ALTER TABLE [dbo].[InvoiceSub]  WITH CHECK ADD  CONSTRAINT [FK_InvoiceSub_InvoiceMain] FOREIGN KEY([InvoiceId])
REFERENCES [dbo].[InvoiceMain] ([InvoiceId])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_InvoiceSub_InvoiceMain]') AND parent_object_id = OBJECT_ID(N'[dbo].[InvoiceSub]'))
ALTER TABLE [dbo].[InvoiceSub] CHECK CONSTRAINT [FK_InvoiceSub_InvoiceMain]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_InvoiceSub_MaTax]') AND parent_object_id = OBJECT_ID(N'[dbo].[InvoiceSub]'))
ALTER TABLE [dbo].[InvoiceSub]  WITH CHECK ADD  CONSTRAINT [FK_InvoiceSub_MaTax] FOREIGN KEY([InvoiceTaxId])
REFERENCES [dbo].[MaTax] ([TaxId])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_InvoiceSub_MaTax]') AND parent_object_id = OBJECT_ID(N'[dbo].[InvoiceSub]'))
ALTER TABLE [dbo].[InvoiceSub] CHECK CONSTRAINT [FK_InvoiceSub_MaTax]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_InvoiceSub_PrMaster]') AND parent_object_id = OBJECT_ID(N'[dbo].[InvoiceSub]'))
ALTER TABLE [dbo].[InvoiceSub]  WITH CHECK ADD  CONSTRAINT [FK_InvoiceSub_PrMaster] FOREIGN KEY([InvoicePrId])
REFERENCES [dbo].[PrMaster] ([PrId])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_InvoiceSub_PrMaster]') AND parent_object_id = OBJECT_ID(N'[dbo].[InvoiceSub]'))
ALTER TABLE [dbo].[InvoiceSub] CHECK CONSTRAINT [FK_InvoiceSub_PrMaster]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_MaParty_PaStateID]') AND parent_object_id = OBJECT_ID(N'[dbo].[MaParty]'))
ALTER TABLE [dbo].[MaParty]  WITH CHECK ADD  CONSTRAINT [FK_MaParty_PaStateID] FOREIGN KEY([PaStateID])
REFERENCES [dbo].[MaState] ([StateID])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_MaParty_PaStateID]') AND parent_object_id = OBJECT_ID(N'[dbo].[MaParty]'))
ALTER TABLE [dbo].[MaParty] CHECK CONSTRAINT [FK_MaParty_PaStateID]
GO
IF NOT Exists(Select * from [dbo].[MaHSN])
Begin
SET IDENTITY_INSERT [dbo].[CompanyInfo] ON 
INSERT [dbo].[CompanyInfo] ([CompanyId], [CompanyName], [CompanyHOAddress1], [CompanyHOAddress2], [CompanyHOAddress3], [CompanyHOPINCode], [CompanyBOAddress1], [CompanyBOAddress2], [CompanyBOAddress3], [CompanyBOPINCode], [CompanyMobileNo], [CompanyEMailId], [CompanyGSTN],
[CompanyBankName], [CompanyBankACNo], [CompanyBankIFSCCode], [CompanyBankBranch], [CompanyPAN]) VALUES (1, N'Your Company Name', N'35G, Annai Sathya Nagar ********************', N'', N'', N'', N'***********************************', N'JG Complex , Nirmala Nagar******************',
N'', N'', N'9843******', N'testmail@gmail.com', N'33CBMPM6394****', N'', N'', N'', N'', N'')
SET IDENTITY_INSERT [dbo].[CompanyInfo] OFF
End
GO
IF NOT Exists(Select * from [dbo].[MaHSN])
Begin
SET IDENTITY_INSERT [dbo].[MaHSN] ON 
INSERT [dbo].[MaHSN] ([HSNId], [HSNCode], [HSNDescription]) VALUES (1, N'2301', N'FLOURS-MEALS')
INSERT [dbo].[MaHSN] ([HSNId], [HSNCode], [HSNDescription]) VALUES (2, N'07139010', N'DAL')
INSERT [dbo].[MaHSN] ([HSNId], [HSNCode], [HSNDescription]) VALUES (3, N'250100', N'SALT')
INSERT [dbo].[MaHSN] ([HSNId], [HSNCode], [HSNDescription]) VALUES (4, N'15121920', N'SUNFLOWER OIL')
INSERT [dbo].[MaHSN] ([HSNId], [HSNCode], [HSNDescription]) VALUES (5, N'1513', N'COCONUT')
INSERT [dbo].[MaHSN] ([HSNId], [HSNCode], [HSNDescription]) VALUES (6, N'0910910', N'SPICES')
INSERT [dbo].[MaHSN] ([HSNId], [HSNCode], [HSNDescription]) VALUES (7, N'802', N'NUTS')
INSERT [dbo].[MaHSN] ([HSNId], [HSNCode], [HSNDescription]) VALUES (8, N'040900', N'NATURAL HONEY')
INSERT [dbo].[MaHSN] ([HSNId], [HSNCode], [HSNDescription]) VALUES (9, N'1006', N'RICE')
INSERT [dbo].[MaHSN] ([HSNId], [HSNCode], [HSNDescription]) VALUES (10, N'0405', N'DAIRY PRODUCTS')
INSERT [dbo].[MaHSN] ([HSNId], [HSNCode], [HSNDescription]) VALUES (11, N'3401', N'BATH SOAP')
SET IDENTITY_INSERT [dbo].[MaHSN] OFF
End
GO
IF NOT Exists(Select * from [dbo].[MaTax])
Begin
SET IDENTITY_INSERT [dbo].[MaTax] ON 
INSERT [dbo].[MaTax] ([TaxId], [TaxName], [CGSTCaption], [CGSTTaxRate], [SGSTCaption], [SGSTTaxRate], [IGSTCaption], [IGSTTaxRate], [UGSTCaption], [UGSTTaxRate]) VALUES (1, N'CGST 9% AND SGST 9%', N'CGST 9%', CAST(9.00 AS Numeric(18, 2)), N'SGST 9%', CAST(9.00 AS Numeric(18, 2)), N'IGST', CAST(0.00 AS Numeric(18, 2)), N'UGST', CAST(0.00 AS Numeric(18, 2)))
INSERT [dbo].[MaTax] ([TaxId], [TaxName], [CGSTCaption], [CGSTTaxRate], [SGSTCaption], [SGSTTaxRate], [IGSTCaption], [IGSTTaxRate], [UGSTCaption], [UGSTTaxRate]) VALUES (2, N'CGST 2.5% & SGST 2.5%', N'CGST 2.5%', CAST(2.50 AS Numeric(18, 2)), N'SGST 2.5%', CAST(2.50 AS Numeric(18, 2)), N'IGST', CAST(0.00 AS Numeric(18, 2)), N'UGST', CAST(0.00 AS Numeric(18, 2)))
INSERT [dbo].[MaTax] ([TaxId], [TaxName], [CGSTCaption], [CGSTTaxRate], [SGSTCaption], [SGSTTaxRate], [IGSTCaption], [IGSTTaxRate], [UGSTCaption], [UGSTTaxRate]) VALUES (3, N'CGST 6% & SGST 6%', N'CGST 6%', CAST(6.00 AS Numeric(18, 2)), N'SGST 6%', CAST(6.00 AS Numeric(18, 2)), N'IGST', CAST(0.00 AS Numeric(18, 2)), N'UGST', CAST(0.00 AS Numeric(18, 2)))
INSERT [dbo].[MaTax] ([TaxId], [TaxName], [CGSTCaption], [CGSTTaxRate], [SGSTCaption], [SGSTTaxRate], [IGSTCaption], [IGSTTaxRate], [UGSTCaption], [UGSTTaxRate]) VALUES (4, N'IGST 18%', N'CGST', CAST(0.00 AS Numeric(18, 2)), N'SGST', CAST(0.00 AS Numeric(18, 2)), N'IGST 18%', CAST(18.00 AS Numeric(18, 2)), N'UGST', CAST(0.00 AS Numeric(18, 2)))
INSERT [dbo].[MaTax] ([TaxId], [TaxName], [CGSTCaption], [CGSTTaxRate], [SGSTCaption], [SGSTTaxRate], [IGSTCaption], [IGSTTaxRate], [UGSTCaption], [UGSTTaxRate]) VALUES (5, N'IGST 5%', N'CGST', CAST(0.00 AS Numeric(18, 2)), N'SGST', CAST(0.00 AS Numeric(18, 2)), N'IGST 5%', CAST(5.00 AS Numeric(18, 2)), N'UGST', CAST(0.00 AS Numeric(18, 2)))
INSERT [dbo].[MaTax] ([TaxId], [TaxName], [CGSTCaption], [CGSTTaxRate], [SGSTCaption], [SGSTTaxRate], [IGSTCaption], [IGSTTaxRate], [UGSTCaption], [UGSTTaxRate]) VALUES (6, N'IGST 12%', N'CGST', CAST(0.00 AS Numeric(18, 2)), N'SGST', CAST(0.00 AS Numeric(18, 2)), N'IGST 12%', CAST(12.00 AS Numeric(18, 2)), N'UGST', CAST(0.00 AS Numeric(18, 2)))
SET IDENTITY_INSERT [dbo].[MaTax] OFF
End
GO
IF NOT Exists(Select * from [dbo].[MaUnit])
Begin
SET IDENTITY_INSERT [dbo].[MaUnit] ON 
INSERT [dbo].[MaUnit] ([UnitId], [UnitDesc]) VALUES (3, N'Dozen')
INSERT [dbo].[MaUnit] ([UnitId], [UnitDesc]) VALUES (2, N'Litre(s).')
INSERT [dbo].[MaUnit] ([UnitId], [UnitDesc]) VALUES (1, N'No(s).')
SET IDENTITY_INSERT [dbo].[MaUnit] OFF
End

GO
IF NOT Exists(Select * from [dbo].[PrMaster])
Begin
SET IDENTITY_INSERT [dbo].[PrMaster] ON 
INSERT [dbo].[PrMaster] ([PrId], [PrCode], [PrDesc], [PrUnit], [Pr_HSNId], [Pr_TaxID], [PrOpenBalance], [PrPurchaseRate], [PrSalesRate], [Pr_UnitId]) VALUES (1, N'001', N'BOILED RICE -5KG BAG', N'No(s).', 9, 1, CAST(10.00 AS Numeric(18, 2)), CAST(250.00 AS Numeric(18, 2)), CAST(275.00 AS Numeric(18, 2)), 1)
INSERT [dbo].[PrMaster] ([PrId], [PrCode], [PrDesc], [PrUnit], [Pr_HSNId], [Pr_TaxID], [PrOpenBalance], [PrPurchaseRate], [PrSalesRate], [Pr_UnitId]) VALUES (2, N'002', N'BASMATI RICE 1 KG BAG', N'No(s).', 9, 1, CAST(10.00 AS Numeric(18, 2)), CAST(85.00 AS Numeric(18, 2)), CAST(100.00 AS Numeric(18, 2)), 1)
INSERT [dbo].[PrMaster] ([PrId], [PrCode], [PrDesc], [PrUnit], [Pr_HSNId], [Pr_TaxID], [PrOpenBalance], [PrPurchaseRate], [PrSalesRate], [Pr_UnitId]) VALUES (3, N'003', N'WHEAT FLOUR 5 KG BAG', N'No(s).', 1, 1, CAST(10.00 AS Numeric(18, 2)), CAST(75.00 AS Numeric(18, 2)), CAST(85.00 AS Numeric(18, 2)), 1)
INSERT [dbo].[PrMaster] ([PrId], [PrCode], [PrDesc], [PrUnit], [Pr_HSNId], [Pr_TaxID], [PrOpenBalance], [PrPurchaseRate], [PrSalesRate], [Pr_UnitId]) VALUES (4, N'004', N'BUTTER 100 GRAM', N'No(s).', 10, 3, CAST(5.00 AS Numeric(18, 2)), CAST(70.00 AS Numeric(18, 2)), CAST(75.00 AS Numeric(18, 2)), 1)
INSERT [dbo].[PrMaster] ([PrId], [PrCode], [PrDesc], [PrUnit], [Pr_HSNId], [Pr_TaxID], [PrOpenBalance], [PrPurchaseRate], [PrSalesRate], [Pr_UnitId]) VALUES (5, N'005', N'CRYSTAL SALT 1KG', N'No(s).', 3, 3, CAST(5.00 AS Numeric(18, 2)), CAST(8.00 AS Numeric(18, 2)), CAST(10.00 AS Numeric(18, 2)), 1)
INSERT [dbo].[PrMaster] ([PrId], [PrCode], [PrDesc], [PrUnit], [Pr_HSNId], [Pr_TaxID], [PrOpenBalance], [PrPurchaseRate], [PrSalesRate], [Pr_UnitId]) VALUES (6, N'006', N'PEPPER - 100 GRAM', N'No(s).', 6, 2, CAST(8.00 AS Numeric(18, 2)), CAST(75.00 AS Numeric(18, 2)), CAST(85.00 AS Numeric(18, 2)), 1)
INSERT [dbo].[PrMaster] ([PrId], [PrCode], [PrDesc], [PrUnit], [Pr_HSNId], [Pr_TaxID], [PrOpenBalance], [PrPurchaseRate], [PrSalesRate], [Pr_UnitId]) VALUES (7, N'007', N'SUN FLOWER OIL - 1 LTR', N'No(s).', 4, 2, CAST(8.00 AS Numeric(18, 2)), CAST(120.00 AS Numeric(18, 2)), CAST(130.00 AS Numeric(18, 2)), 2)
INSERT [dbo].[PrMaster] ([PrId], [PrCode], [PrDesc], [PrUnit], [Pr_HSNId], [Pr_TaxID], [PrOpenBalance], [PrPurchaseRate], [PrSalesRate], [Pr_UnitId]) VALUES (8, N'0052', N'LUX 25 GRAM', N'1', 11, 1, CAST(10.00 AS Numeric(18, 2)), CAST(25.00 AS Numeric(18, 2)), CAST(28.00 AS Numeric(18, 2)), 3)
INSERT [dbo].[PrMaster] ([PrId], [PrCode], [PrDesc], [PrUnit], [Pr_HSNId], [Pr_TaxID], [PrOpenBalance], [PrPurchaseRate], [PrSalesRate], [Pr_UnitId]) VALUES (9, N'0053', N'HAMAM 25GRAM', N'1', 11, 1, CAST(8.00 AS Numeric(18, 2)), CAST(25.00 AS Numeric(18, 2)), CAST(28.00 AS Numeric(18, 2)), 3)
SET IDENTITY_INSERT [dbo].[PrMaster] OFF
End
GO
IF NOT Exists(Select * from [dbo].[MaState])
Begin
SET IDENTITY_INSERT [dbo].[MaState] ON 
INSERT [dbo].[MaState] ([StateID], [StateName], [StateCode]) VALUES (1, N'Tamil Nadu', N'33')
SET IDENTITY_INSERT [dbo].[MaState] OFF
End
GO
IF NOT Exists(Select * from [dbo].[MaParty])
Begin
SET IDENTITY_INSERT [dbo].[MaParty] ON 
INSERT [dbo].[MaParty] ([PaID], [PaName], [PaAddress1], [PaAddress2], [PaAddress3], [PaPINCode], [PaGSTN], [PaPAN], [PaMailId], [PaStateID], [PaTypeID], [PaMobileNo]) VALUES (1, N'DEMO USER', N'CHENNAI', N'*********', N'*********', N'600106', N'*********', N'BA206**', N'', 1, 1, N'')
INSERT [dbo].[MaParty] ([PaID], [PaName], [PaAddress1], [PaAddress2], [PaAddress3], [PaPINCode], [PaGSTN], [PaPAN], [PaMailId], [PaStateID], [PaTypeID], [PaMobileNo]) VALUES (2, N'DEMO USER 2', N'*', N'', N'', N'', N'', N'', N'', 1, 1, N'')
SET IDENTITY_INSERT [dbo].[MaParty] OFF
End
GO
IF NOT Exists(Select * from [dbo].[InvoiceMain])
Begin
SET IDENTITY_INSERT [dbo].[InvoiceMain] ON 
INSERT [dbo].[InvoiceMain] ([InvoiceId], [InvoiceNo], [InvoiceDate], [InvoicePaID], [InvoiceGrossAmount], [InvoiceTaxAmount], [InvoiceRoundOff], [InvoiceNetAmount], [InvoiceInsertData], [InvoiceStatus]) VALUES (1, 1, CAST(N'2022-06-09T00:00:00.000' AS DateTime), 1, CAST(1035.00 AS Numeric(18, 2)), CAST(131.40 AS Numeric(18, 2)), CAST(-0.40 AS Numeric(18, 2)), CAST(1166.00 AS Numeric(18, 2)), CAST(N'2022-06-09T00:46:04.680' AS DateTime), 1)
INSERT [dbo].[InvoiceMain] ([InvoiceId], [InvoiceNo], [InvoiceDate], [InvoicePaID], [InvoiceGrossAmount], [InvoiceTaxAmount], [InvoiceRoundOff], [InvoiceNetAmount], [InvoiceInsertData], [InvoiceStatus]) VALUES (2, 2, CAST(N'2022-06-09T00:00:00.000' AS DateTime), 2, CAST(333.00 AS Numeric(18, 2)), CAST(58.14 AS Numeric(18, 2)), CAST(-0.14 AS Numeric(18, 2)), CAST(391.00 AS Numeric(18, 2)), CAST(N'2022-06-09T00:47:25.440' AS DateTime), 1)
SET IDENTITY_INSERT [dbo].[InvoiceMain] OFF
End
GO
IF NOT Exists(Select * from [dbo].[InvoiceSub])
Begin
SET IDENTITY_INSERT [dbo].[InvoiceSub] ON 
INSERT [dbo].[InvoiceSub] ([InvoiceSubId], [InvoiceId], [InvoicePrId], [InvoicePrQty], [InvoicePrRate], [InvoiceTaxId], [InvoiceCGSTAmount], [InvoiceSGSTAmount], [InvoiceIGSTAmount], [InvoiceUGSTAmount]) VALUES (1, 1, 1, CAST(1.00 AS Numeric(18, 2)), CAST(275.00 AS Numeric(18, 2)), 1, CAST(24.75 AS Numeric(18, 2)), CAST(24.75 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)))
INSERT [dbo].[InvoiceSub] ([InvoiceSubId], [InvoiceId], [InvoicePrId], [InvoicePrQty], [InvoicePrRate], [InvoiceTaxId], [InvoiceCGSTAmount], [InvoiceSGSTAmount], [InvoiceIGSTAmount], [InvoiceUGSTAmount]) VALUES (2, 1, 2, CAST(3.00 AS Numeric(18, 2)), CAST(100.00 AS Numeric(18, 2)), 1, CAST(27.00 AS Numeric(18, 2)), CAST(27.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)))
INSERT [dbo].[InvoiceSub] ([InvoiceSubId], [InvoiceId], [InvoicePrId], [InvoicePrQty], [InvoicePrRate], [InvoiceTaxId], [InvoiceCGSTAmount], [InvoiceSGSTAmount], [InvoiceIGSTAmount], [InvoiceUGSTAmount]) VALUES (3, 1, 5, CAST(7.00 AS Numeric(18, 2)), CAST(10.00 AS Numeric(18, 2)), 3, CAST(4.20 AS Numeric(18, 2)), CAST(4.20 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)))
INSERT [dbo].[InvoiceSub] ([InvoiceSubId], [InvoiceId], [InvoicePrId], [InvoicePrQty], [InvoicePrRate], [InvoiceTaxId], [InvoiceCGSTAmount], [InvoiceSGSTAmount], [InvoiceIGSTAmount], [InvoiceUGSTAmount]) VALUES (4, 1, 7, CAST(3.00 AS Numeric(18, 2)), CAST(130.00 AS Numeric(18, 2)), 2, CAST(9.75 AS Numeric(18, 2)), CAST(9.75 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)))
INSERT [dbo].[InvoiceSub] ([InvoiceSubId], [InvoiceId], [InvoicePrId], [InvoicePrQty], [InvoicePrRate], [InvoiceTaxId], [InvoiceCGSTAmount], [InvoiceSGSTAmount], [InvoiceIGSTAmount], [InvoiceUGSTAmount]) VALUES (5, 2, 9, CAST(1.00 AS Numeric(18, 2)), CAST(28.00 AS Numeric(18, 2)), 1, CAST(2.52 AS Numeric(18, 2)), CAST(2.52 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)))
INSERT [dbo].[InvoiceSub] ([InvoiceSubId], [InvoiceId], [InvoicePrId], [InvoicePrQty], [InvoicePrRate], [InvoiceTaxId], [InvoiceCGSTAmount], [InvoiceSGSTAmount], [InvoiceIGSTAmount], [InvoiceUGSTAmount]) VALUES (6, 2, 1, CAST(1.00 AS Numeric(18, 2)), CAST(275.00 AS Numeric(18, 2)), 1, CAST(24.75 AS Numeric(18, 2)), CAST(24.75 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)))
INSERT [dbo].[InvoiceSub] ([InvoiceSubId], [InvoiceId], [InvoicePrId], [InvoicePrQty], [InvoicePrRate], [InvoiceTaxId], [InvoiceCGSTAmount], [InvoiceSGSTAmount], [InvoiceIGSTAmount], [InvoiceUGSTAmount]) VALUES (7, 2, 5, CAST(3.00 AS Numeric(18, 2)), CAST(10.00 AS Numeric(18, 2)), 3, CAST(1.80 AS Numeric(18, 2)), CAST(1.80 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)), CAST(0.00 AS Numeric(18, 2)))
SET IDENTITY_INSERT [dbo].[InvoiceSub] OFF
End
GO