Processing data from source to destination table

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

Processing data from source to destination table

Author

Topic

pravin.pawar
Starting Member

6 Posts

Posted - 2013-09-22 : 10:44:14

Hi,

I want to process data from source table to destination table without using cursor.

At this point; we are creating temp table and inserting data from source to temp table. once we get data into temp table; using while loop we are processing record one by one to destination table.

while executing stored procedure; we noticed that there are few records in source table which are invalid and stored procedure is terminating from such records.

Please suggest us better approach to log INVALID data and resume code to process next record instead of terminating

Regards, Pravin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-22 : 10:53:07

Why not go for set based alternative?
Since you've data in temporary table you can apply set based logic to capture the invalid rows and move them to some error log table after deleting from main table. Then do the data processing for valid rows.
If you can specify your specific validation logic I'll try to help you out.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

pravin.pawar
Starting Member

6 Posts

Posted - 2013-09-22 : 11:14:18

Thanks for your quick reply. Like I said before; we are working on data migration project and pushing data into source table from text files using some tools.

Both source and destination tables are available in SQL Server and we have written stored which will fetch records from source table and insert records one by one in destination table.

while processing records one by one; stored procedure is terminating at record which contain invalid datetime which is of type varchar in source table.

we have to log such records who has invalid data and continue with next record to process instead of terminating execution.

Please let me know how should I log invalid record and continue with processing other records instead of terminating my loop of execution.

Your help is appreciated.

quote:
Originally posted by visakh16

Why not go for set based alternative?
Since you've data in temporary table you can apply set based logic to capture the invalid rows and move them to some error log table after deleting from main table. Then do the data processing for valid rows.
If you can specify your specific validation logic I'll try to help you out.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Regards, Pravin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-22 : 11:37:20

I repeat..Specify the validation logic and I'll show you how to do set based logic for that
You dont need to do any kind of row by row processing

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-09-22 : 12:03:03

1 You should always store datetime values using DATETIME datatype
2 What is the format that datetime values are stored on varchar column?

Madhivanan

Failing to plan is Planning to fail

pravin.pawar
Starting Member

6 Posts

Posted - 2013-09-22 : 13:26:27

There is NO any validation logic. It's just mapping of columns from source table to destination tables.

Single row of source database table is mapped with insert on multiple tables. I mean data from source database table is mapped with multiple tables in destination database.

Thanks, Pravin

quote:
Originally posted by visakh16

I repeat..Specify the validation logic and I'll show you how to do set based logic for that
You dont need to do any kind of row by row processing

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Regards, Pravin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-22 : 13:45:09

quote:
Originally posted by pravin.pawar

There is NO any validation logic. It's just mapping of columns from source table to destination tables.

Single row of source database table is mapped with insert on multiple tables. I mean data from source database table is mapped with multiple tables in destination database.

Thanks, Pravin

quote:
Originally posted by visakh16

I repeat..Specify the validation logic and I'll show you how to do set based logic for that
You dont need to do any kind of row by row processing

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Regards, Pravin

then why do you need to do this row by row?
why cant you use


INSERT..SELECT

statement?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-09-22 : 23:35:33

i think you need this
http://www.sqlteam.com/article/handling-sql-server-errors

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources