Solved

Append Records from one sql table to another table

Posted on 2013-01-16
10
471 Views
Last Modified: 2013-02-02
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
Comment
Question by:teogos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
10 Comments
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 38785989
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
 

Author Comment

by:teogos
ID: 38787475
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
 

Author Comment

by:teogos
ID: 38787991
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:teogos
ID: 38788106
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
 

Author Comment

by:teogos
ID: 38789157
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
 
LVL 21

Expert Comment

by:masterpass
ID: 38789712
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38789959
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
 

Author Comment

by:teogos
ID: 38794026
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38794404
There are some links above to show some coding examples.

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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question