将一个表的5列保存到不同表中的5行中

I have a table Items having a unique id: itemId, … Example of a row: ‘3506’, …

我有一个表具有唯一ID的项:itemId,...行的示例:'3506',...

I have a table ItemProperties with the following columns: itemId, datecreated, datemodified, property1, property2, property3, property4, property5

我有一个表ItemProperties与以下列:itemId,datecreated,datemodified,property1,property2,property3,property4,property5

example of a row: ‘3506’, ‘2012…’, ‘2012…’, ‘prop1’, ‘prop2’, ‘prop3’, ‘prop4’, ‘prop5’

行的示例:'3506','2012 ...','2012 ...','prop1','prop2','prop3','prop4','prop5'

I need to split every row of the ItemProperties table into 5 separate rows in the NewItemProperties table with the following columns: itemId, datecreated, datemodified, propertynumber, property

我需要将ItemProperties表的每一行拆分为NewItemProperties表中的5个单独的行,并包含以下列:itemId,datecreated,datemodified,propertynumber,property

example of the 5 rows:

5行的示例:

‘3506’, ‘2012…’, ‘2012…’, 1, ‘prop1’

‘3506’, ‘2012…’, ‘2012…’, 2, ‘prop2’

‘3506’, ‘2012…’, ‘2012…’, 3, ‘prop3’

‘3506’, ‘2012…’, ‘2012…’, 4, ‘prop4’

‘3506’, ‘2012…’, ‘2012…’, 5, ‘prop5’

Constraints: - ItemProperties with an itemId that is no longer in the Items table should not be processed

约束: - 不应处理具有不再位于Items表中的itemId的ItemProperties

• Items that are already in the NewItems table(id =id and propertynumber=number of property1~5 column) should not be overwritten/updated by this process

  不应该通过此过程覆盖/更新已在NewItems表中的项(id = id和propertynumber = property1~5列的数量)

• Microsoft SQL Server 2008 R2

  Microsoft SQL Server 2008 R2

• Around 800000 rows in the ItemProperties table

  ItemProperties表中大约有800000行

Very slow solution I have at this moment:

我现在的解决方案非常慢:

...

DECLARE c CURSOR read_only FOR SELECT * from ItemProperties

DECLARE @ID varchar(14), @CREA datetime, …

OPEN  c     

FETCH NEXT FROM c INTO @ID, @CREA, @MODI, @COL, @SIZE, @PAT, @YEAR, @ITEM

WHILE (@@fetch_status <> -1)

BEGIN

    IF (@@fetch_status <> -2)

    BEGIN
         select * from Items where itemId = @ID
         if @@ROWCOUNT = 0
               BEGIN
                    GOTO Fetch_Next
               END
         select * from ItemProperties where itemId = @ID AND propertynumber = 1
         if @@ROWCOUNT = 0
               INSERT INTO NewItemProperties
               VALUES(@ID, '1', @COL, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
         select * from ItemProperties where itemId = @ID AND propertynumber = 2
         if @@ROWCOUNT = 0
               INSERT INTO NewItemProperties
               VALUES(@ID, '2', @SIZE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
     …

      END
    Fetch_Next:
    FETCH NEXT FROM c INTO @ID, @CREA, @MODI, @COL, @SIZE, @PAT, @YEAR, @ITEM
...

I can optimize my fetch statement by removing the creation and modification dates but that is about all I can come up with. I could not fit this into an unpivot query. Basically anything that is faster then a cursor will help me out. All ideas are welcome. Thank you.

我可以通过删除创建和修改日期来优化我的fetch语句,但这是我能想到的全部内容。我无法将其纳入一个非透视查询。基本上任何比光标快的东西都会帮助我。欢迎所有想法。谢谢。

3 个解决方案

#1

Try this

INSERT INTO NewItemProperties
SELECT itemId, '1', property1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
FROM ItemProperties 
UNION ALL
SELECT itemId, '2', property2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
FROM ItemProperties
......
UNION ALL
SELECT itemId, '5', property5, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
FROM ItemProperties

#2

I am unsure why you stated that you could not get this to fit in an UNPIVOT. But you should be able to use an UNPIVOT to transform your data and then insert it into the new table. You can add any WHERE clause conditions on the inner query. The key to the UNPIVOT is that the data be of the same datatype, so if needed you might have to cast the data in the subquery to the same datatype:

我不确定为什么你说你不能让这个适合UNPIVOT。但您应该能够使用UNPIVOT转换数据,然后将其插入新表中。您可以在内部查询上添加任何WHERE子句条件。 UNPIVOT的关键是数据具有相同的数据类型,因此如果需要,您可能必须将子查询中的数据转换为相同的数据类型:

insert into NewItemProperties(yourCcolsHere)
select itemid,
  datecreated, 
  datemodified,
  replace(col, 'property', '') col,
  value
from
(
 -- perform any datatype conversions here
  select i.itemid,
    p.datecreated,
    p.datemodified,
    p.property1, 
    p.property2, 
    p.property3, 
    p.property4, 
    p.property5
  from items i
  inner join ItemProperties p
    on i.itemid = p.itemid
) src
unpivot
(
  value
  for col in (property1, property2, property3,
              property4, property5)
) unpiv

See SQL Fiddle with Demo

请参阅SQL Fiddle with Demo

The UNPIVOT function performs the same function as a UNION ALL query:

UNPIVOT函数执行与UNION ALL查询相同的功能:

insert into NewItemProperties(yourCcolsHere)
select i.itemid,
    p.datecreated,
    p.datemodified,
    1 PropNumber,
    p.property1 value 
from items i
inner join ItemProperties p
  on i.itemid = p.itemid
union all
select i.itemid,
    p.datecreated,
    p.datemodified,
    2 PropNumber,
    p.property2 value
from items i
inner join ItemProperties p
  on i.itemid = p.itemid -- add more queries for the other properties

See SQL Fiddle with Demo

请参阅SQL Fiddle with Demo

#3

I would do this by defining a query that returns everything to insert, and then inserting them:

我会这样做,通过定义一个返回要插入的所有内容的查询,然后插入它们:

with toinsert as (
     select ip.itemid, datecreated, datemodified, num as propertynumber
            (case when num = 1 then property1
                  when num = 2 then property2
                  when num = 3 then property3
                  when num = 4 then property4
                  when num = 5 then property5
             ) as property
     from ItemProperties ip cross join
          (select 1 as num union all select 2 union all select 3 union all select 4 union all select 5
          ) nums left outer join
          NewItemProperties nip
          on ip.itemid = nip.itemid and
             nums.num = nip.propertynumber
     where nip.itemid is null
    )
insert into NewItemProperties (itemid, datecreated, datemodified, propertynumber, property)
    select *
    from toinsert