How to import data in Sql Server based on Account number

Other SQL Server Topics
1

Hello Experts.

Am new to Sql server database, just started learning tables.
I have a quick question.

How to import raw data from excel into SQL server in differnt tables.

Account Number starting with 1 should go to Table 1
Account Number NOT starting with 1 should go to Table 2

Please advise

Thanking you in Advance.

2

That's actually more of an Excel/VBA question. Have you tried posting on an Excel forum?

3

Hello
Thank u for your response..

I tried but...Got to know this question to be posted in SQL forum.

4

Ha! So you'll need some VBA and some SQL, The VBA is better found in the Excel universe. Once you have that, we can help with the SQL part

5

Thank you!

6

Using SSIS to import the Excel spreadsheet - you would use a conditional split transformation to redirect rows from the source to separate destinations.

In the conditional split - you would define each output rule, something like:

Output1

SUBSTRING(AccountNumber, 1, 1) = 1

Output2

SUBSTRING(AccountNumber, 1, 1) <> 1

In each destination you define which table is used to receive the data.

With that said - working with Excel and SSIS can be problematic because SSIS is a x86 (32-bit) application and if you are using the x64 version of Office you cannot load the Excel driver. If you can export the Excel spreadsheet to a CSV file that is much easier to use in SSIS.

Another option would be to not worry about splitting the data to other tables in the process to upload the data to SQL Server. Upload the data to a staging table - then use T-SQL to insert the data into the appropriate final tables. That would be fairly simple code:

INSERT INTO dbo.AccountsStartingWith1
SELECT * FROM stage.InputTable WHERE AccountNumber Like '1%';

INSERT INTO dbo.AccountsNotStartingWith1
SELECT * FROM stage.InputTable WHERE AccountNumber Not Like '1%';
1 Like
7

Thank you so very much for the Explanation..Let me check and get back to you.