SQLTeam.com | Weblogs | Forums

Importing denormalized csv files into production tables

Hello everyone. I am a newbie to SQL development. I need your help to understand whether what I am about to do is feasible.

I have de-normalized data in csv files from different tables. I want to normalize the data and import them into tables in production database. Some of the records are many to many and also auto incremented keys that joined them together. The data, because they are not brand new, they are linked to existing records in the database.

As you can see from the attached file, the InterestID is from Interest Table, ContactID from contacted from comtact table etc but, the output was derived as a result of join queries. Again, the Landreference is one to many relationship to Interest and Contacts. So, field was transposed into column like this, 24,602,452,354,010,700. This would need to un-pivot and linked them back to contacts or Interest tables.

These arethe tables joined together for the csv output.
FROM dbo.tbl_Land
JOIN dbo.tbl_Land_Address
JOIN dbo.tbl_Address
JOIN dbo.tbl_Interest
JOIN dbo.tbl_Nature AS
JOIN dbo.tbl_Nature_Type

Can someone please advise if this is technically doable. Please see the sample data below.

Thanking you for your time and advise.

Rac

Possible and doable but highly unusual. What is it you are attempting to do here? Please explain the thinking behind this design approach?

Thanks Yosiasz
I have a csv file that was put together joining multiple tables from third party firm. We kind of have similar database table structure. What I am trying to do is to insert or append the csv into production relevant tables.
For example, InterestIds will be appended to Interest table and so on.
Hope this make sense.

got it. So in order for you to get prompt response from other busy people offering you free advise taking out time from their busy schedule you will need to provide us sample data not in the form of an image but real consumable data as follows

create sampledata(InterestId int, ContractId varchar(100), 
LoadReference varchar(10), TitleIDs --etc

insert into sample
select 6523,6119,'24,602,452,354,010,700','Freehold' ---etc and provide the full set of data

then you see we can try out this sample data on our sql servers?

hi

please try the following link
to see how to normalize your data ...

hope it helps :slight_smile: :slight_smile:

if you need me to do it
please let me know
I can give it a try ....

other people on this forum .. experts ..can do this very very quickly
for you ( seniors who have lots of experience )

Thanks once again. Please find my sample data below.

  1. The test data is coming from csv file. The landId is M:M relationships to to Title table.
  2. So, there will be only one landId for a specific TitleID but many TitleID can be associated with One landId.
  3. There is a bridge table called ##land_title between Title table and land table.
  4. Basically, I want to append the records from the csv to existing records in Land table, then insert corresponding values to ##land_title and then to Title table.
  5. I was trying this method but I need help from you guys. Hope this make sense.

INSERT INTO ##land_title( LandId, titleid, CreatedDateTime, CreatedUserID )
SELECT LandID, Land_TitleID, getdate(),'xyz'
FROM #Testdata as sd
OUTPUT inserted or SCOPE_IDENTITY()

CREATE TABLE #Testdata
(
LandID INT,
ReferenceID INT,
Land_TitleID VARCHAR(MAX)
)
INSERT #Testdata SELECT 1, 9, '18,20,22'
INSERT #Testdata SELECT 2, 8, '17,19'
INSERT #Testdata SELECT 3, 7, '13,19,20'
INSERT #Testdata SELECT 4, 6, ''
INSERT #Testdata SELECT 9, 11, '1,2,3,4'
Select * from #Testdata
;WITH tmp(LandID, ReferenceID, DataItem, Land_TitleID) AS
(
SELECT
LandID,
ReferenceID,
LEFT(Land_TitleID, CHARINDEX(',', Land_TitleID + ',') - 1),
STUFF(Land_TitleID, 1, CHARINDEX(',', Land_TitleID + ','), '')
FROM #Testdata
UNION all
SELECT
LandID,
ReferenceID,
LEFT(Land_TitleID, CHARINDEX(',', Land_TitleID + ',') - 1),
STUFF(Land_TitleID, 1, CHARINDEX(',', Land_TitleID + ','), '')
FROM tmp
WHERE
Land_TitleID > ''
)
SELECT
LandID,
ReferenceID,
DataItem
FROM tmp
ORDER BY LandID

Thanks

R

was hoping you would provide the sample data you posted in the image

but let me see what I can do.

Take a look at this using sample temp tables. I am using the following able Valued Function DelimitedSplit8k by @JeffModen

use sqlteam
go

create table #tbl_Land(landID int, LandName varchar(50))

insert into #tbl_Land
select 1 , 'Wild Alas Ka' union
select 2 , 'Womantana' union
select 3 , 'Tim Back Two' union
select 4 , 'Oregon Gorge' union
select 9  , 'Zimzam' 

CREATE TABLE #csv
(
  LandID INT,
  ReferenceID INT,
  Land_TitleID VARCHAR(MAX)
)
INSERT #csv 
SELECT 1, 9, '18,20,22' union
SELECT 2, 8, '17,19' union
SELECT 3, 7, '13,19,20' union
SELECT 4, 6, '' union
SELECT 9, 11, '1,2,3,4'

Select l.LandName, a.Item as TitleID , s.ReferenceID
  from #csv s
    cross apply dbo.DelimitedSplit8k(s.Land_TitleID,',') a
	join #tbl_Land l on s.LandID = l.landID


drop table #csv
drop table #tbl_Land