More on Data Transfer - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

More on Data Transfer

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-07-15 : 15:51:10

I have to transfer data from a table where I have data columns for 24 hours of the day – to a table where there is just one column for the data. So data from 24 columns from the first table will go to 24 rows of the new table.
Each of the tables have same type of header tables. So, the old tables are like:
Header_Data - Header_NUM(PK), Header_TYPE (FK),Header_START_DATE],[ Header_START_TIME],[ Header_END_DATE],[ Header_END_TIME]

Detail_Data – DT_Header_NUM,DT_SAMPLE_TYPE,DT_PARAMETER,DT_UNITS,DT_METHOD, ,DT_HOUR1_VALUE, DT_HOUR1_FLAG,DT_HOUR2_VALUE,……………DT_HOUR23_VALUE,DT_HOUR24_VALUE.

New tables are as follows:

New_Header_Data – Same

New_Detail_Data - DATA_ID,DATA_Header_NUM,DATA_PARAMETER,DATA_METHOD,DATA_UNIT DATA_VALUE,DATA_FLAG

Also, in the old header, ids (header_num) was one for each day. In the new - it will be one for each hour. So I need to create 24 rows for each row in the old header table.

I need help to build this script, Thanks.

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2009-07-15 : 16:58:37

I'm not sure I follow you 100%, but see if this gets you going in the right direction.

Create Table ##test
(DT_Header_Num int
, DT_Sample_type int
, DT_Parameter int
, DT_Units int
, DT_Method int
, DT_Hour1_Value varchar(50)
, DT_Hour1_Flag bit
, DT_Hour2_Value varchar(50)
, DT_Hour2_Flag bit
, DT_Hour3_Value varchar(50)
, DT_Hour3_Flag bit)

insert into ##test values (1, 1, 1, 1, 1, 'test 1', 1, 'test 2', 1, 'test 3', 1)
insert into ##test values (2, 2, 2, 2, 2, 'test a', 1, 'test b', 1, 'test c', 1)

Create Table ##new
(Data_ID int identity(1,1)
, DT_Header_Num int
, DT_Sample_Type int
, DT_Parameter int
, DT_Units int
, DT_Method int
, DT_Hour int
, DT_Value varchar(50)
, DT_Flag bit)

Declare @cmd nvarchar(4000), @x int
Set @x = 1
While @x < 4
Begin
Set @cmd = 'Insert Into ##new
Select DT_Header_Num
, DT_Sample_Type
, DT_Parameter
, DT_Units
, DT_Method
, ' + Convert(varchar, @x) + '
, DT_Hour' + Convert(varchar, @x) + '_Value
, DT_Hour' + Convert(varchar, @x) + '_Flag
From ##test'

Exec sp_ExecuteSql @cmd
Set @x = @x + 1
End

Select * From ##new

Drop Table ##new
Drop Table ##test

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-07-16 : 12:11:45

Hi JoeNak,

I had some incorrect information in my initial post. Here’s the detailed correct flow of how things should happen:
1) The header table has the primary key (header_num), so I’ll have to fill the new header table from the old header table first.
When I do that – in the “old header” table, there is ONE ROW (one key) for 24 hours of the day. In the “new header” table – For each Date, I will have to have 24 rows – One for each hour of the day. The start time, end times for each hours will be like 0000/0059, 0100/0159 and so on. We need to run a loop for this.

2) Next step is to transfer data to the “new Data” table. Here, I need to fetch data from two tables. Each Row will be like as follows:
For each Date - the foreign key values should come from the newly populated header (new header) table, and data values from the “old data” table. In the “old data” I have 24 data columns for 24 hours of the day. Data from 24 columns of this table will go to 24 rows of the new data table. So the key comes from the “new Header” where start time / end time is 0000/0059 and data comes from the first column of the “old data” table e.g. DT_HOUR1_VALUE.
Please refer to my initial post for the table definition.
Thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-16 : 12:55:53

Is this different than your other topic on this subject? http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=129342

Here is one method using UNIONs:

INSERT New_Header_Data

SELECT 
	DATA_ID, -- not sure what this is
	DT_Header_Num AS DATA_Header_NUM,
	DT_PARAMETER AS DATA_PARAMETER,
	DT_METHOD AS DATA_METHOD,
	DT_UNITS AS DATA_UNITS, 
	DT_HOUR1_VALUE AS DATA_VALUE,
	DT_HOUR1_FLAG AS DATA_FLAG
FROM 
	Detail_Data

UNION ALL

SELECT 
	DATA_ID, -- not sure what this is
	DT_Header_Num,
	DT_PARAMETER,
	DT_METHOD,
	DT_UNITS, 
	DT_HOUR2_VALUE,
	DT_HOUR2_FLAG
FROM 
	Detail_Data
	
UNION ALL 

-- Other 21 selects go here ........ 

SELECT 
	DATA_ID, -- not sure what this is
	DT_Header_Num,
	DT_PARAMETER,
	DT_METHOD,
	DT_UNITS, 
	DT_HOUR24_VALUE,
	DT_HOUR24_FLAG
FROM 
	Detail_Data

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-07-16 : 13:34:06

Hi Lamprey,

No - this is the same one. But look at my corrections on this page right before yours. Regards.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-16 : 14:25:38

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

sqlbug
Posting Yak Master

201 Posts

Posted - 2009-07-20 : 11:43:17

Here I put things together with more info:

Old Tables:
-----------
Header_Data(Stores 1 sample for 24 hours) - [Header_NUM](PK), [Header_TYPE](FK),[Header_START_DATE],
[Header_START_TIME],[Header_END_DATE],[Header_END_TIME]

Detail_Data – [DT_Header_NUM](FK),[DT_SAMPLE_TYPE],[DT_PARAMETER],[DT_UNITS],[DT_METHOD],
[DT_HOUR1_VALUE], [DT_HOUR1_FLAG],[DT_HOUR2_VALUE],……………[DT_HOUR24_VALUE],
[DT_HOUR24_FLAG].

New tables:
-----------
New_Header_Data – Same but stores 24 samples - 1 sample for each hour of the day.

New_Detail_Data - [DATA_ID],[DATA_Header_NUM](FK),[DATA_PARAMETER],[DATA_METHOD],
[DATA_UNIT],[DATA_VALUE],[DATA_FLAG].

1) The header table has the primary key (header_num), so I need to fill the new header
table from the old header table first. When I do that – in the “old header” table,
there is ONE ROW (one key) for 24 hours of the day.
In the “new header” table – For each Date, I will have to have 24 rows –
One for each hour of the day. The start time, end times for each hours will be like
0000/0059, 0100/0159(type - char(4)) and so on. We need to run a loop for this.

2) Next step is to transfer data to the “new Data” table. Here, I need to fetch data from
two tables. Each Row will be like as follows:
For each Date - the foreign key values should come from the newly populated header
(new header) table, and data values from the “old data” table. In the “old data” I have
24 data columns for 24 hours of the day. Data from 24 columns of this table will go to
24 rows of the new data table. So the key comes from the “new Header” where
start time / end time is 0000/0059 and data comes from the column of the “old data”
table e.g. DT_HOUR1_VALUE.

Hope this provides enough information to help me find a solution.
Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources