April 11, 2018 at 11:40 am
I've been given a spreadsheet with these column headings.
TeamID | ProjectID | Project | Note 1 Date | Note 1 | Note 2 Date | Note 2 | Note 3 Date | Note 3 | Note 4 Date | Note 4 | Note 5 Date | Note 5 ... etc. (goes on up to Note 30)
I created a table called tblProjectNotesImport in my SQL Server 2016 database to receive the data. The columns in the table are named as above.
The table that holds existing project notes - called tblProjectNotes - has these columns:
TeamID, ProjectID, NoteDate, Note
I need to loop through the rows and columns of tblProjectNotesImport and write the data in the columns into rows in tblProjectNotes.
So, in the first row, the first two columns contain the TeamID and ProjectID - so, if I get at this data I then need to write, in pseudo code ...
INSERT INTO tblProjectNotes( TeamID, ProjectID, NoteDate, Note)
VALUES (@TeamID, @ProjectID, value from column [Note 1 Date], value from column [Note 1]
INSERT INTO tblProjectNotes( TeamID, ProjectID, NoteDate, Note)
VALUES (@TeamID, @ProjectID, value from column [Note 2 Date], value from column [Note 2]
INSERT INTO tblProjectNotes( TeamID, ProjectID, NoteDate, Note)
VALUES (@TeamID, @ProjectID, value from column [Note 3 Date], value from column [Note 3]
etc. until I encounter a null vale - at which point I move on to the next row and move across the columns doing an insert for each pair of Note Date / Note fields until a null value is met.
How can I do this?
April 11, 2018 at 11:55 am
webskater - Wednesday, April 11, 2018 11:40 AMI've been given a spreadsheet with these column headings.
TeamID | ProjectID | Project | Note 1 Date | Note 1 | Note 2 Date | Note 2 | Note 3 Date | Note 3 | Note 4 Date | Note 4 | Note 5 Date | Note 5 ... etc. (goes on up to Note 30)
I created a table called tblProjectNotesImport in my SQL Server 2016 database to receive the data. The columns in the table are named as above.
The table that holds existing project notes - called tblProjectNotes - has these columns:
TeamID, ProjectID, NoteDate, Note
I need to loop through the rows and columns of tblProjectNotesImport and write the data in the columns into rows in tblProjectNotes.
So, in the first row, the first two columns contain the TeamID and ProjectID - so, if I get at this data I then need to write, in pseudo code ...
INSERT INTO tblProjectNotes( TeamID, ProjectID, NoteDate, Note)
VALUES (@TeamID, @ProjectID, value from column [Note 1 Date], value from column [Note 1]
INSERT INTO tblProjectNotes( TeamID, ProjectID, NoteDate, Note)
VALUES (@TeamID, @ProjectID, value from column [Note 2 Date], value from column [Note 2]
INSERT INTO tblProjectNotes( TeamID, ProjectID, NoteDate, Note)
VALUES (@TeamID, @ProjectID, value from column [Note 3 Date], value from column [Note 3]
etc. until I encounter a null vale - at which point I move on to the next row and move across the columns doing an insert for each pair of Note Date / Note fields until a null value is met.
How can I do this?
Rather than attempting to do this in one hit, I suggest that you perform a direct import from Excel into a staging table, and from there call a stored proc to transform the data to fit your desired structure.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 12, 2018 at 8:03 am
The only alternative to Phil's suggestion is to learn how to code in either VBA or VBScript, and use the Excel object model to get programmatic access to each cell. If you have the language skill and knowledge of the Excel object model (you can Google that) it's not terribly difficult - just rather time consuming. If it's a one-time thing and you're pressed for time, I'd use Phil's suggestion.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply