• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 430
  • Last Modified:

T-SQL to copy table content to new table structure

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
guswebb
Asked:
guswebb
  • 3
  • 2
1 Solution
 
Lee SavidgeCommented:
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
 
Lee SavidgeCommented:
Actually yeah, sorry, ignore my first comment. That won'tt help you with that structure. Let me think a minute...
0
 
guswebbAuthor Commented:
I should have mentioned there are around 1 million rows in table1, so the migration will yield several million rows in table2.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Lee SavidgeCommented:
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
 
LowfatspreadCommented:
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
 
guswebbAuthor Commented:
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

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now