Hi guys,
Been tasked with bulk adding a load of users for 466 different companies into the database.
there are 3 tables that need to be added into...
User - contains certain details about the User account
DealerEmployee - simple table that ties the User back to a company
Password - contains password details for the User's account.
The details that will be added into the User table are indentical as it is just adding the same username for the different companies.
To insert one user into the User table the syntax would be... (just to give example of column names and values etc)
the UI and PK for this table is ID_User so after inserting into this table i was gonna grab this Id number...
My thought was that the way i would do it (not sure its the best.. but as it requires bulk inserting... thought it would be easist) would just be to set up a csv file (with 466 identical rows) for the user table... insert those rows.
then collect all the Ids from there and make a second CSV file for the DealerEmployee table which only needs Id_User and Id_Dealer.
This is where i fell over....
The Id_Dealer is the UI from the Dealer table that contains all the info on the companies etc.
any ideas
a) on whether the .csv file is the best way to go
b) getting around the foreign key error?
cheers guys... apologies if the above is a bit of waffle... friday afternoon symptoms - brain beginning to shut down!
Cheers,
Dan
To start with I wanted to just try adding one
Been tasked with bulk adding a load of users for 466 different companies into the database.
there are 3 tables that need to be added into...
User - contains certain details about the User account
DealerEmployee - simple table that ties the User back to a company
Password - contains password details for the User's account.
The details that will be added into the User table are indentical as it is just adding the same username for the different companies.
To insert one user into the User table the syntax would be... (just to give example of column names and values etc)
Code:
insert into [User]
(Code_Language, Id_Role, Login, Name, Disabled, FailureCount, ExcludeFromReports, DeactivationExempt, SsoUser)
values
('DE', 3,'MPNew', 'MPNew', 0,0,0,1,0)the UI and PK for this table is ID_User so after inserting into this table i was gonna grab this Id number...
My thought was that the way i would do it (not sure its the best.. but as it requires bulk inserting... thought it would be easist) would just be to set up a csv file (with 466 identical rows) for the user table... insert those rows.
then collect all the Ids from there and make a second CSV file for the DealerEmployee table which only needs Id_User and Id_Dealer.
This is where i fell over....
Code:
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Employee_Dealer". The conflict occurred in database "MP2_GME_CUS", table "dbo.Dealer", column 'Id_Dealer'.
The statement has been terminated.The Id_Dealer is the UI from the Dealer table that contains all the info on the companies etc.
any ideas
a) on whether the .csv file is the best way to go
b) getting around the foreign key error?
cheers guys... apologies if the above is a bit of waffle... friday afternoon symptoms - brain beginning to shut down!
Cheers,
Dan
To start with I wanted to just try adding one