Hello,
I hope someone can help me with a problem I have using SQL Server 2016.
I have 2 tables of data (Table_1 & Table_2) and I wish to create a third table which merges the data from Table_1 & Table_2 but then also appends further data which I wish to manually enter.
Table_1 contains employee information such as Job Role and contact details - see attached
Table_2 contains information regarding COVID19 Testing - such as a test date / test result - see attached.
I have created a third table (Table_3) within my database - see attached.
Within Table_3.xlsx I have tried to show where I want to take the data from Table_1 and Table_2 and add it to Table 3 and then manually enter the remaining data.
Initially i tried to write 2 separate INSERT TO statements; one which took data from Table_1 & Table_2 and another script which manually entered the remaining data. I quickly learnt this would just create two rows in the table rather than one.
Please can someone help?
Thanks,
Tom
June 5, 2020 at 12:45 pm
It'd be so much easier if you showed stuff in T-SQL since that's how we have to do all the work. However, let me take a stab at a pass on this. I'm going to have to recreate your stuff in a database in order to make sure the code is correct. I skipped some columns, but just add them:
CREATE DATABASE TestDB;
GO
USE TestDB;
GO
CREATE TABLE Table1
(
People_Soft_ID INT,
First_name VARCHAR(50),
Last_name VARCHAR(50)
);
CREATE TABLE Table2
(
People_Soft_ID INT,
Date_of_test VARCHAR(50),
Test_timestamp DATETIME
);
CREATE TABLE Table3
(
CTN_ID INT IDENTITY(1, 1),
People_Soft_ID INT,
First_name VARCHAR(50),
Last_name VARCHAR(50),
Date_of_test VARCHAR(50),
Test_timestamp DATETIME,
SomeOtherColumn INT
);
GO
INSERT INTO dbo.Table1
(
People_Soft_ID,
First_name,
Last_name
)
VALUES
( 1, -- People_Soft_ID - int
'Tim', -- First_name - varchar(50)
'DBA' -- Last_name - varchar(50)
);
INSERT INTO dbo.Table2
(
People_Soft_ID,
Date_of_test,
Test_timestamp
)
VALUES
( 1, -- People_Soft_ID - int
'String cause I wasn''t paying attention', -- Date_of_test - varchar(50)
GETDATE() -- Test_timestamp - datetime
);
Then, here's a query:
INSERT INTO dbo.Table3
(
People_Soft_ID,
First_name,
Last_name,
Date_of_test,
Test_timestamp,
SomeOtherColumn
)
SELECT t1.People_Soft_ID,
t1.First_name,
t1.Last_name,
t2.Date_of_test,
t2.Test_timestamp,
NULL
FROM dbo.Table1 AS t1
JOIN dbo.Table2 AS t2
ON t2.People_Soft_ID = t1.People_Soft_ID;
Or, just leave out the extra columns in Table3 if they're nullable. You just map your columns from the tables to the right spots in Table3.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Sorry - first time poster and as you probably guessed a bit of a newbie with SQL, i'll be sure to include the T-SQL next time.
Thank you for that, i think i understand what's going on there. So for the data that i wish to manually enter, can i just add that to the SELECT part of the query, obviously just without the reference to t1 or t2? So for example, say 'SomeOtherColumn' was actually 'TestResult' and i wanted to manually enter the value of 'Yes' could i use?
INSERT INTO dbo.Table3
(
People_Soft_ID,
First_name,
Last_name,
Date_of_test,
Test_timestamp,
Test_Result
)
SELECT t1.People_Soft_ID,
t1.First_name,
t1.Last_name,
t2.Date_of_test,
t2.Test_timestamp,
Yes',
NULL
FROM dbo.Table1 AS t1
JOIN dbo.Table2 AS t2
ON t2.People_Soft_ID = t1.People_Soft_ID;
June 5, 2020 at 2:22 pm
Welcome to the party! I hope you come back.
Yeah, you got it. If you have additional information you'd like to add as part of the insert, just plug it in as appropriate. It's all about defining the INSERT column list in a particular order. Then, make the SELECT set of columns go in the same order. And yes, you can simply add values, GETDATE(), 'some string value', 42, in the appropriate places.
I hope that's clear and helps. Please don't hesitate to ask more questions.
Total side note: Sometimes, responses here can seem gruff, especially if you're new. However, know that almost all of us just want to help. If our comms are occasionally not quite right, please be forgiving.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 8, 2020 at 7:44 am
That's great! thanks Grant!
Nothing to forgive, it's all good constructive feedback. Thanks again for your help
Tom
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply