Insert different attributes from keyless table with non-unique records to 2 related tables-CodePudding

I have a table "tempImport" with no unique ID column containing several hundreds of records. Lets assume that not all records are unique (i.e. 2 records may have the exact same values in each column).

I would like to first insert some of the attributes in the tempImport table into table "myTable", which has a PK column with auto-incrementing unique IDs. The repeating rows will be kept-- I do not want to combine them in myTable (from the below example, there will be 3 rows for Samuel Clark and 2 for Faith Hodge in myTable, each with their own unique PK).

Something along the lines of:

INSERT INTO myTable([name]) SELECT [name] FROM tempImport

I would also like to insert the rest of the columns from the tempImport table into another table, "myTableDetails", which contains ID from myTable as FK. As with the previous insert, repeating data will be kept.

INSERT INTO myTableDetails([myTableID], [phone],[email],[date],[company]) ...

Assume all non-key columns are VARCHAR and key columns are INT datatypes.

Is there a way to do this?

Ideally, I think being able to merge INSERTED from myTable with the other columns in tempImport would solve the issue, although I am not sure how this can be done.

Otherwise, the only option I can think of is to add a PK column to tempImport and add FK in both of the other tables, but that would not be what I'm asking for here.

Here is some sample data:

CREATE TABLE tempImport (
    [name] VARCHAR(255) NULL,
    [phone] VARCHAR(100) NULL,
    [email] VARCHAR(255) NULL,
    [date] VARCHAR(255) NULL,
    [company] VARCHAR(255) NULL,
);

CREATE TABLE myTable (
    [ID] INT NOT NULL PRIMARY KEY,
    [name] VARCHAR
);

CREATE TABLE myTableDetails (
    [myTableID] INT,
    FOREIGN KEY (myTableID) REFERENCES myTable(ID),
    [phone] VARCHAR(100) NULL,
    [email] VARCHAR(255) NULL,
    [date] VARCHAR(255) NULL,
    [company] VARCHAR(255) NULL,
)

INSERT INTO 
    tempImport([name], [phone], [email], [date], [company]) 
VALUES 
  ('Samuel Clark','1-887-516-2723','[email protected]','Apr 9, 2023','Convallis Dolor Ltd'),
  ('Samuel Clark','1-887-516-2723','[email protected]','Apr 9, 2023','Convallis Dolor Ltd'),
  ('Samuel Clark','1-887-516-2723','[email protected]','Apr 9, 2023','Convallis Dolor Ltd'),
  ('Faith Hodge','(903) 877-5281','[email protected]','Mar 18, 2023','Eu Foundation'),
  ('Faith Hodge','(903) 877-5281','[email protected]','Mar 18, 2023','Eu Foundation'),
  ('Fuller Hyde','1-886-712-8325','[email protected]','Jan 21, 2022','In Tempus Associates'),
  ('Yvonne Edwards','(549) 872-0163','[email protected]','Apr 24, 2022','Enim Incorporated'),
  ('Darrel Hodges','(334) 868-7545','[email protected]','Aug 21, 2022','Sed Nunc Foundation');

My desired result after the 2 inserts:

myTable (SELECT * FROM myTable):

ID  name
1, 'Samuel Clark'
2, 'Samuel Clark'
3, 'Samuel Clark'
4, 'Faith Hodge'
5, 'Faith Hodge'
6, 'Fuller Hyde'
7, 'Yvonne Edwards'
8, 'Darrel Hodges'

myTableDetails (SELECT * FROM myTableDetails):

myTableID  phone            email                       date          company 
1,         '1-887-516-2723','[email protected]','Apr 9, 2023','Convallis Dolor Ltd'
2,         '1-887-516-2723','[email protected]','Apr 9, 2023','Convallis Dolor Ltd'
3,         '1-887-516-2723','[email protected]','Apr 9, 2023','Convallis Dolor Ltd'
4,         '(903) 877-5281','[email protected]',             'Mar 18, 2023','Eu Foundation'
5,         '(903) 877-5281','[email protected]',             'Mar 18, 2023','Eu Foundation'
6,         '1-886-712-8325','[email protected]',    'Jan 21, 2022','In Tempus Associates'
7,         '(549) 872-0163','[email protected]','Apr 24, 2022','Enim Incorporated'
8,         '(334) 868-7545','[email protected]','Aug 21, 2022','Sed Nunc Foundation'

CodePudding user response：

Considering not using IDENTITY_INSERT I suggest this solution:

--Retrieve the maximum id ever our identity column incremented to
DECLARE @max_id INT = (SELECT Max(ID) FROM myTable)
IF @max_id IS NULL SET @max_id = 0

--Insertion considering that our ID in identity
INSERT INTO myTable (name)
SELECT name 
FROM tempImport
ORDER BY name, phone, email, date, company

--Insertion into the myTableDetails by joining tempImport and recently inserted rows in myTable
INSERT INTO myTableDetails
SELECT b.ID, a.phone, a.email, a.date, a.company 
FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY name, phone, email, date, company) row,* 
    FROM tempImport
) a
INNER JOIN (
    SELECT ROW_NUMBER() OVER(ORDER BY ID) row,* 
    FROM myTable 
    WHERE ID > @max_id
) b
ON a.row = b.row