How do we import the data from table using Stored Procedure

Question

How do we import the data from table using Stored Procedure

BeUnique 2,332

I have two tables (Table-1 and Table-2)

Table-1 data is coming from the jobs which is another source of origin.

Some times the jobs getting failed due to n/w or someother server issues.

so, that time the complete data does not import from the source.

because of this failure, it will be impacting of our application and report.

so we decided to use another table called "Table-2". It will have the same structure of the first table (Table-1).

we don't want to disturb Table-1 always and planning to use Table-2 for app and report purpose.

Here we want to create one stored procedure and this we can make it in the schedule based on the below conitions.

How to create the SP using below conditions.

     1. Before importing data from Table-1, it should check the row count of Table-2.  
 If Row count of Table-2 is equal or greater than Table-1, then import data should be happening. other wise it should not do any import.

How to create the stored procedure like above conditions..?

The Final conclusion is, we can always refer Table-2 with data for apps and reports.

LiHongMSFT-4306 31,576 Reputation points

2022-12-08T02:31:16.373+00:00

Hi @BeUnique
Is this issue solved now? If you have any questions, please feel free to share with us.
And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thanks for your understanding!

Best regards,
Li Hong

2 answers

Your answer

LiHongMSFT-4306 31,576 Reputation points

2022-12-08T02:31:16.373+00:00

Hi @BeUnique
Is this issue solved now? If you have any questions, please feel free to share with us.
And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thanks for your understanding!

Best regards,
Li Hong

Answer 1

LiHongMSFT-4306 31,576

Hi @BeUnique
Don't know what your table like, maybe you could try something like this:

CREATE OR ALTER PROC sp_test  
AS   
DECLARE @RowCount1 INT  
DECLARE @RowCount2 INT  
SELECT @RowCount1=COUNT(*) FROM Table1;  
SELECT @RowCount2=COUNT(*) FROM Table2;  
IF @RowCount1 > @RowCount2  
  ......  
ELSE  
  ......

Best regards,
LiHong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

BeUnique 2,332 Reputation points

2022-12-05T07:42:24.513+00:00
Intially Very first time both table rows count are the same.
i want to make another job schedule (for TABLE-2).
So the data will be there always in Table-2.

Shall i use below code for importing data from TABLE-1 to TABLE-2.

IF @RowCount1 > @RowCount2 //Do export from TABLE-1 to TABLE-2 SELECT * INTO TABLE-2 FROM TABLE-1 WHERE 1=1 ELSE //Do not Export anything here. So TABLE-2 always remains the original data for apps and reports using

Waiting for your reply
LiHongMSFT-4306 31,576 Reputation points

2022-12-05T08:01:54.087+00:00
The SQL Server (Transact-SQL) SELECT INTO statement is used to create a new table from an existing table by copying the existing table's columns.
So, you need to drop TABLE-2 first, then do this: SELECT * INTO TABLE-2 FROM TABLE-1 WHERE 1=1 .
Like this:

IF @RowCount1 > @RowCount2 BEGIN DROP TABLE Table2; SELECT * INTO TABLE-2 FROM TABLE-1 WHERE 1=1; END
BeUnique 2,332 Reputation points

2022-12-05T11:18:38.483+00:00

Thanks for your reply.

Is it possible to move only data in the destination table without dropping the table..?

Do we have any impact if we are going use drop table and then create table..

Because we are refering Table-2 for all the reports and applications.

pls. guide...
LiHongMSFT-4306 31,576 Reputation points

2022-12-06T01:29:17.647+00:00

Hi @BeUnique

Is it possible to move only data in the destination table without dropping the table..?

If Table1 has same table structure with table2, then just INSERT INTO Table2 SELECT * FROM Table1.

If you want to delete the original data in table2 before inserting data into table2, you can Truncate TABLE Table2 first, and then INSERT INTO Table2 SELECT * FROM Table1.

Answer 2

Olaf Helper 47,526

No clear, what your final question is.
Do as you described your process, import first into a stagging table and if everything is fine, exchange the data.
If the table & index design is exact the same, then the easiest & most performant way is to do a partition switch.

Share via

How do we import the data from table using Stored Procedure

2 answers

Your answer