complex data import - 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

SQL Server 2005 Forums

Transact-SQL (2005)

complex data import

Author

Topic

rum23
Yak Posting Veteran

77 Posts

Posted - 2009-02-05 : 13:45:07

I have complex data migration that I need to peform between 2 tables in my sql server database. I'm not sure if this can be done using SQL scripts and hence wanted to check with you, the experts, before I start writing some vb code.

Here is the situation.

I have projects table called (tblProjects). The fields in this table are
ProjectID,
D_WaterPressTest,
D_SewerLineInsp,
D_HydrantFlowTest,
D_ReclaimPressTest

I have the inspections table called (tblProjectInspections). The fields in this table are
ProjectID,
TestName,
TestDate

I have another static table called tblTestNames. The fields in this table are
RowID,
TestName

The data in tblTestNames is as follows.

Date Water Press Test
Date Sewer Line Inspection
Date Hydrant Flow Test
Date Reclaim Press Test

Now, I want to import all the data from tblProjects to tblProjectInspections. For e.g.:

The data in tblProjects looks like this....
ProjectID|D_WaterPressTest|D_SewerLineInsp|D_HydrantFlowTest| D_ReclaimPressTest
B-829|1/1/2008|1/14/2008|2/25/2008|3/13/2008

and I want to import the data from tblProjects to tblProjectInspections like this.
ProjectID TestName TestDate
B-829 Date Water Press Test 1/1/2008
B-829 Date Sewer Line Inspection 1/14/2008
B-829 Date Hydrant Flow Test 2/25/2008
B-829 Date Reclaim Press Test 3/13/2008

Date Water Press Test corresponds to tblProjects.D_WaterPressTest field and so forth. Is this possible to do in SQL scripts? Please help. Let me know if you need more info

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-05 : 14:04:31

You can use like this:

Select t.ProjectID,
tbl.TestName,
t.D_WaterPressTest,
t.D_SewerLineInsp,
t.D_HydrantFlowTest,
t.D_ReclaimPressTest from
tblProjects t inner join tblTestNames tbl
on t.RowID = tbl.ProjectID

And then UNPIVOT the data to your requirement and insert to tblproject.

rum23
Yak Posting Veteran

77 Posts

Posted - 2009-02-05 : 14:29:26

tblTestNames.RowID is not equal to tblProject.ProjectID.

RowID is just an incremental number in the table and projectID is the actual ID of the project. But I can make an assumption that
D_WaterPressTest is will always correspond to "Date Water Press Test
" in tblTestNames table.

Thanks for your reply

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-02-05 : 15:20:19

[code]
SELECT ProjectID,
'Date Water Press Test' AS TestName,
D_WaterPressTest AS TestDate
FROM tblProjects
UNION
SELECT ProjectID,
'Date Sewer Line Inspection',
D_SewerLineInsp
FROM tblProjects
UNION
SELECT ProjectID,
'Date Hydrant Flow Test',
D_HydrantFlowTest
FROM tblProjects
UNION
SELECT ProjectID,
'Date Reclaim Press Test',
D_ReclaimPressTest
FROM tblProjects
[/code]

If you don't want to use the literal values for TestName you can replace with (SELECT TestName FROM tblTestNames WHERE RowID = ...) assuming RowID is unique.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources