Want to load data on a staging table.

Question

Want to load data on a staging table.

Teewhy

SSC Veteran

Points: 203
More actions
May 8, 2023 at 4:05 pm

#4186172

In the past, I loaded data off excel, and it was difficult to manipulate in SQL. Someone suggested loading the data on a staging area next time to avoid hassles. Now, I have more of such similar data from excel to import to SQL server, and need to load on a staging table. I have tried seeing videos to help but they don't speak to my issue. Could someone help?

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55539 More actions · Answer 1

When I have similar problems, I make a staging table with the same fields as production, but all defined as appropriate length VARCHAR to eliminate (or reduce) data mapping errors. Once the data is in SQL, it's easier to examine the data in the staging table to look for invalid data, such as CHAR data that should be INT, or invalid Dates. Then it can be fixed before pushing to PROD, or go back to the source to find the origin of the bad data. Then fix the original data and re-import.

This reply was modified 11 months, 2 weeks ago by homebrew01.

Teewhy SSC Veteran Points: 203 More actions · Answer 2

Thanks but for a newbie, I don't quite understand.

It would be helpful If you used demonstrative example with codes or processes that would help my cause.

Thank you.

homebrew01 SSC Guru Points: 55539 More actions · Answer 3

You can script your PROD MyTable table as CREATE to a SQL window, change the table name to MyTable_Stage, and change all the data types to VARCHAR(nn) , then run the script to create MyTable_Stage.

Then right-click your database -> tasks -> and choose 1 of the "Import" options to map the data from Excel into your staging table MyTable_Stage

This reply was modified 11 months, 2 weeks ago by homebrew01.
This reply was modified 11 months, 2 weeks ago by homebrew01.

Teewhy SSC Veteran Points: 203 More actions · Answer 4

Whooo!!!! That's what I was looking for! Thank you so much! Very helpful!

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

Teewhy wrote:

Whooo!!!! That's what I was looking for! Thank you so much! Very helpful!

I'm curious... how often will you need to do this from the same spreadsheet? (Obviously, after some modifications/additions to the data in the spreadsheet but still the same spreadsheet).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Teewhy SSC Veteran Points: 203 More actions · Answer 6

Probably a few more times. Is there anything I should be concerned about or something else I can do to improve the process?

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

Teewhy wrote:

Probably a few more times. Is there anything I should be concerned about or something else I can do to improve the process?

I was going to suggest that if it's a nightly or, perhaps, even a weekly process to load the data from the same spreadsheet, you might want to consider automating the process. Some folks will use SSIS for such a thing and that's fine but I try to not even install it. Instead, I use the "ACE Drivers" for such things. There's a bit of a setup on how to do such a thing. It's probably not some to take the time to do for ad hoc loads a "few more times" but, at least you now know, "it's possible". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

homebrew01 SSC Guru Points: 55539 More actions · Answer 8

Bulk Copy "BCP" might be another option. But the import wizard's probably the easiest since it's GUI driven.

This reply was modified 11 months, 2 weeks ago by homebrew01.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

homebrew01 wrote:

Bulk Copy "BCP" might be another option. But the import wizard's probably the easiest since it's GUI driven.

I'm pretty sure that BCP doesn't work on actual spreadsheets ((but I could be wrong there). I believe you'd have to export the spreadsheet to a CSV or TSV for BCP to work.

Of course, if I am wrong about that, I'd love to see the BCP command line that does it because I love learning. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

homebrew01 SSC Guru Points: 55539 More actions · Answer 10

Jeff Moden wrote:

homebrew01 wrote:
Bulk Copy "BCP" might be another option. But the import wizard's probably the easiest since it's GUI driven.
I'm pretty sure that BCP doesn't work on actual spreadsheets ((but I could be wrong there). I believe you'd have to export the spreadsheet to a CSV or TSV for BCP to work.
Of course, if I am wrong about that, I'd love to see the BCP command line that does it because I love learning. 😀

If it's an occasional manual process, the XLSX could be "SAVE AS" a CSV. If that works, it might be easier to re-run BCP using the saved code, rather than step through the Import Wizard again.

Also, the Import Wizard process can be saved as an SSIS package at the end to be used again later.

Disclaimer: I don't do much in MSSQL these days so I may be off base somewhat.

This reply was modified 11 months, 2 weeks ago by homebrew01.

David Burrows SSC Guru Points: 65138 More actions · Answer 11

Jeff Moden wrote:

homebrew01 wrote:
Bulk Copy "BCP" might be another option. But the import wizard's probably the easiest since it's GUI driven.
I'm pretty sure that BCP doesn't work on actual spreadsheets ((but I could be wrong there). I believe you'd have to export the spreadsheet to a CSV or TSV for BCP to work.
Of course, if I am wrong about that, I'd love to see the BCP command line that does it because I love learning. 😀

How about a using a DSN

Far away is close at hand in the images of elsewhere.
Anon.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 12

David Burrows wrote:

Jeff Moden wrote:
homebrew01 wrote:
Bulk Copy "BCP" might be another option. But the import wizard's probably the easiest since it's GUI driven.
I'm pretty sure that BCP doesn't work on actual spreadsheets ((but I could be wrong there). I believe you'd have to export the spreadsheet to a CSV or TSV for BCP to work.
Of course, if I am wrong about that, I'd love to see the BCP command line that does it because I love learning. 😀
How about a using a DSN

I don't know. I've never done such a thing to import a spreadsheet. It's almost always been the ACE Drivers for me (and continues to be so).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Burrows SSC Guru Points: 65138 More actions · Answer 13

Jeff Moden wrote:

David Burrows wrote:
Jeff Moden wrote:
homebrew01 wrote:
Bulk Copy "BCP" might be another option. But the import wizard's probably the easiest since it's GUI driven.
I'm pretty sure that BCP doesn't work on actual spreadsheets ((but I could be wrong there). I believe you'd have to export the spreadsheet to a CSV or TSV for BCP to work.
Of course, if I am wrong about that, I'd love to see the BCP command line that does it because I love learning. 😀
How about a using a DSN
I don't know. I've never done such a thing to import a spreadsheet. It's almost always been the ACE Drivers for me (and continues to be so).

Me neither but I thought a DSN using ACE driver could be used by bcp to import, but seems a long winded way to achieve the result but as your mantra states “it depends” 😀

Far away is close at hand in the images of elsewhere.
Anon.

Teewhy SSC Veteran Points: 203 More actions · Answer 14

I will want to have this solution in my arsenal. What is the code to install ACE Drivers? How do I set it up?

Please, show me easy and step by step. I'm a newbie.