How to insert multiple records from fields in select record
-
I am trying to take results from multiple fields from single rows that are not null and put them in multiple rows in another table where each valid field has a record (row) of it's own.
DECLARE @TestTable TABLE
(
Name1 VARCHAR(20),
Name2 VARCHAR(20),
Name3 VARCHAR(20)
)DECLARE @TestTable2 TABLE
(
ID int IDENTITY(1,1),
SingleName varchar(20)
)INSERT @TestTable VALUES('Tom', 'Larry', NULL)
INSERT @TestTable VALUES('Ron', NULL, NULL)
INSERT @TestTable VALUES('Sheryl', 'Mary', 'Mark')SELECT * FROM @TestTable
INSERT @TestTable2
(
SingleName
)
SELECT ??? FROM @TestTableHere is what is in @TestTable
What I want to end up with in @TestTable2:
Thanks
-
step 1: find all the names that are not null
step 2: union them together.
insert the result from step 2 into your final table.
return results from final table.Nothing to it, right?
Yeah, I didn't post an answer exactly, but that would deny you the chance to try it yourself. -
That's fine.
But I don't need a list of names (my mistake). I need to use another value in the record to go along with the names in each record. So changing the code to the following:
DECLARE @TestTable TABLE
(
ID INT IDENTITY(1,1),
NameRecord INT,
Name1 VARCHAR(20),
Name2 VARCHAR(20),
Name3 VARCHAR(20)
)DECLARE @TestTable2 TABLE
(
ID int IDENTITY(1,1),
NameRecord INT,
SingleName varchar(20)
)INSERT @TestTable VALUES(5, 'Tom', 'Larry', NULL)
INSERT @TestTable VALUES(7, 'Ron', NULL, NULL)
INSERT @TestTable VALUES(9, 'Sheryl', 'Mary', 'Mark')SELECT * FROM @TestTable
INSERT @TestTable2
(
NameRecord,
SingleName
)
SELECT ???The @TestTable looks like:
The results should look something like:
I could do this with a cursor but would prefer not to.
Thanks,
Tom
-
INSERT @TestTable2
(
NameRecord,
SingleName
)
SELECT ca1.*
FROM @TestTable
CROSS APPLY (
VALUES(ID, NameRecord, Name1), (ID, NameRecord, Name2), (ID, NameRecord, Name3)
) AS ca1(ID, NameRecord, SingleName)
WHERE SingleName IS NOT NULLSQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
- ScottPletcher - Wednesday, January 30, 2019 8:37 AM
You don't need to include fields that are consistently repeated, that is, you don't need to include ID and NameRecord, because you can just use the values from the main table.
INSERT @TestTable2
(
NameRecord,
SingleName
)
SELECT tt.NameRecord, ca1.SingleName
FROM @TestTable AS tt
CROSS APPLY( VALUES(Name1), (Name2), (Name3) ) ca1(SingleName)Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
That helped a lot.
Here is the solution that is a little closer to what I was trying to achieve
I have 2 tables (old values and new values). I only want to put in the record where the values have changed from the old to the new. The issue was that there were multiple types of values (owner, depenants and policies) that had to be compared and I wanted a record for each difference. So there could be from 0 to 3 records written out for each set of records.
Here is the code:
DECLARE @OldTable TABLE
(
ID INT IDENTITY(1,1),
NameRecord INT,
OwnerName VARCHAR(20),
Dependants INT,
Policies INT
)DECLARE @NewTable TABLE
(
ID INT IDENTITY(1,1),
NameRecord INT,
OwnerName VARCHAR(20),
Dependants INT,
Policies INT
)
DECLARE @DiffTable TABLE
(
ID int IDENTITY(1,1),
NameRecord INT,
Differences varchar(200)
)INSERT @OldTable VALUES(5, 'Tom', 3, NULL)
INSERT @OldTable VALUES(7, 'Ron', NULL, 4)
INSERT @OldTable VALUES(9, 'Sheryl', 2, 3)INSERT @NewTable VALUES(5, 'Tom', 3, 1)
INSERT @NewTable VALUES(7, 'Ron', NULL, 4)
INSERT @NewTable VALUES(9, 'Sheryl Ann', 3, 4)
SELECT *
FROM @OldTable o
JOIN @NewTable n
ON n.NameRecord = o.NameRecordSELECT o.NameRecord,
ca1.value1,
ca1.value2,
ca1.valueType,
CASE WHEN ca1.valueType = 1 THEN 'Owner has changed from ' + ca1.value1 + ' to ' + ca1.value2
WHEN ca1.valueType = 2 THEN 'Dependants have changed from ' + ca1.value1 + ' to ' + ca1.value2
WHEN ca1.valueType = 3 THEN 'Policies have changed from ' + ca1.value1 + ' to ' + ca1.value2
END ValuesChanged
FROM @OldTable o
JOIN @NewTable n
ON n.NameRecord = o.NameRecord
CROSS APPLY
(
VALUES
(ISNULL(o.OwnerName,''), ISNULL(n.OwnerName, ''), 1),
(ISNULL(CONVERT(VARCHAR(10), o.Dependants),''), ISNULL(CONVERT(VARCHAR(10), n.Dependants),''), 2),
(ISNULL(CONVERT(VARCHAR(10), o.Policies),''), ISNULL(CONVERT(VARCHAR(10), n.Policies),''), 3)
) ca1 (value1, value2, valueType)
WHERE ca1.value1 <> ca1.value2INSERT @DiffTable
(
NameRecord,
Differences
)
SELECT o.NameRecord,
CASE WHEN ca1.valueType = 1 THEN 'Owner has changed from ' + ca1.value1 + ' to ' + ca1.value2
WHEN ca1.valueType = 2 THEN 'Dependants have changed from ' + ca1.value1 + ' to ' + ca1.value2
WHEN ca1.valueType = 3 THEN 'Policies have changed from ' + ca1.value1 + ' to ' + ca1.value2
END ValuesChanged
FROM @OldTable o
JOIN @NewTable n
ON n.NameRecord = o.NameRecord
CROSS APPLY
(
VALUES
(ISNULL(o.OwnerName,''), ISNULL(n.OwnerName, ''), 1),
(ISNULL(CONVERT(VARCHAR(10), o.Dependants),''), ISNULL(CONVERT(VARCHAR(10), n.Dependants),''), 2),
(ISNULL(CONVERT(VARCHAR(10), o.Policies),''), ISNULL(CONVERT(VARCHAR(10), n.Policies),''), 3)
) ca1 (value1, value2, valueType)
WHERE ca1.value1 <> ca1.value2SELECT * FROM @DiffTable
Here are the three results. The last one is the resulting table with only the differences.
Thanks for the help.
-
You don't need the CASE expression.
SELECT o.NameRecord,
ca1.value1,
ca1.value2,
ca1.valueType + ' has changed from ' + ca1.value1 + ' to ' + ca1.value2 AS ValuesChanged
FROM @OldTable o
JOIN @NewTable n
ON n.NameRecord = o.NameRecord
CROSS APPLY
(
VALUES
(COALESCE(o.OwnerName, ''), COALESCE(n.OwnerName, ''), 'Owner'),
(COALESCE(CAST(o.Dependants AS VARCHAR(10)), ''), COALESCE(CAST(n.Dependants AS VARCHAR(10)), ''), 'Dependants'),
(COALESCE(CAST(o.Policies AS VARCHAR(10)), ''), COALESCE(CAST(n.Policies AS VARCHAR(10)), ''), 'Policies')
) ca1 (value1, value2, valueType)
WHERE ca1.value1 <> ca1.value2The only difference in your warning message is which value has changed, but it's based on a code that you've hard-coded into your table value constructor. Instead of hard-coding a code that's later translated into a value, hard-code the value in the first place.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
I do like that simplification.
Thanks.
-
Another question on the above cross apply.
Using the above Cross Apply where we have 3 rows coming back, is there a way to tell it to only send only one row instead of three based on some criteria.
For example, if we have blank in the o.OwnerName, we know we have a new record, so we only need the first row and not the dependants or policies rows. After they come back they are three separate records. We don't want to stop the whole record, as I do want to report that this is a new record.
CROSS APPLY
(
VALUES
(COALESCE(o.OwnerName, ''), COALESCE(n.OwnerName, ''), 'Owner'),
(COALESCE(CAST(o.Dependants AS VARCHAR(10)), ''), COALESCE(CAST(n.Dependants AS VARCHAR(10)), ''), 'Dependants'),
(COALESCE(CAST(o.Policies AS VARCHAR(10)), ''), COALESCE(CAST(n.Policies AS VARCHAR(10)), ''), 'Policies')
) ca1 (value1, value2, valueType)Thanks.
-
Using the above Cross Apply where we have 3 rows coming back, is there a way to tell it to only send only one row instead of three based on some criteria.
Use ROW_NUMBER() to number the names that are returned and then filter for = 1?CREATE TABLE #test (
FirstName VARCHAR(20) NOT NULL,
Letter CHAR NOT NULL
);
GO
INSERT INTO #test (FirstName, Letter) VALUES ('Al','B'),('Al','D'),('Al','A'),
('Bart','X'),('Bart','B'),('Bart','D'),('Homer','D'),('Homer','A');SELECT FirstName
, Letter
FROM
(SELECT FirstName
, Letter
, ROW_NUMBER() OVER (PARTITION BY FirstName ORDER BY Letter) AS rn
FROM #test) x
WHERE rn = 1;Oh wait, you're using an old version of SQL Server... Windowing functions were introduced in either 2008R2 or 2012. So you'd have to use a Common Table Expression, I think..
-
That wasn't really what I meant but I figured out how to do it.
I can't stop all three from being sent from the cross apply, but I can use a case statement in the Cross Applyl and then filter on the rows in the where clause.In this case, if the o.OwnerName is blank, I know this is a new record so I set the other valueTypes to a blank so they will get filtered out.
CROSS APPLY
(
VALUES
(COALESCE(o.OwnerName, ''), COALESCE(n.OwnerName, ''), 'Owner'),
(COALESCE(CAST(o.Dependants AS VARCHAR(10)), ''), COALESCE(CAST(n.Dependants AS VARCHAR(10)), ''), CASE WHEN o.OwnerName IS NULL THEN '' ELSE 'Dependants' END),
(COALESCE(CAST(o.Policies AS VARCHAR(10)), ''), COALESCE(CAST(n.Policies AS VARCHAR(10)), ''), CASE WHEN o.OwnerName IS NULL THEN '' ELSE 'Policies' END)
) ca1 (value1, value2, valueType)
WHERE valueType <> '' AND
ca1.value1 <> ca1.value2This will only show rows that are different and the valueType is not blank.
Thanks
- tshad - Wednesday, January 30, 2019 6:51 PM
Just add conditions to your WHERE clause.
AND (o.OwnerName > '' OR ca1.valueType = 'Owner')If the conditions are really complex, you can change from a table value constructor to a SELECT/UNION clause.
CROSS APPLY
(
SELECT COALESCE(o.OwnerName, ''), COALESCE(n.OwnerName, ''), 'Owner'
UNION ALL
SELECT COALESCE(CAST(o.Dependants AS VARCHAR(10)), ''), COALESCE(CAST(n.Dependants AS VARCHAR(10)), ''), 'Dependants'
WHERE o.OwnerName > ''
UNION ALL
SELECT COALESCE(CAST(o.Policies AS VARCHAR(10)), ''), COALESCE(CAST(n.Policies AS VARCHAR(10)), ''), 'Policies'
WHERE o.OwnerName > ''
) ca1 (value1, value2, valueType)Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
The union does give flexibility to how the rows are handled.
I did it like your first suggestion and just changed valueType to allow me to use the where clause to remove two of the rows.
Thanks.
- tshad - Tuesday, January 29, 2019 7:59 PM
That's fine.
But I don't need a list of names (my mistake). I need to use another value in the record to go along with the names in each record. So changing the code to the following:
DECLARE @TestTable TABLE
(
ID INT IDENTITY(1,1),
NameRecord INT,
Name1 VARCHAR(20),
Name2 VARCHAR(20),
Name3 VARCHAR(20)
)DECLARE @TestTable2 TABLE
(
ID int IDENTITY(1,1),
NameRecord INT,
SingleName varchar(20)
)INSERT @TestTable VALUES(5, 'Tom', 'Larry', NULL)
INSERT @TestTable VALUES(7, 'Ron', NULL, NULL)
INSERT @TestTable VALUES(9, 'Sheryl', 'Mary', 'Mark')SELECT * FROM @TestTable
INSERT @TestTable2
(
NameRecord,
SingleName
)
SELECT ???The @TestTable looks like:
The results should look something like:
I could do this with a cursor but would prefer not to.
Thanks,
Tom
- tshad - Tuesday, January 29, 2019 7:59 PM
That's fine.
But I don't need a list of names (my mistake). I need to use another value in the record to go along with the names in each record. So changing the code to the following:
DECLARE @TestTable TABLE
(
ID INT IDENTITY(1,1),
NameRecord INT,
Name1 VARCHAR(20),
Name2 VARCHAR(20),
Name3 VARCHAR(20)
)DECLARE @TestTable2 TABLE
(
ID int IDENTITY(1,1),
NameRecord INT,
SingleName varchar(20)
)INSERT @TestTable VALUES(5, 'Tom', 'Larry', NULL)
INSERT @TestTable VALUES(7, 'Ron', NULL, NULL)
INSERT @TestTable VALUES(9, 'Sheryl', 'Mary', 'Mark')SELECT * FROM @TestTable
INSERT @TestTable2
(
NameRecord,
SingleName
)
SELECT ???The @TestTable looks like:
The results should look something like:
I could do this with a cursor but would prefer not to.
Thanks,
Tom
Just Simple use UNPIVOT
SELECT u.Namerecord, u.CNAME, u.NAME
FROM [ANT_STAGE_MISC].[dbo].[FARMFILE] s
unpivot
(
NAME
for CNAME in (NAME1,NAME2,NAME3)
) u
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply