Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

T-SQL to copy table content to new table structure

Posted on 2013-11-22
6
Medium Priority
?
429 Views
Last Modified: 2013-11-22
I'm looking for some T-SQL to migrate the content of table1 to table2 (see attachment).

I expect I need to loop through the columns in table1 and insert a new row in table2 for each value that is not null in table1.fieldx so using a cursor will probably be necessary, but open to other ideas to achieve this.

I'm using MS SQL Server 2008.
tablemigration.png
0
Comment
Question by:guswebb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39668487
Why not do something like this if it is a one off:

select * into table2 from table1 where table1.field is not null

Open in new window

0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39668497
Actually yeah, sorry, ignore my first comment. That won'tt help you with that structure. Let me think a minute...
0
 
LVL 9

Author Comment

by:guswebb
ID: 39668511
I should have mentioned there are around 1 million rows in table1, so the migration will yield several million rows in table2.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39668517
I can't think of a way around using a cursor for this unless anyone has any suggestions using a pivot?

If I were to do this I'd create the target table manually. I'd create one cursor to select the initial data and then I'd have a second cursor that was used to pull the data out for each matching ID and QID that put these into a table variable. Once the inner cursor ended I'd do an insert into the target table.

For a million rows, it wouldn't be the most efficient way of doing it but it would work. Sometimes using an axe to open a tin is the best way!
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 total points
ID: 39668528
how many columns?
what datatypes are the columns?

you could in general use the UNPIVOT clause  to generate the normalised rows to insert to table 2....

you in general do not need to use a cursor to perform such processing...

1 million source rows to a few 10's of million target rows should not in general cause any problems given a good basic server configuration.

in general you would just do

Insert into table2 (id,qid,[field],[datetime])
 select id,qid,field1,[datetime]
   from table2
  where field1 is not null

and either have several individual inserts

or

Insert into table2 (id,qid,[datetime],[field])
 select id,qid,[datetime]
   ,case x.n when 1 then field1
             when 2 then field2
             when 3 then field3
            ...
             end

   from table2
  cross join (select 1 as n union all select 2 union all select 3 union all select 4 ....) as x

  where (x.n = 1 and field1 is not null)
      or (x.n=2 and field2 is not null)
      or (x.n=3 and field3  is not null)
      ...
  order by id,qid,x.n
0
 
LVL 9

Author Comment

by:guswebb
ID: 39668585
The data types are:

Table1
ID: int
QID: int
Field1, Field2 etc: varchar(max)
Datestamp: datetime

Table2
ID: int
QID: int
Field: varchar(max)
Datestamp: datetime
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question