[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 134
  • Last Modified:

Copy all rows of

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
SweetingA
Asked:
SweetingA
  • 7
  • 6
1 Solution
 
Jacques Bourgeois (James Burger)Commented:
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
 
SweetingAAuthor Commented:
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
 
SharathData EngineerCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Jacques Bourgeois (James Burger)Commented:
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
 
SweetingAAuthor Commented:
but i would like to trigger it during a vb event like a button press
0
 
Jacques Bourgeois (James Burger)Commented:
		'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
 
SweetingAAuthor Commented:
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
 
Jacques Bourgeois (James Burger)Commented:
'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
 
SweetingAAuthor Commented:
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
 
Jacques Bourgeois (James Burger)Commented:
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
 
SweetingAAuthor Commented:
I use management studio
Microsoft SQL 2008 R2
0
 
SweetingAAuthor Commented:
Seems very straight forward but i get an error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
0
 
Jacques Bourgeois (James Burger)Commented:
Without seeing the SQL, not way to know what the incorrect syntax might be.
0
 
SweetingAAuthor Commented:
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

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.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now