Solved

Copy all rows of

Posted on 2014-02-23
14
126 Views
Last Modified: 2014-02-24
I have a table called temptbl_Stops

Columns.............

ID (Primary Key)
StartDate (datetime)
StopDate (datetime)
StopTime (datetime)
DowntimeCode (nchar 10)

I want to copy all data from qry_Stops to this temptbl_Stops, the query contains the columns...

StartDate (datetime)
StopDate (datetime)
StopTime (datetime)
DowntimeCode (nchar 10)

I know how to insert a particular field on a given row but i don't know how to copy all.

I should mention i do not want to copy the table and its structure - only the data.

Thanks in advance
0
Comment
Question by:SweetingA
  • 7
  • 6
14 Comments
 
LVL 40
ID: 39881140
A table in code (DataTable) or a table in a database.

If in a database, which one : SQL Server, Access, Oracle, other.

Is the query in your Visual Basic .NET code or in a database?

What is the code in qry_Stops, if you have it?
0
 

Author Comment

by:SweetingA
ID: 39881159
the table is in sql server 2008

the query is also in sql server 2008

the code in the query is very basic, see attached.....

SELECT     TOP (100) PERCENT StartDate, StopDate, SUM(CAST(DATEDIFF(s, StopDate, StartDate) AS Numeric(4, 0)) / 60) AS StopTime, DowntimeCode
FROM         dbo.tbl_MachineData
GROUP BY StartDate, StopDate, DowntimeCode
HAVING      (SUM(CAST(DATEDIFF(s, StopDate, StartDate) AS Numeric(4, 0)) / 60) IS NOT NULL)
ORDER BY StartDate

Thanks
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39881192
try this.
INSERT INTO temptbl_Stops 
            (StartDate, 
             StopDate, 
             StopTime, 
             DowntimeCode) 
SELECT StartDate, 
       StopDate, 
       SUM(CAST(DATEDIFF(s, StopDate, StartDate) AS NUMERIC(4, 0)) / 60) AS StopTime, 
       DowntimeCode 
  FROM dbo.tbl_MachineData 
 GROUP BY StartDate, 
          StopDate, 
          DowntimeCode 
HAVING ( SUM(CAST(DATEDIFF(s, StopDate, StartDate) AS NUMERIC(4, 0)) / 60) IS NOT NULL ) 
 ORDER BY StartDate 

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 40
ID: 39881197
Hoping that I did not miss something, because I cannot test it having no access to your database, simply add the first line in the following to your query.

INSERT INTO temptbl_Stops (StartDate, StopDate, StopTime, DownTimeCode)
SELECT     TOP (100) PERCENT StartDate, StopDate, SUM(CAST(DATEDIFF(s, StopDate, StartDate) AS Numeric(4, 0)) / 60) AS StopTime, DowntimeCode
FROM         dbo.tbl_MachineData
GROUP BY StartDate, StopDate, DowntimeCode
HAVING      (SUM(CAST(DATEDIFF(s, StopDate, StartDate) AS Numeric(4, 0)) / 60) IS NOT NULL)
ORDER BY StartDate
0
 

Author Comment

by:SweetingA
ID: 39881198
but i would like to trigger it during a vb event like a button press
0
 
LVL 40
ID: 39881201
		'Assuming that the query is already saved as a stored procedure on your server
		Dim con As New System.Data.SqlClient.SqlConnection("ConnectionString to your database")
		Dim cmd As New System.Data.SqlClient.SqlCommand("qry_Stops") 'Or another name
		cmd.CommandType = CommandType.StoredProcedure
		cmd.Connection = con
		con.Open()
		cmd.ExecuteNonQuery()
		con.Close()

Open in new window

0
 

Author Comment

by:SweetingA
ID: 39881206
i also get an error

This SQL statement type cannot be used in a view or function.
Only a SELECT statement can be used.
0
 
LVL 40
ID: 39881214
'Assuming that the query is already saved as a stored procedure on your server


You cannot have an INSERT in a View. It needs to be defined in a Stored Procedure.
0
 

Author Comment

by:SweetingA
ID: 39881221
Hi James,

I'm afraid you will have to be patient with me as i am a complete novice, until 2 weeks ago i have never used SQL or VB.Net.  I normally use MS Access.

Can you give me the idots guide on how to do that please.
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 500 total points
ID: 39881285
What tool do you use to work with SQL Server? Do you do it from the Server Explorer in Visual Studio, or with Management Studio? Tell us also in which version of VS you are, because the interface has changed in Visual Studio in the last few versions.

The method is similar in both, but knowing that can help us help you in future posts. The instructions I give you here are for the Solution Explorer in Visual Studio, it would be slightly different in Management Studio.

Open the tree where you work with your database.

There should be an entry there for Stored Procedures.

Rigth click on it and select the option to add a procedure.

Give a name to your procedure on the CREATE PROCEDURE line at the top, by replacing the Procedure word between brackets by the name you want. This is also the name that you should use when creating the SqlCommand in your VB code.

Remove the 2 param entries, your procedure does not need parameters.

Replace the SELECT with the code provided for the INSERT INTO.

If you have an arrow with Update in top left, click on it to save the procedure on your server. If not, simply Save as you would for a file.

Go for it and test.

------

One important different between Access and SQL Server as far as queries are concerned.

SQL Server does not have queries, although the word is sometimes used for simple queries that only have a SELECT in it.

SQL Server has Views and Stored Procedures.

Views are different ways of seeing the data. They are a lot like the Select queries of Access but are more limited in what they can do.

For more complex Select queries, or when you need a "query" that writes in the database in any way, you use a Stored Procedure. As their name implies, they are procedure, the same way that you have procedures in VBA in Access. You can pass parameters, return values, perform many operations one after the other, do If and Case instructions, and have built-in functions that you can call to help you with format, date manipulations and the likes. The main difference is that they use Transact SQL (a SQL developed specially for SQL Server) instead of VB.

Stored procedures takes the role of Delete, Update and Append queries in Access, but can do a lot more.

A trick that can help you sometimes when you do not know how to do it in SQL Server but know how to do it in Access. Do it in Access, and while in the query designer, select SQL View in the View menu. This will give you the SQL that Access is creating for that query. In some cases, you can copy and paste in SQL Server with very few changes. The results are never optimum, but they help you make the transition while you are learning the SQL language.
0
 

Author Comment

by:SweetingA
ID: 39881805
I use management studio
Microsoft SQL 2008 R2
0
 

Author Comment

by:SweetingA
ID: 39881834
Seems very straight forward but i get an error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
0
 
LVL 40
ID: 39882772
Without seeing the SQL, not way to know what the incorrect syntax might be.
0
 

Author Closing Comment

by:SweetingA
ID: 39883199
All sorted James, thanks a lot for the help.

I couldn't find the stored procedures tab but it was inside programmability.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Creating a route in asp.net webforms 2 32
VB: Convert 2 dates to specific format 24 53
Import Data from Multiple Text Files in Excel 12 61
Write to a printer using vb.net 9 40
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

827 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