Orders
ProductPrice ProductQuantity TotalCost
-- --
￡2.50 2
￡1.75 3
￡12.99 2
Can anyone please help me?You don't need a trigger, you need a computed column:
CREATE TABLE [dbo].[xxx](
[col_a] [int] NOT NULL default(2),
[col_b] [int] NOT NULL default(2),
[col_axb] as a * b,
) ON [PRIMARY]
GO|||Thank you for the help!
Using Sql Server 2005 Express and Management Studio I need to createa SQL insert statement for the contents of a table (FullDocuments) sothat I can run the query on another server with that same table schema(FullDocuments) and the contents will automatically be inserted intothe new instance of the FullDocuments table.
In Management StudioI have used "Script Table as" for the create table query. Thesecond instance of FullDocuments has been created on the remoteserver. Now how do I generate an insert query for the contents ofFullDocuments so that the contents can be moved/inserted to the newinstance of the table?
Thanks for any help provided.
Why not just do something like :
insert into fulldocuments2
select * from server1.fulldocuments1
|||Hi Partha. Thanks for the suggestion.
The problem is that the fulldocuments2 is on a web server whilefulldocuments1 is on my local machine which does not allow me to uploada table or db directly. I need to transfer the data by SQL querybut don't how to generate a sql insert statement that contains all thedata from fulldocuments1 without manually typing it. For smalldata inserts I can do that. But this time it involves thousandsof lines of text, so I am hoping for a simple automated way to createthe sql insert statement to move the data.
|||Just export the data in fulldocuments1 to a flat file and import into fulldocuments2 using Export Import wizard
|||I can't find the Export Import Wizard. I am using the Express version of SQL Server 2005 and Management Studio.
|||use bcp to copy out the data and copy it back in the web server
http://msdn2.microsoft.com/en-us/library/ms162802.aspx
|||The web server admin to which i need to import data only allows dataimport via Query Analyzer (hence sql insert statement) or via CSVfile. Since I am having no luck generating a sql insert statementthat contains the data, it looks like I need to focus on how to createa CSV file with the data content. I have been studying the BCPdocumentation from your link, but don't see any means for generating aCSV file using BCP.
What are my options for creating a CSV file containing the data from the source table?
See this link for an example of bcp to create csv file
http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/
|||Consider writing a very short app (20 lines or so) using SqlBulkCopy. It's incredibly easy and incredibly fast.
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx
http://davidhayden.com/blog/dave/archive/2006/01/13/2692.aspx
|||Hi Buddha. Thanks for the links. The first one uses VB so I have tried that for my app. I like theconcept of uploading directly to the web server db but am getting thiserror message when I click the button to upload the data:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Iam a novice at this and realize I am bumping up against a securityissue, but don't know how to solve it. Any help would beappreciated. Here is the VB code for my app:
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page
Dim connectionString1 As String = "Data Source=_x_connection string to local db_x_"
Dim connectionString2 As String = "Data Source=_x_connection string to web server db_x_"
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
' Open a connection to the source database.
Using sourceConnection As SqlConnection = _
New SqlConnection(connectionString1)
sourceConnection.Open()
' Perform an initial count on the destination table.
Dim commandRowCount As New SqlCommand( _
"SELECT COUNT(*) FROM dbo.Category;", _
sourceConnection)
Dim countStart As Long = _
System.Convert.ToInt32(commandRowCount.ExecuteScalar())
Console.WriteLine("Starting row count = {0}", countStart)
' Get data from the source table as a SqlDataReader.
Dim commandSourceData As SqlCommand = New SqlCommand( _
"SELECT * " & _
"FROM Category;", sourceConnection)
Dim reader As SqlDataReader = commandSourceData.ExecuteReader
' Open the destination connection.
Using destinationConnection As SqlConnection = _
New SqlConnection(connectionString2)
destinationConnection.Open()
' Set up the bulk copy object.
' The column positions in the source data reader
' match the column positions in the destination table,
' so there is no need to map columns.
Using bulkCopy As SqlBulkCopy = _
New SqlBulkCopy(destinationConnection)
bulkCopy.DestinationTableName = _
"Category"
Try
' Write from the source to the destination.
bulkCopy.WriteToServer(reader)
Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
' Close the SqlDataReader. The SqlBulkCopy
' object is automatically closed at the end
' of the Using block.
reader.Close()
End Try
End Using
' Perform a final count on the destination table
' to see how many rows were added.
Dim countEnd As Long = _
System.Convert.ToInt32(commandRowCount.ExecuteScalar())
Console.WriteLine("Ending row count = {0}", countEnd)
Console.WriteLine("{0} rows were added.", countEnd - countStart)
Console.WriteLine("Press Enter to finish.")
Console.ReadLine()
End Using
End Using
End Sub
End Class
Although I would have liked to have gotten the app approach to work so that I could learn more, the practical solution to my problem is the free Database Publishing Wizard provided by Microsoft. Amongst the various options it provides is one that creates a sql file with complete insert query for all the data in the db, etc. Thanks for the input which has gotten me thinking about new possibilities for future projects.
Like the subject says, I'm using strongly typed datasets. I'm using to designer to create the datasets and the methods. I can select and insert, but I can't update or delete.
I right click on the adapter bar and select Add Query.
I sleect 'Use SQL Statements'
I select 'Update' (or 'Delete')
I get a sql statement pane containing the word 'Update' ('Delete') and asking 'What data should the table load?'
I can click on next, but anything else gives me errors. I'd list them, but I'm clearly doing something wrong and it's probably obvious.
Diane
Have you made sure that you have set a primary key for your table.|||The table didn't have a primary key. I added one, but I still don't know how I'm supposed to use update and delete in a recordset.
Diane
Hi Diane,
You can remove and re-add the TableAdapter. Set the SELECT command to select from that table which has primary key.
When you walk to the end of the wizard, it will show you that Update and Delete methods are generated. In this case, you will be able to Update and Delete through the TableAdapter.
|||Update and Delete have been generated, I just don't know how to use them.
Diane
|||Hi Diane,
When you have generated the Update and Delete methods, you can call them directly from an instance of TableADapter.
The Update method has several overloads. Some take a DataRow or a DataSet. They can update rows in batch. There is another overload that takes each field value seperately.
The Delete method takes arguments for all the field values, and it will check then delete the row.
HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!
Hi all,
well i want to have an web-based database application in which in an single webform , i need to insert the values to 5 different tables.
here empid is primary key (PK) in one table and in the rest of the tables its an Foreign Key(FK)
my question is, how can i create these tables with composite key?
ie, i want to creat an table which will have FK and as well another key which both will uniquely idenitfies a row.
Well i am using SQL server 2000 and i wish to create these tables using the enterprise manager.
here are the tables
1) empidtable
empid,fname,lname
2)empcountrytable
empid,country
3)empvisatable
empid,visa
4)empdoctable
empid,doc
5)empfile
empid,filename,filecontenttype,filesize,filedata
Plz do help me
thanx in advance
If you're using Enterprise Manager, go to Design View of the table anduse control + click to select all the columns you want as part of thecomposite key, then right-click Set Primary Key.
Hey thanks for that.
will try to implement the same
i want to thank the people who answered my previous thread..
ii want to know how to creat a stored procedure with insert commands that need to be call from a servlet using callableStatement..
i want to know how to create the stored procedure in sql..
i have two tables login_details(username,password), personal_details(Name,Age,dob)..
With advanced thanks..
Ur's
Rams
Quote:
Originally Posted by subramanian_pks
hi.. guys...
i want to thank the people who answered my previous thread..
ii want to know how to creat a stored procedure with insert commands that need to be call from a servlet using callableStatement..
i want to know how to create the stored procedure in sql..
i have two tables login_details(username,password), personal_details(Name,Age,dob)..
With advanced thanks..
Ur's
Rams
