Solved

T-SQL to copy table content to new table structure

Posted on 2013-11-22
6
418 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
  • 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

A quick step-by-step overview of installing and configuring Carbonite Server Backup.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now