Solved

T-SQL to copy table content to new table structure

Posted on 2013-11-22
6
424 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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 500 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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
A quick step-by-step overview of installing and configuring Carbonite Server Backup.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

695 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