SQL insert with select and specify value for a field
Hi,
Can someone tell me if it is possible to do an SQL insert with a select (to copy specific records) query and specify the value for a specific field to insert in the new records instead of using the value in the field in the select statement.
If so can you provide me with a simple example.
Cheers
Mark :)
|
0 |
|
markbpriv
10/24/2007 8:53:57 AM
asp.net.sql-datasource
29906 articles. 
0 followers.
4 Replies
487 Views
Please descript what you want to accomplish.
"Object reference not set to an instance of an object"
|
|
0 |
|
che3358
10/24/2007 12:40:23 PM
Hi,
Don't worry I figured it out.
If I use a stored procedure I can just create a varaible e.g
strFileId intand then do this
insert into mytable (field1,field2,field3)
select from mysecondtable (field1,@strFileId,field3)
I believe if I don't use a stored procedure I could just put the value in. If the value was a string I just need to put it in quotation marks.
insert into mytable (field1,field2,field3)
select from mysecondtable (field1,1,field3)
Is this correct?
|
|
0 |
|
markbpriv
10/24/2007 1:21:34 PM
markbpriv:
insert into mytable (field1,field2,field3)
select from mysecondtable (field1,1,field3)
In addition to this approach, the way this works is that you can specify either static values or column names ( if you are selecting records from a table ). You can even use union all to specify more than one static records like this:
insert into <table name> ( col1 , col2 , ... , colN ) select value1 , value2 , ... , valueN union all select value1 , value2 , ... , valueN union all select value1 , value2 , ... , valueNIn short whatever set of records your select query(ies) return will be directly inserted in the table.
Hope this will help.
Thanks,
Dhimant Trivedi
"When the going gets tough, tough gets going."
"Mark as Answer" the post(s) which helped you solve the problem
|
|
0 |
|
dhimant
10/24/2007 1:46:59 PM
That is absolutely correct.
"Object reference not set to an instance of an object"
|
|
0 |
|
che3358
10/24/2007 1:48:59 PM
Similar Artilces:
linking a sql field to point to the value of another sql field
is it possible to create hyperlinks in powerbuilder such that if you retrieve two fields from a database.. and want to display the first field on the screen and have it link to the value of the other field (when clicked) which is actually a URL address, you can? any help will be appreciated. thank you! ...
Variable Insert to SQL server insert satement setting values for the @variable INSIDE sql
ok, I am on Day 2 of being brain dead.I have a database with a table with 2 varchar(25) columns I have a btton click event that gets the value of the userName, and a text box.I NEED to insert a new row in a sql database, with the 2 variables.Ive used a sqldatasource object, and tried to midify the insert parameters, tried to set it at the button click event, and NOTHING is working. Anyone have a good source for sql 101/ASP.Net/Braindead where I can find this out, or better yet, give me an example. this is what I got <%@ Page Language="C#" %><!DOCTYPE html ...
FormView SQLDataSource Insert
FormView SQLDataSource Insert - How to pass parameter to Insert SQL field value I am trying to pass value(s) to the Insert SQL. The id_company for example. These values come from Session Variables. INSERT INTO dbo.SetupRoom(id_company, id_property, date_schedule,) VALUES (@id_company, @id_property, @date_schedule) I tried to pass them in the Insert Parameters with default value but did not work: <InsertParameters> <asp:Parameter Type="Decimal" Name="id_company" DefaultValue="Session("IdCompany").ToString></asp:Parameter> ...
SQL Server insert query
When I am inserting into a table, I have some values which I am finding are "N/A" which obviously come from a source which allows it ie nvarchar column) I need to check the values before entering into the column, and if it is not an integer, converting it to the value '00000000' ie a similar thing would be isnull(columnname,00000000), but in this case, not checking for if it is null, but rather if it is an integer. Does anyone have any ideas? Take a look at http://berezniker.com/content/pages/sql/microsoft-sql-server/isnumericex-udf-data-type-awareBeware of ...
How do I get the Identity field value after a sql insert so the value can be included in the email sent upon submit?
I have a form that gathers a bunch of data, inserts into a sql database via a stored procedure, and then emails a user some details. How can I include the value of the unique identifier for the row just inserted into sql in the email? Here's my code behind.. and I don't know how/where to query the db to get the value and insert it... Protected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.Click InsertConflictSearch.InsertParameters("SearchStatusID").DefaultValue = "1"InsertConflictSearch.Insert...
Insert SQL on Sql Command
i'm using an sql command for insert query it looks like this cmd=new sqlcommand("insert into tblname values('" & textbox1.text & "')",cn) cmd.executereader the problem is if i type a singlequote or double quote an error is throwing,invalid syntax .... how can i s'rt out it with out using a sql stored procedure thanks in advance PrasantHI think therefore i'mvPras© Hi, try replacing the single quotes present in your input with double single quotes ... example below: string sqlInput = textbox1.text.Replace("'","''...
HOW DO I: Insert nulls into SQL dateTime field stored in SQL DB from a web UI textbox
I’m looking for feedback on the Best/Right way to Insert nulls into SQL dateTime field in SQL DB from a web UI textbox. Option 1: Presently implemented: Dim dtFollowUpDate = IIf(dtDateFollowUp.Text = "", System.Data.SqlTypes.SqlDateTime.Null, dtDateFollowUp.Text) Although ithis does what is needed it generates the following inner exception ParamValue {System.Data.SqlTypes.SqlDateTime} Object[System.Data.SqlTypes.SqlDateTime] {System.Data.SqlTypes.SqlDateTime} ...
Problem inserting sql query into database float datatype field using SQL Transaction
I have this problem of inserting my query into database field. My code is as of below. The @AVERAGESCORE parameter is derived from Dim averagescore As Single = (122 * 1 + 159 * 2 + 18 * 3 + 3 * 4 + 0 * 5) / (122 + 159 + 18 + 3 + 0) and the value returned is (averagescore.toString("0.00")) However, I have error inserting the averagescore variable into a field of datatype float during the transaction. I have no problems when using non transactional sql insert methods. What could be the problem? Try Dim i As Integer ...
Inserting values from textbox (string) into a sql numeric field
Dim MyCommand As SqlCommand Dim InsertCmd As String = "INSERT into artigianato (art_denominazione, art_phone) VALUES (@art_denominazione, @art_phone)" MyCommand = New SqlCommand(InsertCmd, MyConnection) MyCommand.Parameters.Add(New SqlParameter("@art_denominazione", SqlDbType.VarChar)) MyCommand.Parameters("@art_denominazione").Value = denominazione.Text MyCommand.Paramet...
How to insert dropdownlist selected value into sql DB table
Normal 0 false false false MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; ...
select cheboxlist if any values are selected in sql
hello and thanks in advance, I have a checkboxlist that is created dynamically from table1 once the records are inserted they are inserted in table2. i can save the values into the table and remove them if i uncheckd them that is working fine. the problem is to checked the items that have been selected already, how to i do this?? really really confuse... here is the cs code; namespace IMS_2K9.Admin { public partial class Order_Charges : System.Web.UI.Page {private string connectionString = WebConfigurationManager.ConnectionStrings["IMSCon...
Inserting a checkbox value into bit field sql server 2000
Edited by SomeNewKid. Please post code between <code> and </code> tags. This is probaly the easiest question you've ever read but here goes. I have a simple checkbox value that i want to insert into the database but whatever i do it does not seem to let me. Here is my code: Sub AddSection_Click(Sender As Object, e As EventArgs) Dim myCommand As SqlCommand Dim insertCmd As String ' Build a SQL INSERT statement string for all the input-form ' field values. insertCmd = "insert into Customers values (@SectionName, ...
Select three fields in SQL datasource but only display two in datalist
How would I do this? I have to select the First and last name to show up in the datalist, but I also need to select the key because I am updating another column in the selected row. How would I display only the first and last name? You can edit the html in the aspx page and remove that item from the templates.BrucePlease remember to click "Mark as Answer" on the posts that helped solve your issue. The problem is the template doesnt know the name yet because the sql select is selecting from a "' + table + '" so that wouldnt work. Sorry I forgo...
Adding a default value to sql select parameters in vb.net
Hi, I know it's easy to select the default value if you use the sql data source object to configure your query and feed it the select parameters. However, i will like to know if it's possible or what's is the syntax to add a default value to sql query being executed before page is post back. Below is what i have so far: the text strikethrought is the missing piece i need help making it the default value.Dim connect As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("Solution_DA_LiveConnectionString").ToString()) Dim sqlcom...
is it possible to create hyperlinks in powerbuilder such that if you retrieve two fields from a database.. and want to display the first field on the screen and have it link to the value of the other field (when clicked) which is actually a URL address, you can? any help will be appreciated. thank you! ...
Variable Insert to SQL server insert satement setting values for the @variable INSIDE sql
ok, I am on Day 2 of being brain dead.I have a database with a table with 2 varchar(25) columns I have a btton click event that gets the value of the userName, and a text box.I NEED to insert a new row in a sql database, with the 2 variables.Ive used a sqldatasource object, and tried to midify the insert parameters, tried to set it at the button click event, and NOTHING is working. Anyone have a good source for sql 101/ASP.Net/Braindead where I can find this out, or better yet, give me an example. this is what I got <%@ Page Language="C#" %><!DOCTYPE html ...
FormView SQLDataSource Insert
FormView SQLDataSource Insert - How to pass parameter to Insert SQL field value I am trying to pass value(s) to the Insert SQL. The id_company for example. These values come from Session Variables. INSERT INTO dbo.SetupRoom(id_company, id_property, date_schedule,) VALUES (@id_company, @id_property, @date_schedule) I tried to pass them in the Insert Parameters with default value but did not work: <InsertParameters> <asp:Parameter Type="Decimal" Name="id_company" DefaultValue="Session("IdCompany").ToString></asp:Parameter> ...
SQL Server insert query
When I am inserting into a table, I have some values which I am finding are "N/A" which obviously come from a source which allows it ie nvarchar column) I need to check the values before entering into the column, and if it is not an integer, converting it to the value '00000000' ie a similar thing would be isnull(columnname,00000000), but in this case, not checking for if it is null, but rather if it is an integer. Does anyone have any ideas? Take a look at http://berezniker.com/content/pages/sql/microsoft-sql-server/isnumericex-udf-data-type-awareBeware of ...
How do I get the Identity field value after a sql insert so the value can be included in the email sent upon submit?
I have a form that gathers a bunch of data, inserts into a sql database via a stored procedure, and then emails a user some details. How can I include the value of the unique identifier for the row just inserted into sql in the email? Here's my code behind.. and I don't know how/where to query the db to get the value and insert it... Protected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.Click InsertConflictSearch.InsertParameters("SearchStatusID").DefaultValue = "1"InsertConflictSearch.Insert...
Insert SQL on Sql Command
i'm using an sql command for insert query it looks like this cmd=new sqlcommand("insert into tblname values('" & textbox1.text & "')",cn) cmd.executereader the problem is if i type a singlequote or double quote an error is throwing,invalid syntax .... how can i s'rt out it with out using a sql stored procedure thanks in advance PrasantHI think therefore i'mvPras© Hi, try replacing the single quotes present in your input with double single quotes ... example below: string sqlInput = textbox1.text.Replace("'","''...
HOW DO I: Insert nulls into SQL dateTime field stored in SQL DB from a web UI textbox
I’m looking for feedback on the Best/Right way to Insert nulls into SQL dateTime field in SQL DB from a web UI textbox. Option 1: Presently implemented: Dim dtFollowUpDate = IIf(dtDateFollowUp.Text = "", System.Data.SqlTypes.SqlDateTime.Null, dtDateFollowUp.Text) Although ithis does what is needed it generates the following inner exception ParamValue {System.Data.SqlTypes.SqlDateTime} Object[System.Data.SqlTypes.SqlDateTime] {System.Data.SqlTypes.SqlDateTime} ...
Problem inserting sql query into database float datatype field using SQL Transaction
I have this problem of inserting my query into database field. My code is as of below. The @AVERAGESCORE parameter is derived from Dim averagescore As Single = (122 * 1 + 159 * 2 + 18 * 3 + 3 * 4 + 0 * 5) / (122 + 159 + 18 + 3 + 0) and the value returned is (averagescore.toString("0.00")) However, I have error inserting the averagescore variable into a field of datatype float during the transaction. I have no problems when using non transactional sql insert methods. What could be the problem? Try Dim i As Integer ...
Inserting values from textbox (string) into a sql numeric field
Dim MyCommand As SqlCommand Dim InsertCmd As String = "INSERT into artigianato (art_denominazione, art_phone) VALUES (@art_denominazione, @art_phone)" MyCommand = New SqlCommand(InsertCmd, MyConnection) MyCommand.Parameters.Add(New SqlParameter("@art_denominazione", SqlDbType.VarChar)) MyCommand.Parameters("@art_denominazione").Value = denominazione.Text MyCommand.Paramet...
How to insert dropdownlist selected value into sql DB table
Normal 0 false false false MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; ...
select cheboxlist if any values are selected in sql
hello and thanks in advance, I have a checkboxlist that is created dynamically from table1 once the records are inserted they are inserted in table2. i can save the values into the table and remove them if i uncheckd them that is working fine. the problem is to checked the items that have been selected already, how to i do this?? really really confuse... here is the cs code; namespace IMS_2K9.Admin { public partial class Order_Charges : System.Web.UI.Page {private string connectionString = WebConfigurationManager.ConnectionStrings["IMSCon...
Inserting a checkbox value into bit field sql server 2000
Edited by SomeNewKid. Please post code between <code> and </code> tags. This is probaly the easiest question you've ever read but here goes. I have a simple checkbox value that i want to insert into the database but whatever i do it does not seem to let me. Here is my code: Sub AddSection_Click(Sender As Object, e As EventArgs) Dim myCommand As SqlCommand Dim insertCmd As String ' Build a SQL INSERT statement string for all the input-form ' field values. insertCmd = "insert into Customers values (@SectionName, ...
Select three fields in SQL datasource but only display two in datalist
How would I do this? I have to select the First and last name to show up in the datalist, but I also need to select the key because I am updating another column in the selected row. How would I display only the first and last name? You can edit the html in the aspx page and remove that item from the templates.BrucePlease remember to click "Mark as Answer" on the posts that helped solve your issue. The problem is the template doesnt know the name yet because the sql select is selecting from a "' + table + '" so that wouldnt work. Sorry I forgo...
Adding a default value to sql select parameters in vb.net
Hi, I know it's easy to select the default value if you use the sql data source object to configure your query and feed it the select parameters. However, i will like to know if it's possible or what's is the syntax to add a default value to sql query being executed before page is post back. Below is what i have so far: the text strikethrought is the missing piece i need help making it the default value.Dim connect As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("Solution_DA_LiveConnectionString").ToString()) Dim sqlcom...
Web resources about - SQL insert with select and specify value for a field - asp.net.sql-datasource
First picture of terrorist Tashfeen Malik who pledged allegiance to ISIS before killing 14 in party attack ...Tashfeen Malik, 27, wore a burka and didn’t speak to male relatives, Syed Farook's family members said, adding that they had never seen her face, ...
Minneapolis Star Tribune Cocaine found in bus where rocker Weiland died Minneapolis Star Tribune Video (01:43) : Scott Weiland, the former ...
George Lucas on Star Wars Episode VII: 'I Really Liked It'George Lucas plans on attending the premiere of Star Wars: The Force Awakens later this month, according to The Hollywood Reporter . The 71-year-old ...
Strong job gains send US stock market sharply higherAlbuquerque’s source for local news and live streaming video online. KRQE reports on news, weather, traffic, investigations and sports for New ...
Samsung agrees to pay Apple $548 million in settlementSamsung has been fighting Apple in court for years, but it sounds like it's getting near to letting it all go. The South Korean electronics maker ...
Here’s Why Donald Trump’s December Poll Numbers MatterHere’s some good news for Donald Trump fans: History is now on his side. For months, members of the Republican Establishment have dismissed ...
International Business Times Contrary To Popular Belief, Men Use Planned Parenthood Too – And They Love It International Business Times Contrary ...
FCC’s Net Neutrality Rules Challenged in Appeals CourtThe FCC’s long effort to establish net neutrality rules came before a D.C. appeals court on Friday, as a three-judge panel raised questions over ...
How Using Your Phone To Pay For The Subway Can Help Fight Climate ChangeBuying a pass for public transportation with your phone doesn't just save you time it can also save your city big bucks . This week, 81 cities ...
Officer Who Repeatedly Punched 51-Year-Old Marlene Pinnock Will Not Be Charged With CrimeYesterday, the Los Angeles County DA’s office announced that it would not be pressing charges against highway patrol officer officer Daniel Andrew, ...
Resources last updated: 12/5/2015 4:15:55 AM