asked on 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
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
SBSMicrosoft SQL Server 2008
Last Comment
guswebb
Actually yeah, sorry, ignore my first comment. That won'tt help you with that structure. Let me think a minute...
ASKER
I should have mentioned there are around 1 million rows in table1, so the migration will yield several million rows in table2.
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!
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!
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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
Table1
ID: int
QID: int
Field1, Field2 etc: varchar(max)
Datestamp: datetime
Table2
ID: int
QID: int
Field: varchar(max)
Datestamp: datetime
Open in new window