October 14, 2015 at 2:34 pm
Hi,
I'm in the process of putting data from a large table into several smaller tables.
In the process, I need for the auto identity number (PK) in the first smaller table to be output and used as a Foreign Key in the other smaller tables.
Would I need an intermediary temporary table to make comparisons?
Thanks for any help. 🙂
Here are my tables:
CREATE TABLE [dbo].[aaa_test_sp]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](50) NULL,
[first_name] [nvarchar](50) NULL,
[last_name] [nvarchar](50) NULL,
[position] [nvarchar](50) NULL,
[phone] [nvarchar](50) NULL,
[education] [nvarchar](50) NULL,
[ListID] [int] NULL,
CONSTRAINT [PK_aaa_test_sp]
PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[aaa_test_users]
(
[UserID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](50) NULL,
[first_name] [nvarchar](50) NULL,
[last_name] [nvarchar](50) NULL,
CONSTRAINT [PK_aaa_test_users]
PRIMARY KEY CLUSTERED ([UserID] ASC)
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[aaa_test_users_positions]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[position] [nvarchar](50) NULL,
[phone] [nvarchar](50) NULL,
CONSTRAINT [PK_aaa_test_users_positions]
PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[aaa_test_users_education]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[education] [nvarchar](50) NULL,
CONSTRAINT [PK_aaa_test_users_education]
PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]
GO
Here is the code I've been working on:
declare @NewUserID nvarchar(50)
--DECLARE @InsertOutput1 table (UserID nvarchar(50));
--insert, first, rows from aaa_test_sp to aaa_test_users, and get the autoNumber ID,
--"NewUserID"
INSERT INTO aaa_test_users (UserName, first_name, last_name)
OUTPUT inserted.UserID INTO @InsertOutput1
SELECT
UserName, first_name, last_name
FROM aaa_test_sp
WHERE (ListId = '1')
--now that the "NewUserID" has been generated,
--insert it, along with other columns,
--into the 'users_positions' table.
INSERT INTO aaa_test_users_positions (UserID, position, phone)
(SELECT @NewUserID, position, phone
FROM aaa_test_sp
WHERE ListId = '1';)
--now that the "NewUserID" has been generated,
--insert it, along with other columns,
--into the 'users_education' table
INSERT INTO aaa_test_users_education (UserID, education)
(SELECT@NewUserID, education
FROM aaa_test_sp
WHERE ListId = '1';)
October 15, 2015 at 3:25 am
It looks like you're overthinking it. Try something like this:
INSERT INTO aaa_test_users (UserName, first_name, last_name)
FROM aaa_test_sp
INSERT INTO aaa_test_users_positions (UserID, position, phone)
SELECT aaa_test_users.UserID, aaa_test_sp.position, aaa_test_sp.phone
FROM aaa_test_sp
join aaa_test_users on aaa_test_sp.username = aaa_test_users.username
Repeat the INSERT INTO query for the other tables, linking back to the new user table on the username field (assuming that the username field is unique and will only match one row). You don't need the variable or the OUTPUT clause.
October 16, 2015 at 2:53 pm
daniel.freedman 80164 (10/15/2015)
It looks like you're overthinking it. Try something like this:
INSERT INTO aaa_test_users (UserName, first_name, last_name)FROM aaa_test_sp
INSERT INTO aaa_test_users_positions (UserID, position, phone)
SELECT aaa_test_users.UserID, aaa_test_sp.position, aaa_test_sp.phone
FROM aaa_test_sp
join aaa_test_users on aaa_test_sp.username = aaa_test_users.username
Repeat the INSERT INTO query for the other tables, linking back to the new user table on the username field (assuming that the username field is unique and will only match one row). You don't need the variable or the OUTPUT clause.
Many, many thanks - very much for helping me to simplify this! 🙂
In the end, I used my compound PK (as the username wasn't unique) and use that as the unique identifier, with which to do the joins.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply