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.
I have been tasksed to create a data table and stored procedure to extract a special formatted XML file that is an attachment with a standard XML envelope. The XML file is an attchment in a node within the XML wrapper. There are other MIME files (pdf's ) that are handle by a seperate procedure. But I need to just extract the XML file attached along with those and put it into the datable with some other PK?FK fields.
Is a blob the best datatype. How to I insert that XML file into it?
I have a requirement where i have to insert empty string in column whose datatype is decimal.
I have to replace the column value if it is null then insert it as ' '.
Obviously its generate error msg for conversion, is there any workaround for this. I cannot add 0 or other value as this column is used to generate some output value.
IIF(Column1 IS NULL, ' ', Column1))
I used TRY_PARSE but it insert null value if there is conversion error.
I want Compare two Table data and insert changed field to the third table ...
View 9 Replies View RelatedI 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 RelatedHi,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 RelatedHi,
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 :)
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
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,
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 !
Hello,I wonder how I can insert a string which contains #, as # is a specialcharacters in sqlThanks
View 1 Replies View RelatedWhat sql data type would fit these value? This is timestamp field in the ERP.
2013-05-24-20.03.46.843480
is it possible to have a field that is calculated using a User Defined Function, and index that field?
View 9 Replies View RelatedI have two tables tbl1 and tbl2, which I do a full outer join between tbl1 and tbl2 on recordId field. The recordId field is not a key in either of the tables.
If there is one row each for a recordId 123 in both tables, the select query would return one combined row.
If tble1 had two rows for recordId 123, and tbl2 had one row for the same, it would return to rows repeating the data in tbl2.
If tbl2 had two rows and bl1 had one row, it would return two rows in output repeating the data in tbl1.
Is the above correct? Would the result be different if it was an inner join instead of full outer join?
Is it ever possible that one of the two records with recordId 123 will be dropped from the result?
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 RelatedHow 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
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
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 RelatedI 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
Hi, I want to INSERT INTO [Table] ([Field]) VALUES('I Have a ' in value')
please teach me how to
xxx
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 RelatedHii,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
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
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
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.
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.
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.
I try with textcopy but I did'n do nothing. Please help me
View 1 Replies View RelatedHello 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!
hi everybody I have this case which I couldn't solve and need badly your help
table1
id1 field1 field2
1 a b
2 a c
table2
id1 field1 field2
a b
a c
I need to update table2 on the (id1) if the value of field1 and field2 in table 1 is equal to field1 and field2 in table 2 and inserting null when there is no match:::
I'm trying with this code but couldn't get thru
UPDATE table2
SET id1= COALESCE(table2.id1, 0)
from table1
left outer Join table2
ON
table1.id1=table2.id1
thanks
Hi,
I have a table with 1000000 records i try to add a field with following`spec.
[mkey] [int] IDENTITY(1,1) NOT NULL
I get the following meassage:-
yearly' table
- Saving Definition Changes to tables with large amounts of data could take a considerable amount of time. While changes are being saved, table data will not be accessible.
Then I Have the following error:-
Time out error
- How is the best way to copy a large table from one to anther.
regards
Hi,
Does anyone know how should I create a table in order that I can insert values(numbers) in the primary key field, using insert statements. I also would like to know if there are any differences between SQL 2k and SQL 2k5.
Thanks in advance for any reply.
hey all
I am new to this side of the sql and just testing some statements
what I want to do
insert one line into a table that already has data in it
KBCopy = Database name
KBStatusID = (PK, tinyint, not null)
KBStatusName = (varchar(100) not null)
the following is what I have tried.
use KBCopy
go
insert into KBStatus ("KBStatusID", "KBStatusName") Values ("4", "Archived")
the error I receive
Msg 128, Level 15, State 1, Line 2
The name "4" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables.
Column names are not permitted.
I presume this is to do with the ID?
thanks
jewel