Merging data from excel to sql server

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

General SQL Server Forums

New to SQL Server Programming

Merging data from excel to sql server

Author

Topic

chinna1214
Starting Member

21 Posts

Posted - 2014-12-24 : 06:05:06

Hi,

Is any one working on sql integration with hybris. i was new to this hybris environment previously my job was completly admin part. Now i got a project where i need to fetch data from hybris (Data Hub) and then i need to maintain the same records in sql server with same attributes nd some times we need to add new attributes and tables even.

Please assist me how we can import data from the excel sheet to sql server. For this i have gone through 2 approches wether using merge statement through stored procedure or SSIS package (I dont know whether it works out for the same to merge data in sql server, if it works). Which of the procedure is more efficent.
Note: previously my tasks were completly admin tasks and i dnt have much knowledge about developing part.

So if stored procedures were efficent, please let me know what kind of stored procedures that we can use. For ur info i cannot provide much as i was not completly intlo project. I have just joined and have to see futher.

Your asistance is much appriciated.

Thanks,
Chinna

Harish Amballa

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-12-24 : 06:19:23

You can do it thru SSIS or you can use the BCP command or you can convert/save your sheet as text and bulk insert like below:

BULK INSERT #CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

Select *
From #CSVTest
GO

We are the creators of our own reality!

chinna1214
Starting Member

21 Posts

Posted - 2014-12-24 : 06:49:40

Thanks for the info.

if i use SSIS packages can we schedule jobs for that packages. And the data that we inserting is so huge and after merging the data into SQL we need to check the data integrity even. Can we check the data intigrity while we are inserting data into sql server..

eg: We have a cust table in sql server database and now again we need to merge some data to that customer table. We will create a pakage for that and when merging if at all the current data have some coloumns similar to them in sql db how to it will take. it will just simply insert or SSIS package works similar to that of general merge command. Like it only update if new columns was there else it will leave ri8 like that will ssis package will also do.

Harish Amballa

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-12-24 : 06:57:23

Yes,you can schedule SSIS packages through sql agent.
You may need to use slowly changing dimension in SSIS for delta load i.e to insert the new records and update the changed records.

Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-12-24 : 07:17:50

As Javeed mentioned you can use SCD although this is only available on enterprise editions I believe. There is a check database integrity task under the tools and there are other things you can do to make sure the data is checked such as breakpoints/endpoints. I hard code SQL for my SSIS packages most of the time, so if you have a table that only gets updated depending on other factors from other tables you would want this running after the main tables in its own package, you can also use rollback method in containers where if any package fails it will roll back the transactions, Im assuming you dont truncate and are appending as the rows grow, are you rebuilding indexes or using reorganize to speed up your queries. Any SSIS package can be run via SQL Agent when deployed to a server. You could also look at CDC command change data capture to only update changed records.

We are the creators of our own reality!

chinna1214
Starting Member

21 Posts

Posted - 2014-12-24 : 07:30:03

Thanx for the info.

The fact is that i havent been working on the db now. I was still looking into it for the best method to apply. So in case if i do i may use truncate. If incase i use truncate and the package has not updated can we fetch it through roll back. And rebulding indexes.. Sorry, i dnt evn have knowledge on them.

Harish

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-12-24 : 07:50:44

Dont truncate anything if you are unsure what you are doing and always make sure the backups are in place for the DataMarts in question. You should test the ETL before applying to LIVE, copy the existing DataMart and call it Test at end of name and connect to that thru SSIS, you can then test away without hurting anything until you are happy with the results. Also in SQL script for truncate thru SSIS always double check the connection in the properties and make sure you are not connecting to the LIVE db as oppose to the DataMart. If you are keeping history in the DataMart then you will probably be using surrogate keys (identity keys), if these are not present then its possible that the DataMart is truncated depending on what records are capured. Test, test and test again on test db, stay well away from live until you are happy!!

For rollbacks you need to be looking at Transaction Option in the package/container/task properties, these can be set to supported and required depending on what the needs are to rollback.

For example if one fails they can all fail, this would be important when you are appending a history DataMart for example to make sure the records dont fall out of sync.

We are the creators of our own reality!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources