SQL Insert With Select And Specify Value For A Field

Oct 24, 2007

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 :)

View 4 Replies


ADVERTISEMENT

Select The Insert Value Into Field In Another Table!!!!!!

May 24, 2002

i have tbl_location which includes userid, building, room. i combine the building and room into one feild called mailstop

SELECT Userid, Building +'/'+Room AS mailstop
FROM tbl_Location

i then want to take this recordset and insert it into a field called mailstop in my employee table. but they must based upon the userid of the location table and the userid of the employee table.

so the userid of the location table must match the userid of the employee table and insert that mailstop value into the mailstop feild in employee table. i want to get this right the first time. any help would be greatly appreciated.

View 1 Replies View Related

Can I Insert/Update Large Text Field To Database Without Bulk Insert?

Nov 14, 2007

I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind.  I've tried using the new .write() method in my update statement, but it cuts off the text after a while.  Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.

View 6 Replies View Related

Conditionally Select A Field Depending On Another Field Value

Jul 13, 2004

I want in my query to select a different field in case another one is null. in mysql i'd do it like this:

select
a
,if(b is null, c, b)
,d
from
alphabet

how can this be done in sql server?
thanks

View 2 Replies View Related

Reporting Services :: Select Text Field Dataset Based On User Select Option?

Aug 4, 2015

I have a report that uses different datasets based on the year selected by a user.

I have a year_id parameter that sets a report variable named dataset_chosen. I have varified that these are working correctly together.

I have attempted populating table cell data to display from the chosen dataset. As yet to no avail.

How could I display data from the dataset a user selects via the year_id options?

View 4 Replies View Related

WHERE Field=(select Field From Tables)??????

Aug 26, 2005

I need some help.I am trying to write a query which does the followingSELECT * from table1 where field1=(SELECT distinct field1 FROM table1WHERE field2='2005' or field2='2010')I need all the values from table1 which match any value from field 1from the subquery.Any help is appreciated.thanks

View 4 Replies View Related

INSERT-SELECT Depending On The Select:ed Order

Aug 15, 2006

I'm doing a INSERT...SELECT where I'm dependent on the records SELECT:ed to be in a certain order. This order is enforced through a clustered index on that table - I can see that they are in the proper order by doing just the SELECT part.

However, when I do the INSERT, it doesn't work (nothing is inserted) - can the order of the records from the SELECT part be changed internally on their way to the INSERT part, so to speak?

Actually - it is a view that I'm inserting into, and there's an instead-of-insert trigger on it that does the actual insertions into the base table. I've added a "PRINT" statement to the trigger code and there's just ONE record printed (there should be millions).

View 3 Replies View Related

INSERT INTO... SELECT... Cannot Insert Duplicate Key...

Jul 20, 2005

I want to add the content of a table into anotherI tried to copy all fields, except the primary key:INSERT INTO table2(field2, field3, field4, ...)SELECT field2, field3, field4, ...FROM anotherDB.dbo.table1gives the following error:Violation of UNIQUE KEY constraint...Cannot insert duplicate key...Why?I didn't ask him to copy the key column; Isn't the SQL Server supposedto know how to increment the key ?

View 2 Replies View Related

Select Max Value Of One Field For Value Of Other Field(s)

Feb 29, 2008

I would like to query a table for a max value of one field for a distinct combination of two other fields. Let's call these fields RowID, ObjectID, and ObjectType. RowID is an auto-increment field, so for each distinct combination of ObjectID and ObjectType, there will be many values of RowID. To visualize an example:

RowID, ObjectID, ObjectType
1 , 1 , 1
2 , 1 , 2
3 , 1 , 3
4 , 1 , 1
5 , 1 , 2
6 , 1 , 3

Of these rows, I would only want 4, 5, and 6 (max values for distinct combination of ObjectID and ObjectType).

I hope I explained this clearly. I would imagine I'd need to use some form of nested query, but nothing I have tried so far has worked. I am using SQL 2005.

Thanks!

View 1 Replies View Related

Get The Field Value After INSERT

Jul 23, 2005

Hi,Problem:I need to get the value of auto-incremented field from just insertedrecordIn Oracle this is INSERT .. RETURNING command.In SQL Server there are @@IDENTITY, IDENT_CURRENT, SCOPE_IDENTITY- @@IDENTITY returns the value from the very LAST insert on any tableinvolving in the insert process ( including triggers ) ,so this value may have nothing to do with my table- IDENT_CURRENT returns the last identity value generated for aspecific table in any session and any scope,so this value may come not from my session- SCOPE_IDENTITY returns the last identity value generated for anytable in the current session and the current scope , but from the veryLAST insert command ( that may be some INSERT in the audit tables)so it may have nothing to do with my tableQuestion :- Is there any trusted way I can get the value of auto-incrementedfieldin my table and in the scope of my session?Thanks, Eugene

View 2 Replies View Related

Insert Should Aquire Only Exclusive Rowlock. Why Does Insert Block Select With ( Updlock, Rowlock )?

Mar 12, 2007

Here is the situation i am stuck with, see the example first and below explained the problem:

-- 'SESSION A

create table foo (

id integer,

pid integer,

data varchar(10)

);

begin transaction

insert into foo values ( 1, 1, 'foo' )

insert into foo values ( 2, 1, 'bar' )

insert into foo values ( 3, 1, 'bozo' )

insert into foo values ( 4, 2, 'snafu' )

insert into foo values ( 5, 2, 'rimrom' )

insert into foo values ( 6, 2, 'blark' )

insert into foo values ( 7, 3, 'smeg' )

commit transaction

create index foo_id_idx on foo ( id )

create index foo_pid_idx on foo ( pid )

begin transaction

insert into foo values ( 9, 3, 'blamo' )

-- 'SESSION B

begin transaction

select id, data from foo with ( updlock, rowlock ) where id = 5;



-- Problem:

-- Uncommitted transaction in session A, with insert into table FOO, aquires lock on index foo_pid_idx which BLOCKS select with ( updlock, rowlock ) in session B.

-- Insert should aquire only exclusive rowlock. Why does insert block select with ( updlock, rowlock )?



Appreciate your help,

Rajesh.

View 5 Replies View Related

Insert Into Sequence Field

Mar 25, 2002

What I want to do is: insert into newtable (field1, field2, sequence) select
(fielda, fieldb, #) from oldtable and have the sequence field "re-seed" to 1 every time the value in fielda/field1 changes. This added requirement makes this not an identity field problem, if I understand identity fields. This is a data conversion problem, so I'm converting 1000's of rows from oldtable to newtable and want the sequence to re-seed at every change in value of the fielda/field1 data; in fact fielda/field1 is a simplification as there could be multiple controlling fields forcing a re-seed.

TIA...Al

View 2 Replies View Related

Insert Text Field

Apr 3, 2001

Hellow, Everyone,

I am having a problem in Inserting a text field into another Text field on another table. each record in the field might be more than 255 charactors.

Any help would be appreciated.

Thanks,

View 1 Replies View Related

SQL 2012 :: Get ID And Set To Another Field On Insert

Feb 21, 2015

I want to learn if its possible to get ID in trigger and set it to another field.

I can do it by using scope_identity and using update command but I dont want to run another update command.

This is a very heavy loaded system that runs maybe 10-30 transactions on every second and I cannot afford that. I come up with a solution by using sequences but if I could use ID would be better.

View 1 Replies View Related

Insert Into A Table With Only One Field (ID)

Nov 25, 2007

Hello,
I would like to know the sql-query to insert a new row into a table which has only one column (ID) which is the primary key.

Thanks a lot for any suggestion !

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

View 4 Replies View Related

How Can I Insert # In A Character Field

May 2, 2006

Hello,I wonder how I can insert a string which contains #, as # is a specialcharacters in sqlThanks

View 1 Replies View Related

Value To Select Of Field

Feb 27, 2008

Hi,
 I've got a question!
In a database I have a field interest-target with values like 8,15,115,3 and 18,13,15,6 and 51,6,7,118 etc.
Now I like to select from these fields the value 8. I tried the following:
SELECT * FROM TABLE WHERE FIELD LIKE '%8,%' OR FIELD LIKE '%,8,%' OR FIELD LIKE '%,8%'
I will the also get the value 18 wich I don't like :-(
Any solutions here?
Thanks!
 Roel

View 7 Replies View Related

How Do You Select A Field That Has ' In It

Feb 15, 2005

I want to select only the descriptions from a table that have a ' in them. Anyone know how to do this?

exp: Select * from SkuMaster where Description like '%'%'

I have tried all kinds of combinations with no luck.

View 2 Replies View Related

Select Where A Field Contains @

May 31, 2006

Is there a way in SQL Server to query for a character that is in a field? ... I have this old table before there was form validation, and I want to get the addresses out of the column that actually have an @.

I want to do something like this.

SELECT
email
FROM
xyz
WHERE
email contains '@'
AND
email contains '.'

any ideas? Will this work?

Thanks,
-L

View 4 Replies View Related

Insert Multiple Values Into One Field

Jul 24, 2006

i can't believe my situation is unique, but my searches for answers have proven fruitless, so please bear with me.i have a date field in my database, and have previously used a simple textbox to allow users to insert a date.  i want to make this a little step a little nicer, however, and have added a dropdown for the month, a textbox for the date (i'm not "good" enough to dynamically change this around for a dropdown list), and a dropdown list for the next few years.what "insert" statement would allow me to accomplish this?  what i currently have doesn't blow up, but neither does it actually insert anything.  here's my statment:<asp:SqlDataSource ID="sdsVersion" runat="server" ConnectionString="<%$ ConnectionStrings:DB %>"    InsertCommand="INSERT INTO [Version] ([Major], [Minor], [Sub], [Rev], [Date]) VALUES (@Major, @Minor, @Sub, @Rev, @Month + '-' + @Day + '-' + @Year)">    <InsertParameters>        <asp:Parameter Name="Major" Type="Int32" />        <asp:Parameter Name="Minor" Type="Int32" />        <asp:Parameter Name="Sub" Type="Int32" />        <asp:Parameter Name="Rev" Type="Int32" />        <asp:ControlParameter ControlID="ddlMonths" Name="Month" PropertyName="SelectedValue" Type="Int32" />        <asp:ControlParameter ControlID="tbDate" Name="Day" PropertyName="Text" Type="Int32" />        <asp:ControlParameter ControlID="ddlYears" Name="Year" PropertyName="SelectedValue" Type="Int32" />    </InsertParameters></asp:SqlDataSource>

