Solved

Append Records from one sql table to another table

Posted on 2013-01-16
10
446 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
  • 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
 

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now