Solved

Copy all rows of

Posted on 2014-02-23
14
123 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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
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 …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 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

21 Experts available now in Live!

Get 1:1 Help Now