View 7 Replies View Related

How Can I Insert ' (Apostrophe) Into Sql Table Field ?

May 30, 2007

How can I insert ' (Apostrophe) into sql table field ?
Insert Into Table(Field) Values(?)
After executing sql statement above, I want to see ' (apostrope) in field.
Thanks

View 3 Replies View Related

Insert Null In A Numeric Field

Jul 2, 2007

I need to insert a null valvue when the user does not impute any text.
here is my code
If cell_phone.Text = "" Then
cell_phone.Text = "dbnull.value"
End IfDim mySqlConnection As New SqlConnection
mySqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings("Call_ListConnectionString").ConnectionString
Dim strSQL As String = "INSERT INTO Employees(Clock_ID, Last_Name, First_Name, Cell_Phone, Home_Phone, Work_Phone, Email, Primary_Day_Phone, Primary_Night_Phone, Blackberry_Number) VALUES ('" & clock_id.Text & "','" & last_name.Text & "','" & first_name.Text & "','" & work_phone.Text & "','" & home_phone.Text & "','" & cell_phone.Text & "','" & email.Text & "','" & prime_day_lst.SelectedValue & "','" & prime_night_lst.SelectedValue & "','" & blackberry.Text & "')"Dim mySqlCommand As New SqlCommand(strSQL, mySqlConnection)
mySqlConnection.Open()
mySqlCommand.ExecuteNonQuery()
mySqlConnection.Close()
THanks
Mike
 

View 4 Replies View Related

How To Insert Null Into Date Field?

Dec 11, 2007

hi all!I have a task, for example, to create a record for bill. I have table which represents this bill entity (Bill_ID, Amount,  CreationDate, ExposureDate, PaymentDate)In table definition date fields allow null. I would like to create bill, which means insert record:  (new_bill_id, 1000, 2007.12.11, null, null) But it couses exception. Smth like: System.Data.SqlTypes.SqlTypeException, date should be not null.  How could I do it?Please advice!    

View 5 Replies View Related

Trying To Append A Field If An Insert Happens To Contain A Duplicate

Jan 9, 2004

I deliberately intend to add some duplicates to one of my tables. For eg

Job User IsAdmin

JobID 235User ID 1
JobID 235User ID 5
JobID 235User ID 9
JobID 235User ID 5
JobID 235User ID 2
JobID 235User ID 9
JobID 235User ID 10
JobID 235User ID 1

