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
Solved

T-SQL to copy table content to new table structure

Posted on 2013-11-22
6
422 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

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…
I’m often asked about newer and larger USB drives connected to SBS2008 and 2011 failing Windows Server Backup vs the older USB drives not failing. As disk space continues to grow and drive technology change SBS2008 and some SBS2011 end up with the f…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

808 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