T-SQL - Insert Data into Parent and Child Tables
Here is my solution (based on the same answer I've linked to in the comments):
First, you must add another column to your UDT, to hold a temporary ID for the employee:
CREATE TYPE dbo.tEmployeeData AS TABLE
(
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
DepartmentType NVARCHAR(10),
DepartmentBuilding NVARCHAR(50),
DepartmentEmployeeLevel NVARCHAR(10),
DepartmentTypeAMetadata NVARCHAR(100),
DepartmentTypeBMetadata NVARCHAR(100),
EmployeeId int
)
GO
Populating it with that new employeeId column:
DECLARE @tEmployeeData tEmployeeData
INSERT INTO @tEmployeeData (FirstName, LastName, DepartmentType,
DepartmentBuilding, DepartmentEmployeeLevel,
DepartmentTypeAMetadata, DepartmentTypeBMetadata, EmployeeId)
SELECT
N'Tom_FN', N'Tom_LN', N'A',
N'101', N'IV', N'Tech/IT', NULL, 5
UNION
SELECT
N'Mike_FN', N'Mike_LN', N'B',
N'OpenH', N'XII', NULL, N'Med', 6
UNION
SELECT
N'Joe_FN', N'Joe_LN', N'A',
N'101', N'IV', N'Tech/IT', NULL, 7
UNION
SELECT
N'Dave_FN', N'Dave_LN', N'B',
N'OpenC', N'XII', NULL, N'Lab', 8
Insert part goes here
Then, you use a table variable to map the inserted value from the employee table to the temp employee id in the data you sent to the procedure:
DECLARE @EmployeeidMap TABLE
(
temp_id int,
id int
)
Now, the trick is to populate the employee table with the MERGE
statement instead of an INSERT...SELECT
because you have to use values from both inserted and source data in the output clause:
MERGE INTO @MainEmployee USING @tEmployeeData AS sourceData ON 1 = 0 -- Always not matched
WHEN NOT MATCHED THEN
INSERT (FirstName, LastName)
VALUES (sourceData.FirstName, sourceData.LastName)
OUTPUT sourceData.EmployeeId, inserted.EmployeeID
INTO @EmployeeidMap (temp_id, id); -- populate the map table
From that point on it's simple, you need to join the data you sent to the @EmployeeidMap
to get the actual employeeId:
INSERT INTO @ParentEmployeeDepartment (EmployeeID, DepartmentType)
SELECT Id, DepartmentType
FROM @tEmployeeData
INNER JOIN @EmployeeidMap ON EmployeeID = temp_id
Now you can use the data in @ParentEmployeeDepartment
to map the actual values in ParentEmployeeDepartmentID
to the data you sent:
Testing the inserts so far
SELECT FirstName,
LastName,
SentData.DepartmentType As [Dept. Type],
DepartmentBuilding As Building,
DepartmentEmployeeLevel As [Emp. Level],
DepartmentTypeAMetadata As [A Meta],
DepartmentTypeBMetadata As [B Meta],
SentData.EmployeeId As TempId, EmpMap.id As [Emp. Id], DeptMap.ParentEmployeeDepartmentID As [Dept. Id]
FROM @tEmployeeData SentData
INNER JOIN @EmployeeidMap EmpMap ON SentData.EmployeeId = temp_id
INNER JOIN @ParentEmployeeDepartment DeptMap ON EmpMap.id = DeptMap.EmployeeID
results:
FirstName LastName Dept. Type Building Emp. Level A Meta B Meta TempId Emp. Id Dept. Id
--------- -------- ---------- -------- ---------- ------ ------ ------ ----------- -----------
Dave_FN Dave_LN B OpenC XII NULL Lab 8 1 1
Joe_FN Joe_LN A 101 IV Tech/IT NULL 7 2 2
Mike_FN Mike_LN B OpenH XII NULL Med 6 3 3
Tom_FN Tom_LN A 101 IV Tech/IT NULL 5 4 4
I'm sure that from this point you can easily figure out the last 2 inserts yourself.
Bulk insert operation into Parent and Child table in SQL
For bulk insert
from an Excel file
to a database
if you want to have a performant application follow this approach.
First of all, create a
temp table
corresponding to your Excel file columns and one more column with the name ofInsertionKey
.Insert all your input data from excel with the same InsertionKey (generate a new GUID) and all your Excel rows will have the same key. (Use Entity Framework
BulkInsertAsync
orSqlBulkCopy
for bulk insert. You also can check my GitHub repo for benchmarking BulkInsert in .Net)After data is inserted, send the
GUID
as a parameter to a stored procedure
Now, you can easily process all your inserted data by fetching them from the temp table with the key.
Example:
Your excel file columns:
Item1 Red 25
Item2 Black 32
Item3 Orange 44
Generate a new Guid before bulk insert
var insertionKey = Guid.NewGuid();
Now bulk insert into DB:
Item1 Red 25 insertionKey
Item2 Black 32 insertionKey
Item3 Orange 44 insertionKey
Now call the stored procedure with the Insertionkey
YourStoredProcedure(Insertionkey)
Inside the stored procedure:
select *
into #temp
from temptable
where insertionKey = insertionKeyParameter
Here you can read inserted rows and after that process them as you want.
Postgresql Simultaneous Insert into Parent/Child Table
I would solve that by creating a view for students:
CREATE VIEW v_student AS
SELECT person.id, person.name, student.grade
FROM person
JOIN student
ON person.id = student.person_id
WHERE person.kind = 'student';
and an INSTEAD OF INSERT
trigger:
CREATE FUNCTION ins_student() RETURNS trigger
LANGUAGE plpgsql AS
$$DECLARE
v_id integer;
BEGIN
INSERT INTO person (kind, name)
VALUES ('student', NEW.name)
RETURNING id INTO v_id;
INSERT INTO student (person_id, grade)
VALUES (v_id, NEW.grade);
RETURN NEW;
END;$$;
CREATE TRIGGER ins_student INSTEAD OF INSERT ON v_student
FOR EACH ROW EXECUTE FUNCTION ins_student();
That can be used like this:
INSERT INTO v_student (name, grade)
SELECT name, grade FROM tmp_table
WHERE kind = 'student';
TABLE person;
id │ kind │ name
════╪═════════╪═══════
1 │ student │ Chris
2 │ student │ Lara
3 │ student │ Izzy
(3 rows)
TABLE student;
person_id │ grade
═══════════╪═══════
1 │ 2
2 │ 1
3 │ 3
(3 rows)
Insert into parent-child tables in SQL Server
You can use the SCOPE_IDENTITY()
function to retrieve the last inserted auto-increment ID:
DECLARE @new_parent_id INT
INSERT INTO MyParent(...) VALUES(...) -- your new parent data
SELECT @new_parent_id = SCOPE_IDENTITY()
INSERT INTO MyChild(parent_id, ...) VALUES(@new_parent_id, ...)
Is this what you mean?
EDIT:
You can indeed pass in all your data at once with ADO.NET and SQL Server 2008 using Table Valued Parameters:
http://msdn.microsoft.com/en-us/library/bb675163.aspx
You could pass in your parent and child data as two tables to your stored procedure, then use a cursor to loop through your parents, inserting one, getting the generated ID, then inserting all its children before proceeding to the next parent.
This would probably be the most efficient solution, potentially requiring only a single call to the database, and if you have a large amount of data to insert, or if you think you can reuse this SP in other places in you solution, then this might be the way to go.
However, I think that inserting the records one-by-one from the client application is a cleaner and certainly a simpler way of achieving what you want.
Insert into parent and child table at same time in MySQL
insert into `size` (title, price) values ("bla", 12.23);
insert into `category` (title, sizeid) values ("blo", LAST_INSERT_ID());
Copying parent and child tables in SQL
What I ended up doing was creating a temp table with both new and old IDs that I could reference like so:
CREATE TABLE #TempParent(OldParentID, NewParentID, ParentData)
INSERT INTO #TempParent
SELECT(ParentID, newid(), ParentData)
FROM OtherDB.dbo.Parent
INSERT INTO Parent(ParentID, ParentData)
SELECT(NewParentID, ParentData)
FOM #TempParent
INSERT INTO Child(ParentID, ChildData)
SELECT p.NewParentID, c.ChildData
FROM OtherDB.dbo.Child c
LEFT JOIN #TempParent p ON p.OldParentID = c.ParentID
DROP TABLE #TempParent
And that works. I'm sure there's a much more elegant way to do this, though.
Insert on a child table and update FK on parent
I figured out in the meantime how to do it. Applied on the database, the query looks like this:
DECLARE @Temp TABLE (ImageId INT, Id INT)
MERGE INTO Translation USING
(
SELECT Image.Name AS Name, Image.Id AS ImageId
FROM Candidate
INNER JOIN Candidacy ON Candidate.Id = Candidacy.CandidateId
INNER JOIN Election ON Candidacy.ElectionId = Election.Id
INNER JOIN SmartVoteCandidate ON Candidate.Id = SmartVoteCandidate.CandidateId
INNER JOIN Image ON SmartVoteCandidate.SpiderImageId = Image.Id
WHERE Election.Id = 1575) AS temp ON 1 = 0
WHEN NOT MATCHED THEN
INSERT (De)
VALUES (temp.Name)
OUTPUT temp.ImageId, INSERTED.Id
INTO @Temp (ImageId, Id);
UPDATE Image
SET Image.TranslationId = t.Id, Name = NULL
FROM @Temp t
WHERE Image.Id = t.ImageId
The solution is heavily inspired by
- Is it possible to for SQL Output clause to return a column not being inserted?
- Using a join in a merge statement
insert new sequential records into child table for each record of parent table
Something like this should get what you want.
Note I added it with a LEFT JOIN so you could use ISNULL in case the parent record isn't in t2 yet.
INSERT INTO t2 (parent_id, record_number)
SELECT
A.id,
ISNULL(MAX(B.record_number), 0) + 1
FROM t1 A
LEFT JOIN t2 B
ON A.id = B.parent_id
GROUP BY
A.id
As an aside, generating a record number could be done effectively using the ROW_NUMBER() function. Here is an example. Perhaps you have specific reasons why it needs to be persisted to the table, but if not this could be useful as well to calculate it on the fly.
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY id)
FROM t2
ORDER BY parent_id, record_number
Note it assumes you have an "id" column in t2, or something else to order the records by
Related Topics
What Is the Simplest SQL Query to Find the Second Largest Value
Select Row With Most Recent Date Per User
Creating Table Names That Are Reserved Words/Keywords in Ms SQL Server
Delete Column from Sqlite Table
How to Get Column Names from a Table in Oracle
Turn Off Constraints Temporarily (Ms Sql)
What's the Difference Between Varchar and Char
SQL Query to Split Column Data into Rows
Single Fixed Table with Multiple Columns VS Flexible Abstract Tables
Top N Records Per Group SQL in Access
MySQL Insert into Table Values.. VS Insert into Table Set
How to Find Gaps in Sequential Numbering in MySQL
String_Split in SQL Server 2012
Declare Variable in Sqlite and Use It
MySQL, Better to Insert Null or Empty String
MySQL Search and Replace Some Text in a Field
Sql: Repeat a Result Row Multiple Times, and Number the Rows