I know its bad practice to do such a thing but there is a genuine reason. What I need to do is to be able to have a SQL statement that appends true to the IsAdmin field whenever it encounters the next UserID thats happens to be a duplicate. Hence the above would look like:

Job User IsAdmin

JobID 235User ID 1
JobID 235User ID 5
JobID 235User ID 9
JobID 235User ID 5 True
JobID 235User ID 2
JobID 235User ID 9 True
JobID 235User ID 10
JobID 235User ID 1 True

Thanks

View 1 Replies View Related

How To INSERT INTO [Table] ([Field]) VALUES('I Have A ' In Value')

Jun 2, 2004

Hi, I want to INSERT INTO [Table] ([Field]) VALUES('I Have a ' in value')

please teach me how to

xxx

View 2 Replies View Related

Insert TIME Only In DateTime Field

Jun 23, 2004

I am doing a temporary retro-upgrade right now. So, I know this isn't exactly in the scope of ASP.Net. Ordinally my posts are. However, I need a VBScript example of how to insert the Date only into the DateTime field of an SQL 2000 Server. By default, if you try to, the server automatically adds the date "1/1/1900". Can anyone help me please?

View 2 Replies View Related

Concatinating Two Field And Insert The Result

Aug 20, 2005

Hii,I need to concatinate two field and insert the result into each record. So far I managed to display the concatination but how do I insert it?use northwind
select city, region,([city]+ +[region]) as uniqefrom customerswhere region is not nullThe resulting records in Quary Anchorage AK AnchorageAKTsawassen BC TsawassenBCVancouver BC VancouverBCSan Francisco CA San FranciscoCA
 

View 1 Replies View Related

Retireve The Uniqueidentification Field After Insert

Mar 14, 2006

Hey
 
Just wondering how to get the uniqueidentifier when you do an insert....I know how to do the @@Identity one but not sure how to get the guid back?
is it even possible...I just prefer to use the guid at this point due to security concerns of identity eg they increase it by 1 and get some where they shouldnt...
 
if i have to do a vb.net guid then insert how do I garentee a unique guid...eg i got a catch exception so if it catches that it already there then I would prefer it to try again is it possible?
 
thanks again

View 9 Replies View Related

Cant Insert NULL To DateTime Field

Jun 5, 2006

Hi I'm using DetailView and I have a text box which show the date. I have formated the date as shortDate {0:d} format. I want to insert/update null if that text box is empty.
I have tried putting null value in my Update statement in sqlDataSource. And I'm getting error saying can't convert varchar to smalldatetime.
If I don't set null value as above, some large date (01/01/2033) has been inserted in my database.
Can anyone help me?
Moe

View 5 Replies View Related

SQL Insert Statement That Returns The ID Field

Sep 7, 2000

I'm fairly new to SQL, so this might be simple question:

I am adding records to an SQL7 database by using the INSERT statement. The table has an IDENTITY field which is auto-incremented, so a value is not needed for the field in the query.

Is there any parameters for INSERT that returns to me the value of the IDENTITY field for the record I just created?...

Any help or suggestions would be appreciated.

View 2 Replies View Related

Insert Time Into DateTime Field

Apr 5, 1999

I would like to be able to insert a time value only into a SQL 7 table colum which has been set as a datetime datatype. When I insert '12:34:44 PM' into the colum it actually inserts '1900-1-1 12:34:44 PM'. An Access table will allow you to insert the time value without adding the '1/1/1900' date value. Can this be done in SQL7?

Thanks for any help.

View 1 Replies View Related

How Can I Insert Picture In Image Field

Feb 6, 2001

I try with textcopy but I did'n do nothing. Please help me

View 1 Replies View Related

SOS!!!can Not Insert The Field Of Smalldatetime With NULL

Jan 8, 2005

Hello everyone,
I am using DTS to transform data from foxpro.when it meets the date of NULL,it fails and says"Insert Error , column 4( 'Coloumn_name ',DBTYPE_DBTIMESTAMP), Status 6 data overflow ".
And then i try to open the DTS Transform Data task and do a preview of the data from the 'Source' side.The Null values are treated as "1899-12-30".
After reading something ,i try to write an ActiveX scripting Task to solve this problem using the function of "IsDate()",but it fails with exceptions.
Now I don't know what to do.could anyone give me some suggestion?Anything will be appreciated!ThankX in advance!

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved