6

I am relatively new when working with queries so let me apologize in advance. This is SQL Server 2012 that I will be working with. This data has been brought into a staging table through a nightly import from Excel. I need to move this data from this table into our main hardware table and make sure that I insert any new data while checking to make sure it updates any of the older data. I will give as much information as I can, please bear with me.

The field names are:

|Ticket_Num | Name | Worker | date1 | Serial | Part | Description1 | Type1 | Eqmt_Type |

I need to bring them into a table that has this:

   |Event_NUM|Name|Install_Date|Pull_Date|Install_Tech|Pull_Tech| PMP1_Desc|PMP1_Part|PMP1_Serial|PMP2_Desc|PMP2_Part|PMP2_Serial|PMP3_Desc|PMP3_Part|PMP3_Serial|Motor1_Desc|Motor1_Serial|Motor1_Part|Motor2_Desc| Motor2_Serial| Cab1_Disc|Cab1_Part|Cab1_Serial|Cab2_Desc|Cab2_Part|Cab2_Serial|Dis1_Desc|Dis1_Part|Dis1_Serial|Dis2_Desc|Dis2_Part|Disc2_Serial|

Here is SQL Fiddle with an example of the data being pulled in from Excel: SQL Fiddle for data to be inserted

Here is SQL Fiddle with an example of what I would like: SQL Fiddle example of what I would like

2
  • Is there always <= 3 PMP, <= 2 motor, <= 2 dis, and <= 2 cab? Aug 20, 2015 at 21:06
  • It will be <=5 PMP, <= 2 motor, <= 3 dis, and <= 2 cab. I am working with more equipment types, but cut some of it out so it was more readable when posting the question.
    – Sandy
    Aug 20, 2015 at 21:33

2 Answers 2

3

Is it possible to redesign your schema? It feels like you are making life harder for yourself by basically trying to pivot the data you're importing from the excel spreadsheets.

CREATE TABLE dbo.Hardware -- hw?
(
    [Event_Num] INT NOT NULL PRIMARY KEY,
    [Name] NVARCHAR(100) NOT NULL,
    [Install_Date] DATE NULL,  -- Install after pulling?
    [Pull_Date] DATE NOT NULL,
    [Install_Tech] INT NULL FOREIGN KEY REFERENCES dbo.techs(UserId), -- separate out your techs to another table
    [Pull_Tech] DATE NOT NULL FOREIGN KEY REFERENCES dbo.techs(UserId), -- separate out your techs to another table
    <Add other common fields here>
)

CREATE TABLE dbo.ComponentType -- Or some better name for PMP, Motor, dis, etc. table
(
    [ComponentTypeId] INT NOT NULL PRIMARY KEY,
    [Description] NVARCHAR(50) NOT NULL -- This is where you would put PMP, Motor, etc.
)

CREATE TABLE dbo.WorkPerformed
(
    [WorkPerformedId] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    [ComponentTypeId] INT NOT NULL FOREIGN KEY REFERENCES dbo.ComponentType(ComponentTypeId), -- FK to type lets us reuse this structure for all components and prevents the need to pivot the excel data
    [Event_Num] INT NOT NULL FOREIGN KEY REFERENCES dbo.Hardware(Event_Num), -- Allows you to associate the work performed with the hw
    [Description] NVARCHAR(400) NULL,
    [SerialNumber] NVARCHAR(50) NOT NULL,
    [Part] NVARCHAR(50) NOT NULL,
    <Other valuable info not currently tracking, ie pulled/installed, tech doing work, etc>
)

Obviously that is just a rough sample of the way the schema could be setup. I'm sure you can see that mapping the data will be much easier now, and more flexable for future updates. In order to maintain backwards compatibility, if needed, you could just create a view with the current table's name and select the data from the new tables.

If going down this path is not possible/acceptable I would look at pulling your data out of the temp table and inserting it into your current table with a PIVOT. See this TechNet article for basic information about pivoting (the syntax from 2008R2 will work in 2012).

0

I would go for a 2 step solution. I use simple SQL. You can use the right names and fields. First I would update the existing rows:

update tab1
set tab1.field = tab1.field + tmp1.field
from table1 tab1 inner join temp1 tmp1 on tab1.key=tmp1.key;

Secondly I would insert all non-existing rows:

insert into table1 (key, field)
select key, field
from   temp1
where  tmp1 not in (select key
                    from   table1);

To finish you must truncate the temp1 table and commit.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.