[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 548
  • Last Modified:

Append Records from one sql table to another table

I have a Sql table1 that every Monday, I want to append all the records of table1  into table2 which contains the same fields and data types there about 500 records total, but I want to append not overwrite  plus adding the DATE for the monday that this action take place on all the records on table2,   I want to do this on a windows form VB.net 2008 visual basic, with the click of the bottom, is this posible.   maybe writing a query or store procedure on the sql but getting initialize by this CLICK BOTTOM on the windows form
0
teogos
Asked:
teogos
  • 5
  • 3
1 Solution
 
Mark WillsTopic AdvisorCommented:
To answer your question, yes it is possible, and I would suggest a Stored Procedure (SP) on the SQL Server...

A couple of questions for you:

1) What would stop anyone clicking on any other day
2) Should the SP check to make sure it is only a Monday ?
3) What happens to the records in table1 as a result - what is to stop them being added again
4) Do you want to check to see if that table1 row already exists in table2 regardless of when it was added ?
5) Are you OK with either the SQL part or the VB part ?

The SQL part could be something like :

create procedure usp_xfr_table1_table2
as
begin

   declare @force_2_run int = 1  -- this is a debug switch could be set as a param 

   if @force_2_run = 0 and datename(dw,getdate()) <> 'Monday' 
   begin
      print 'Incorrect day or parameter not "forcing". Doing a return at this stage'
	  return
   end

   insert table2 (col1,col2,col3,date_added)
   select col1,col2,col3,getdate()
   from table1

end

-- then you need to execute the query - which is what the button would do

exec usp_xfr_table1_table2

Open in new window

0
 
teogosAuthor Commented:
Ok, a little more explanation for Table1,  The records on this table are not increasing but the data on this records it changes every day of the week, so on Monday I want to take a snapshot of this records along with their values into table2, to build a historical week after week of the data on this records. So on Monday  once I copy the records to table2  I can said the values on TABLE2 are week values, so on the future I will generate reports to compare the values of week1  week2 week3  etc..  that is why I want to append not overwrite
0
 
teogosAuthor Commented:
This is what I have on the store procedure, it executes with not errors, but not records are being transfer to table  maininventoryhistorical


ALTER procedure [3320Monitor].[usp_xfr_packinventory_maininventoryhistorical]
as
begin

   declare @force_2_run int = 1  -- this is a debug switch could be set as a param

   if @force_2_run = 0 and datename(dw,getdate()) <> 'Thursday'
   begin
      print 'Incorrect day or parameter not "forcing". Doing a return at this stage'
        return
   end

   insert maininventoryhistorical (NUMBER,INVENTORY,COUNT,ENDWEEK)
   select NUMBER,INVENTORY,COUNT,getdate()
   from packinventory
END
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
teogosAuthor Commented:
ok, This SP

ALTER procedure [3320Monitor].[usp_xfr_packinventory_maininventoryhistorical]
as
begin

   declare @force_2_run int = 1  -- this is a debug switch could be set as a param

   if @force_2_run = 0 and datename(dw,getdate()) <> 'Thursday'
   begin
      print 'Incorrect day or parameter not "forcing". Doing a return at this stage'
        return
   end

   insert maininventoryhistorical (NUMBER,INVENTORY,COUNT,DESCRIPTION,GETDATE()
   select NUMBER,INVENTORY,COUNT,DESCRIPTION,GETDATE()
   from packinventory
END



When I eliminate the GETDATE() on Both, it transfer records, but I need to have a date on the table mainhistoricalinventory. If I put the GETDATE()  it gaves me incorrect sintax "("
0
 
teogosAuthor Commented:
I got the store procedure to work great,    now I need help on the sintax to execute it from the CLICK Bottom, some code examples   I am using VB.NET  2008 Visual basic
0
 
masterpassCommented:
0
 
Mark WillsTopic AdvisorCommented:
masterpass has posted a good reference - you would need to jump down to step 4 (and bypass the creation of the procedure). Small difference is that the results are being bound to a grid to view the results.

You pretty much just need to execute the procedure...

Also worth having a read of : http://www.codeguru.com/vb/gen/vb_database/storedprocedures/article.php/c5155/Programming-with-Stored-Procedures-in-Visual-Basic-NET-Part-2.htm

And note you could just end at the ExecuteNonQuery()

Or, if you wanted to put in a couple of select statement so you can see the results of what happened (instead of the print).

I see you have figured out your stored procedure... You cannot have getdate() as a destination column, it should be the column name in the table, and it is populated by using getdate() in the select part (like the first one you did)

While you are testing, change the value of @force_2_run to be a 1 and it will run regardless of day. You could also set up @force_2_run as a parameter instead of a declared variable and pass a value of eith zero (which means it will check the day) or any other value to bypass checking the day.

e.g.

ALTER procedure [3320Monitor].[usp_xfr_packinventory_maininventoryhistorical] (@force_2_run int=0) -- having =0 means it is an optional param 
as
begin

   --declare @force_2_run int = 1  -- this is a debug switch could be set as a param 

   set nocount on     -- suppress those extra row count messages from the insert

   if @force_2_run = 0 and datename(dw,getdate()) <> 'Monday'    -- use the param to override day checking
   begin
      select 'Incorrect day or parameter not "forcing". Doing a return at this stage' as results
      return
   end

   insert maininventoryhistorical (NUMBER,INVENTORY,COUNT,ENDWEEK)
   select NUMBER,INVENTORY,COUNT,getdate()
   from packinventory

   select 'Finished transferring ' + left(@@rowcount,8) + ' rows.' as results

END 

-- than execute it like 

exec [3320Monitor].[usp_xfr_packinventory_maininventoryhistorical] 1

-- or

exec [3320Monitor].[usp_xfr_packinventory_maininventoryhistorical]

-- then you could process the rows being returned to display the "results"

Open in new window


You could also set up a schedule for the stored procedure to be run all within SQL Server so it will run every monday automatically

There are a couple of "how to" using SSMS and SQL Server Agent Jobs : http://msdn.microsoft.com/en-us/library/ms190268(v=sql.105).aspx then look at the links down the page and you will fin T-SQL jobs and how to schedule ie : http://msdn.microsoft.com/en-us/library/ms187910(v=sql.105).aspx and http://msdn.microsoft.com/en-us/library/ms191439(v=sql.105).aspx
0
 
teogosAuthor Commented:
Thanks for the comments, But the choice to schedule that on the SLQ is not viable since the time will vary every monday, Also maybe the question was  a little confusing,   Now that I have the store procedure,  I want to execute this on a windows form CLICK bottom, but I have not idea where to start coding this, some examples of coding will helpful
0
 
Mark WillsTopic AdvisorCommented:
There are some links above to show some coding examples.

The "schedule" was an after thought...
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now