Inserting information from one table into another.

Question

Hello, I am working on moving information from one of our databases into a newer one the company has recently setup. I am working in MS SQL Server 2012 and am trying to Inset/Update (depending on the information) a part table. I will list the two tables I am working on as Old (where I am getting the information) and New (where it is to be inserted into). The old table has about 250 columns of information, most of which is not needed. The needed information is as follows: Name, ID, Component1, Component1_PIN, Component1_SN, Component1_Description, Component2, Component2_PIN, Component2_SN. The component section repeats up to Component12. The new table has columns setup as such: Name, ID, Case, CasePIN, CaseSN, Case_Desc, Processor, ProcessorPIN, ProcessorSN, Processor_Description, Memory, MemoryPIN, MemorySN, Memory_Description, etc. The issue I am having is that in the old table each component can have Case, Processor, Memory, etc in that one column. I need to insert Case, Memory, etc into the correct column in the new table while keeping intact the rest of the information.  Example: Old Table Name | ID | Component1 | Component1_PIN | Component1_SN | Component1_Description | Component2 | Component2_PIN | Component2_SN | Component2_Description Nest8 | 5682 | Case | 901834 | 237848117 | Black, rectangular | Memory | 9081234 | 5398798134 | NULL Nest8 | 5978 | Case | 901463 | 237848138 | Black, rectangular | Processor | 2394875 | 2903857809 | Bad Reds3 | 5683 | Memory | 2405 | 89752342 | Crucial | HardDrive | 92387595 | 457982234 | NULL Bass | 5644 | HardDrive | 79872346 | 5321675789 | NULL | Case | 10984528 | 3498769872 | NULL I am not sure how to loop through and grab each part and place it in the column it needs to be while keeping it with the ID.  Thank you for any assistance in advance.

Answer 1

Looks like you need to PIVOT the original data first. 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 2

INSERT INTO new_table (Foo, Bar, Fizz, Buzz)
SELECT Foo, Bar, Fizz, Buzz
FROM initial_table
-- optionally WHERE ...

The INSERT INTO ... SELECT FROM syntax is for when the table you're inserting into ("new_table" in my example above) already exists. The SELECT ... INTO syntax is for when you want to create the new table as part of the command. INSERT INTO ... SELECT FROM should be fine if your destination table already exists.

Additionally, you can have a look at the following articles:

1. Insert Data From One Table to Another Table
2. Inserting Data Into a Table From Another Table (video)
3. Inserting Rows by Using SELECT INTO
4. select from one table and insert into another

Answer 3

Thank you Andrey, but I am not really looking at the Insert INTO portion as much as how to get that data sorted and then inserted.  The title information was not worded the way it needed to be.  It looks as if a PIVOT is the way to go.  Now to investigate more into that.

Answer 4

SELECT Name, ID,
       MIN(CASE WHEN Component1 = 'Case' THEN Component1_PIN END) AS CASE_PIN,
       MIN(CASE WHEN Component1 = 'Case' THEN Component1_SN END) AS CASE_SN,
       ...
FROM   old_tbl
GROUP  BY Name, ID

This is a pivot operation, but without the PIVOT operator which is only confusing and which you should not waste your time on.

The key here is that every MIN only sees one non-value, so tat is what you get.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 5

Hi staticlie,

If I understand your requirement correctly, you can reference the below sample.

DECLARE @T TABLE(Name VARCHAR(100), ID INT, Component1 VARCHAR(100), Component1_PIN INT,
				 Component1_SN VARCHAR(100), Component1_Description VARCHAR(100), 
				 Component2 VARCHAR(100), Component2_PIN INT, Component2_SN VARCHAR(100), Component2_Description VARCHAR(100)
)

INSERT INTO @T 
VALUES
('Nest8' , 5682 , 'Case' , 901834 , 237848117 , 'Black, rectangular' , 'Memory' , 9081234 , 5398798134 , NULL),
('Nest8' , 5978 , 'Case' , 901463 , 237848138 , 'Black, rectangular' , 'Processor' , 2394875 , 2903857809 , 'Bad'),
('Reds3' , 5683 , 'Memory' , 2405 , 89752342 , 'Crucial' , 'HardDrive' , 92387595 , 457982234 , NULL),
('Bass' , 5644 , 'HardDrive' , 79872346 , 5321675789 , NULL , 'Case' , 10984528 , 3498769872 , NULL);

SELECT * FROM @T ORDER BY Name,ID

--Name, ID, Case, CasePIN, CaseSN, Case_Desc, Processor, ProcessorPIN, ProcessorSN, Processor_Description, Memory, MemoryPIN, MemorySN, Memory_Description

;WITH Cte AS(
SELECT NAME,ID,Component1 Component,Component1_PIN Component_PIN,Component1_SN Component_SN,Component1_Description Component_Description FROM @T
UNION ALL
SELECT NAME,ID,Component2,Component2_PIN,Component2_SN,Component2_Description FROM @T
)
SELECT NAME,ID,
       MIN(CASE WHEN Component='case' THEN 'Case' ELSE NULL END) [Case],
	   MIN(CASE WHEN Component='case' THEN Component_PIN ELSE NULL END) CasePIN,
	   MIN(CASE WHEN Component='case' THEN Component_SN ELSE NULL END) CaseSN,
	   MIN(CASE WHEN Component='case' THEN Component_Description ELSE NULL END) Case_Desc,
	   MIN(CASE WHEN Component='Processor' THEN 'Processor' ELSE NULL END) [Processor],
	   MIN(CASE WHEN Component='Processor' THEN Component_PIN ELSE NULL END) ProcessorPIN,
	   MIN(CASE WHEN Component='Processor' THEN Component_SN ELSE NULL END) ProcessorSN,
	   MIN(CASE WHEN Component='Processor' THEN Component_Description ELSE NULL END) Processor_Desc,
	   MIN(CASE WHEN Component='HardDrive' THEN 'HardDrive' ELSE NULL END) [HardDrive],
	   MIN(CASE WHEN Component='HardDrive' THEN Component_PIN ELSE NULL END) HardDrivePIN,
	   MIN(CASE WHEN Component='HardDrive' THEN Component_SN ELSE NULL END) HardDriveSN,
	   MIN(CASE WHEN Component='HardDrive' THEN Component_Description ELSE NULL END) HardDrive_Desc,
	   MIN(CASE WHEN Component='Memory' THEN 'Memory' ELSE NULL END) [Memory],
	   MIN(CASE WHEN Component='Memory' THEN Component_PIN ELSE NULL END) MemoryPIN,
	   MIN(CASE WHEN Component='Memory' THEN Component_SN ELSE NULL END) MemorySN,
	   MIN(CASE WHEN Component='Memory' THEN Component_Description ELSE NULL END) Memory_Desc
	   FROM Cte
	   GROUP BY Name,ID
	   ORDER BY Name,ID

If you have any question, feel free to let me know.

---

Eric Zhang
TechNet Community Support

Answer 6

Eric,

  Thank you for the help.  I am having one issue though, I need to add in Component3, component4, etc but it is not grabbing the information for those fields.  

Example I am looking for monitor in Component5 but it shows as null when I have created the Case for the component.  

I can provide more information if needed as well.  

Thank you.

Answer 7

Hi staticlie,

Please post more detailed data with the Component3,Component4 etc, you can export schema and data with SQL Server Management Studio.

You could post your query as well so that we can look into what cause your issue.

If you have any question, feel free to let me know.

---

Eric Zhang
TechNet Community Support