Insert two rows at the end on every...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert two rows at the end on every...

Author

Topic

Pirre0001
Starting Member

19 Posts

Posted - 2014-05-17 : 17:53:27

I have a table that stores a structure. With the main material and component. The components are numbered.

Now I want to insert two components in the end of all components per main material in the table. These two new components are similar for all materials. How do I do this the best way?

Ex:


Material----Component----RowRn----
----------------------------------
Label2------EVO_121---------1-----
Label2------EVO_243---------2-----
Label2------EVO_638---------3-----
Label3------EVO_311---------1-----
Label3------EVO_120---------2-----
Label3------EVO_081---------3-----
Label3------EVO_987---------4-----

The two new components are NewComp1 and NewComp2
I want it to loo like this:


Material----Component----RowRn----
----------------------------------
Label2------EVO_121---------1-----
Label2------EVO_243---------2-----
Label2------EVO_638---------3-----
Label2------NewComp1--------4-----
Label2------NewComp2--------5-----
Label3------EVO_311---------1-----
Label3------EVO_120---------2-----
Label3------EVO_081---------3-----
Label3------EVO_987---------4-----
Label3------NewComp1--------5-----
Label3------NewComp2--------6-----

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-05-18 : 20:35:47

Following is not best way...

INSERT INTO Structure
SELECT
Material, 'NewComp1', MAX(RowRn) + 1
FROM Structure
GROUP BY Material

INSERT INTO Structure
SELECT
Material, 'NewComp2', MAX(RowRn) + 1
FROM Structure
GROUP BY Material

-------------------------------------
From Japan
Sorry, my English ability is limited.

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-05-19 : 01:30:15

Something like this...


--Table Creation 
CREATE TABLE Temp (Material VARCHAR(MAX),Component VARCHAR(1024),RowRn INT)
INSERT INTO Temp
SELECT 'Label2','EVO_121',1 UNION ALL
SELECT 'Label2','EVO_243',2 UNION all
SELECT 'Label2','EVO_638',3 UNION ALL
SELECT 'Label3','EVO_311',1 UNION ALL
SELECT 'Label3','EVO_120',2 UNION ALL
SELECT 'Label3','EVO_081',3 UNION ALL
SELECT 'Label3','EVO_987',4
--Inserting data
INSERT INTO Temp
SELECT DISTINCT Material,'NewComp1',MAX(RowRn+1) OVER (PARTITION BY Material)RowRn FROM Temp
UNION ALL
SELECT DISTINCT Material,'NewComp2',MAX(RowRn+2) OVER (PARTITION BY Material)RowRn FROM Temp
--Sorting the records
SELECT * from Temp ORDER BY Material,RowRn

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

pirre001
Starting Member

11 Posts

Posted - 2014-05-19 : 14:08:44

Thanks!

I also wanna check if these two new NewComp is on the last and second to last row before insert. If it is so, no insert will be made.
On MAX (RowRn) and MAX (RowRn) -1.
How do I do that?

quote:
Originally posted by nagino

Following is not best way...
INSERT INTO Structure
SELECT
Material, 'NewComp1', MAX(RowRn) + 1
FROM Structure
GROUP BY Material

INSERT INTO Structure
SELECT
Material, 'NewComp2', MAX(RowRn) + 1
FROM Structure
GROUP BY Material

-------------------------------------
From Japan
Sorry, my English ability is limited.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-20 : 07:51:00

Question: What do you meant by last and second last row? Remember a table is a set. By definition a set is unordered. There is no guarantee that if I insert five rows into a table they will be stored in the order 1,2,3,4,5. The actual physical ordering depends on a number of factors. The logical ordering depends on the clustered key, if there is one. Do you have a clustered index? If so, on which column(s)?

pirre001
Starting Member

11 Posts

Posted - 2014-05-20 : 15:31:08

By using RowRn (is a counter)that I showed to get up the last and second to last line.
MAX (RowRn) -> The last line
MAX (RowRn) - 1 second to last line

quote:
Originally posted by gbritton

Question: What do you meant by last and second last row? Remember a table is a set. By definition a set is unordered. There is no guarantee that if I insert five rows into a table they will be stored in the order 1,2,3,4,5. The actual physical ordering depends on a number of factors. The logical ordering depends on the clustered key, if there is one. Do you have a clustered index? If so, on which column(s)?

GouravSaxena1987
Starting Member

23 Posts

Posted - 2014-05-21 : 05:20:53

Hello,

I guuess for implement this you need to write store procedure:

Below is store procedure which have your logic:

Create PROCEDURE usp_Ins_MaterialInformation
@NewCompName VARCHAR(50)

AS
BEGIN
SET NOCOUNT ON;

DECLARE
--@CoompName VARCHAR(50),
@MaterialName VARCHAR(50),
@i INT,
@MaterialCount INT,
@RowID INT;

DECLARE @MaterialList table (MaterialNameT VARCHAR(50), CountRowT INT);

SET @i=0;
SELECT @MaterialCount= COUNT(distinct MaterialName) from Material_Information

INSERT INTO @MaterialList
SELECT MaterialName,row_number() OVER (ORDER BY MaterialName) AS CountRow
FROM [dbo].[Material_Information] GROUP BY MaterialName;

--SELECT @I,@MaterialCount;
WHILE (@I<=@MaterialCount)

BEGIN

SELECT 1;
SELECT @MaterialName=MaterialNameT FROM @MaterialList WHERE CountRowT=@i;

IF NOT EXISTS (SELECT 1 FROM Material_Information WHERE MaterialName=@MaterialName AND ComponentName=@NewCompName )

BEGIN

INSERT INTO Material_Information (MaterialName,ComponentName,RowID)
SELECT MaterialName,@NewCompName,MAX(RowID) + 1 FROM [dbo].[Material_Information]
WHERE MaterialName=@MaterialName GROUP BY MaterialName

--SELECT 1
END

--SELECT @i as test;
SET @i=@i+1;

END
END

GO

Regards,
Gourav Saxena
Data Warehouse Counsultant
GouravSaxena1987@gmail.com

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-21 : 07:30:21

Here's a solution that does it in one go. It uses EXCEPT to stop the insertion of duplicate NewComp rows:


CREATE TABLE Temp (Material VARCHAR(MAX),Component VARCHAR(1024),RowRn INT)
INSERT INTO Temp
SELECT 'Label2','EVO_121',1 UNION ALL
SELECT 'Label2','EVO_243',2 UNION all
SELECT 'Label2','EVO_638',3 UNION ALL
SELECT 'Label3','EVO_311',1 UNION ALL
SELECT 'Label3','EVO_120',2 UNION ALL
SELECT 'Label3','EVO_081',3 UNION ALL
SELECT 'Label3','EVO_987',4
--Inserting data
INSERT INTO Temp

-- Get max RowRn by Material, excluding Component like 'NewComp%'
SELECT Material, NewComp, NewRn FROM 
	(SELECT DISTINCT Material,MAX(RowRn) RowRn FROM Temp
	 WHERE Component NOT LIKE'NewComp%'
	 GROUP BY Material
	) a

-- Add two new rows
CROSS APPLY (SELECT 'NewComp1', RowRn+1 UNION ALL SELECT 'NewComp2',RowRn+2 )  b(NewComp, NewRn)

-- Don't add duplicate rows.
EXCEPT
SELECT * FROM Temp WHERE Component LIKE 'NewComp%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources