Solved

Copy all rows of

Posted on 2014-02-23
14
125 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL syntax in VB.net 5 39
Get hold of longitude and latitude in iframe string 11 46
Close form "before" open 3 40
Call windows 10 virtual keyboard from windows forms app 2 36
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

809 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