?
Solved

Copy all rows of

Posted on 2014-02-23
14
Medium Priority
?
129 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
[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
  • 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 41

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 2000 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

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!

Question has a verified solution.

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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

764 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