July 2, 2018 at 12:22 am
I have a procedure which takes two parameters and I want to get the primary key values of those two parameters from two different tables and insert into a new table.
Tbl1 Tbl2
ID1,Name1 ID2,Name2
Now Name1 and name 2 are the parameters for the procedure and I have to get ID1 and ID2 from Tbl1 and Tbl2 and insert into a new table along with current date. There is no relation between Tbl1 and Tbl2 so that I can't go for joins.
Thanks
July 2, 2018 at 1:50 am
Why not just..?
INSERT INTO YourOtherTable (ForeignKey, CurrentDate)
SELECT ID, GETDATE()
FROM Table1
WHERE [Name] = @Name1;
INSERT INTO YourOtherTable (ForeignKey, CurrentDate)
SELECT ID, GETDATE()
FROM Table2
WHERE [Name] = @Name2;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 2, 2018 at 1:51 am
Presumably Name1 and Name2 aren't unique in their respective tables? What happens if you pass in a value of Name1 or Name2 that returns more than one value of ID? Please provide full table DDL (including constraints) in the form of CREATE TABLE statements and sample data in the form of INSERT statements. Please also show us what you've already tried - after all, we're not here to do your job or homework for you.
John
July 2, 2018 at 2:04 am
Thom A - Monday, July 2, 2018 1:50 AMWhy not just..?
INSERT INTO YourOtherTable (ForeignKey, CurrentDate)
SELECT ID, GETDATE()
FROM Table1
WHERE [Name] = @Name1;INSERT INTO YourOtherTable (ForeignKey, CurrentDate)
SELECT ID, GETDATE()
FROM Table2
WHERE [Name] = @Name2;
So do we have to insert them individually. Will there be no other option where we can insert both the values from a single query?
July 2, 2018 at 2:07 am
KGNH - Monday, July 2, 2018 2:04 AMThom A - Monday, July 2, 2018 1:50 AMWhy not just..?
INSERT INTO YourOtherTable (ForeignKey, CurrentDate)
SELECT ID, GETDATE()
FROM Table1
WHERE [Name] = @Name1;INSERT INTO YourOtherTable (ForeignKey, CurrentDate)
SELECT ID, GETDATE()
FROM Table2
WHERE [Name] = @Name2;So do we have to insert them individually. Will there be no other option where we can insert both the values from a single query?
You could combine the 2 SELECT statements with a UNION ALL instead.
On its own, however, I can't really see a use for this other table. It's simply a table of IDs and datetimes; what purpose does it serve?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 2, 2018 at 2:17 am
Thom A - Monday, July 2, 2018 2:07 AMKGNH - Monday, July 2, 2018 2:04 AMThom A - Monday, July 2, 2018 1:50 AMWhy not just..?
INSERT INTO YourOtherTable (ForeignKey, CurrentDate)
SELECT ID, GETDATE()
FROM Table1
WHERE [Name] = @Name1;INSERT INTO YourOtherTable (ForeignKey, CurrentDate)
SELECT ID, GETDATE()
FROM Table2
WHERE [Name] = @Name2;So do we have to insert them individually. Will there be no other option where we can insert both the values from a single query?
You could combine the 2 SELECT statements with a UNION ALL instead.
On its own, however, I can't really see a use for this other table. It's simply a table of IDs and datetimes; what purpose does it serve?
My actual requirement is like I have a User table with UserID and UserName, Role table with RoleID and RoleName. Now I am assigning a role to the user from front end. So I will be passing UserName and Rolename from frontend. From that I have to select UserID and RoleID from the corresponding tables and store the IDs in a Mapping table.
July 2, 2018 at 1:51 pm
INSERT INTO dbo.Tbl3 ( ID1, ID2 )
SELECT (SELECT ID1 FROM dbo.Tbl1 WHERE Name1 = @Name1),
(SELECT ID2 FROM dbo.Tbl2 WHERE Name2 = @Name2)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 2, 2018 at 10:07 pm
ScottPletcher - Monday, July 2, 2018 1:51 PM
INSERT INTO dbo.Tbl3 ( ID1, ID2 )
SELECT (SELECT ID1 FROM dbo.Tbl1 WHERE Name1 = @Name1),
(SELECT ID2 FROM dbo.Tbl2 WHERE Name2 = @Name2)
Thank you Scott. This solved my issue.
July 2, 2018 at 10:08 pm
Thank you every one for your solutions.
July 3, 2018 at 2:01 am
ScottPletcher - Monday, July 2, 2018 1:51 PM
INSERT INTO dbo.Tbl3 ( ID1, ID2 )
SELECT (SELECT ID1 FROM dbo.Tbl1 WHERE Name1 = @Name1),
(SELECT ID2 FROM dbo.Tbl2 WHERE Name2 = @Name2)
This is aimed at the OP, however, this does assume that Name is unique. Something that, in the real world, in far from true. This code will fail when you have 2 people with the same name.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 3, 2018 at 7:33 am
Thom A - Tuesday, July 3, 2018 2:01 AMScottPletcher - Monday, July 2, 2018 1:51 PM
INSERT INTO dbo.Tbl3 ( ID1, ID2 )
SELECT (SELECT ID1 FROM dbo.Tbl1 WHERE Name1 = @Name1),
(SELECT ID2 FROM dbo.Tbl2 WHERE Name2 = @Name2)This is aimed at the OP, however, this does assume that Name is unique. Something that, in the real world, in far from true. This code will fail when you have 2 people with the same name.
Presumably the poster knows their data better than we do, but to be safer, you could do this:
INSERT INTO dbo.Tbl3 ( ID1, ID2 )
SELECT (SELECT TOP (1) ID1 FROM dbo.Tbl1 WHERE Name1 = @Name1 ORDER BY ID1 /*DESC*/),
(SELECT TOP (1) ID2 FROM dbo.Tbl2 WHERE Name2 = @Name2 ORDER BY ID2 /*DESC*/)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 4, 2018 at 3:42 am
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply