Select Statement With A New Identity Field
Hello,
Is it possible to generate a identityfield dynamically upon select, like this:
SELECT tempID AS identity(1,1), username FROM table1 ORDER BY username ASC
I want the output to be:
1 - Name1
2 - Name2
3 - Name3
The reason for this, is that i want to change the sort order in many diffrent ways, but i need to get the IDs from 1-?? even when the sort order changes.
Like:
SELECT tempID AS identity(1,1), username FROM table1 ORDER BY username DESC
I want the output to be:
1 - Name3
2 - Name2
3 - Name1
Patrick
View Complete Forum Thread with Replies
Related Forum Messages:
TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys, I have the query below (running okay): Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01 The results are just as I need: Field01 Field02 ------------- ---------------------- 192473 8461760 192474 22810 Because other reasons. I need to modify that query to: Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are: Field02 ---------------------- 22810 8461760 And what I need is (without showing any other field): Field02 ---------------------- 8461760 22810 Is there any good suggestion? Thanks in advance for any help, Aldo.
View Replies !
SELECT @@IDENTITY During INSERT STATEMENT Error
The following SQL statement fails on SQL CE 3.5 but works on SQL Express 2005: "INSERT INTO BOOKINGS VALUES(@now,'"+note+"'," + p + "); SELECT @@IDENTITY;" Compact 3.5 doesnt like the SELECT statement claiming that: There was an error parsing the query. [ Token line number = 1,Token line offset = 72,Token in error = SELECT ] Can anyone suggest the correct SQL to implement this via Compact? i.e. How do I retrieve the Identity value during and insert statement? I have removed the SELECT @@IDENTITY; portion of the statement and it runs fine.
View Replies !
The Select Statement Is In A Field
Ok, I inherited this database and there is a field that stopres a selectstatement. Is there anyway possible to execute the value of the fieldwithin a select statement?For example:the table:Name "george"lookupForName "Select orders from Ordertable"So maybe something like select name, execute(lookupforname) as ordersSorry, I didn't design this, just inherited :)george
View Replies !
Query To Sum The Same Field Twice In The Select Statement
Hello friends , I have table (MoneyTrans) with following structure [Id] [bigint] NOT NULL, [TransDate] [smalldatetime] NOT NULL, [TransName] [varchar](30) NOT NULL, -- CAN have values 'Deposit' / 'WithDraw' [Amount] [money] NOT NULL I need to write a query to generate following output Trans Date, total deposits, total withdrawls, closing balance i.e. Trans Date, sum(amount) for TransName='Deposit' and Date=TransDate , sum(amount) for TransName=Withdraw and Date=TransDate , Closing balance (Sum of deposit - sum of withdraw for date < = TransDate ) I am working on this for past two days with out getting a right solution. Any help is appreciated Sara
View Replies !
Select Statement Eliminate Field Name
Hi I have tabelA, Which has 10 columns, I need to select 10 column values only no field names. Is there any way I can select only table values not field names. I don't want to see field name in my query result set. Please let me know. I appreciate your help. Thanks Regards -Leong
View Replies !
How Do I Call A Select Statement Properly When The Field Is A Yes/no?
Hello, i am pretty new to asp. I am trying to do a select statement for sending an email to everyone who is not an admin. the code is below, i know it must be fairly simple, yet i do not know how to do it. With the code below, I select everyone. I want to know how to do it properly, similar to the second which does not work. Dim cmd As New OleDbCommand("SELECT Username, Pass, Gender, FirstName, LastName, Email, NickName FROM tblUsers", conn) DOES NOT WORK: Dim cmd As New OleDbCommand("SELECT Username, Pass, Gender, FirstName, LastName, Email, NickName FROM tblUsers WHERE Admin = 'N'", conn) Thanks in advance.
View Replies !
SELECT Statement - How To Not Get Column Field Names?
I do a SELECT * from table command in an ASP page to build a text fileout on our server, but the export is not to allow a field name rows ofrecords. The first thing I get is a row with all the field names. Whydo these come in if they are not part of the table records? How do Ieliminate this from being produced? Here's the ASP code....<html><head><title>Package Tracking Results - Client Feed</title></head><body><%' define variablesdim oConn ' ADO Connectiondim oRSc ' ADO Recordset - Courier tabledim cSQLstr ' SQL string - Courier tabledim oRSn ' ADO Recordset - NAN tabledim nSQLstr ' SQL string - NAN tabledim objFSO ' FSO Connectiondim objTextFile ' Text File' set and define FSO connection and text file object locationSet objFSO = CreateObject("Scripting.FileSystemObject")'Set objTextFile =objFSO.CreateTextFile(Server.MapPath("textfile.txt"))'Response.Write (Server.MapPath("textfile.txt") & "<br />")Set objTextFile = objFSO.OpenTextFile("C: extfile.txt",2)' write text to text file'objTextFile.WriteLine "This text is in the file ""textfile.txt""!"' SQL strings for Courier and NAN tablescSQLstr = "SELECT * FROM Courier"' set and open ADO connection & oRSc recordsetsset oConn=Server.CreateObject("ADODB.connection")oConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &"c:/Database/QaTracking/QaTracking.mdb" & ";"set oRSc=Server.CreateObject("ADODB.Recordset")oRSc.Open cSQLstr, oConnResponse.ContentType = "text/plain"Dim i, j, tmpIf Not oRSc.EOF ThenFor i = 1 To oRSc.Fields.CountobjTextFile.Write oRSc.Fields(i-1).NameIf i < oRSc.Fields.Count ThenobjTextFile.Write " "End IfNextobjTextFile.WriteLineWhile Not oRSc.EOFFor i = 1 To oRSc.Fields.CountIf oRSc.Fields(i-1) <"" Thentmp = oRSc.Fields(i-1)' If TypeName(tmp) = "String" Then' objTextFile.Write "" &_'Replace(oRSc.Fields(i-1),vbCrLf,"") & ""' ElseobjTextFile.Write oRSc.Fields(i-1)' End IfEnd IfIf i < oRSc.Fields.Count ThenobjTextFile.Write " "End IfNextobjTextFile.WriteLineoRSc.MoveNextWendEnd IfobjTextFile.CloseSet objTextFile = NothingSet objFSO = NothingoRSc.CloseSet oRSc = NothingoConn.CloseSet oConn = Nothing%></body></html>
View Replies !
Select Statement With Run Time Field Selection
I have this SELECT statement. SELECT [issueID], [name] FROM [MyIssue] What I wanted to do is in addition to the above statement, I want to add two run time fields like this: 99 [issueID],'All Issues' [name] So let's say the above select statements generates this list: Summer 2007 Issue Winter 2007 Issue The two addition fields will make the result list like this: 01 Summer 2007 Issue 02 Winter 2007 Issue 99 All Issues How do I accomplish this? Any help is much appreciated.
View Replies !
Convert A Time Field In The Select Statement Of The Query
Hi, I have a field called "Starting DateTime" and I want to convert into my local time. I can convert it in the report with the expression "=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Starting_DateTime.Value)", but that is too late. I want to convert it in the Select statement of the query. Can anyone help me please? Thx
View Replies !
Need To Set A Field In A Select Statement Equal To Yes Or No If Record Exists In Separate Table
Hey gang, I've got a query and I'm really not sure how to get what I need. I've got a unix datasource that I've setup a linked server for on my SQL database so I'm using Select * From OpenQuery(DataSource, 'Query') I am able to select all of the records from the first two tables that I need. The problem I'm having is the last step. I need a field in the select statement that is going to be a simple yes or no based off of if a customer number is present in a different table. The table that I need to look into can have up to 99 instances of the customer number. It's a "Note" table that stores a string, the customer number and the sequence number of the note. Obviously I don't want to do a straight join and query because I don't want to get 99 duplicates records in the query I'm already pulling. Here's my current Query this works fine: Select *From OpenQuery(UnixData, 'Select CPAREC.CustomerNo, CPBASC_All.CustorCompName, CPAREC.DateAdded, CPAREC.Terms, CPAREC.CreditLimit, CPAREC.PowerNum From CPAREC Inner Join CPBASC_All on CPAREC.CustomerNo = CPBASC_All.CustomerNo Where DateAdded >= #12/01/07# and DateAdded <= #12/31/07#') What I need to add is one more column to the results of this query that will let me know if the Customer number is found in a "Notes" table. This table has 3 fields CustomerNo, SequenceNo, Note. I don't want to join and select on customer number as the customer number maybe repeated as much as 99 times in the Notes table. I just need to know if a single instance of the customer number was found in that table so I can set a column in my select statement as NotesExist (Yes or No) Any advice would be greatly appreciated.
View Replies !
In Code Behind, What Is Proper Select Statement Syntax To Retrieve The @BName Field From A Table?
In Code Behind, What is proper select statement syntax to retrieve the @BName field from a table?Using Visual Studio 2003SQL Server DB I created the following parameter:Dim strName As String Dim parameterBName As SqlParameter = New SqlParameter("@BName", SqlDbType.VarChar, 50) parameterBName.Value = strName myCommand.Parameters.Add(parameterBName) I tried the following but get error:Dim strSql As String = "select @BName from Borrower where BName= DOROTHY V FOWLER " error is:Line 1: Incorrect syntax near 'V'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'V'. Source Error: Line 59: Line 60: Line 61: myCommand.ExecuteNonQuery() 'Execute the query
View Replies !
Insert Row In Table With Identity Field, And Get New Identity Back
I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table). What is the most direct way to do this in SSIS? TIA, barkingdog P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?
View Replies !
How To Change Integer Field To Identity Field.
I have an integer column(not null) in a table. how can i change that to identity column through query analyzer, through a script, b'cos i have to apply this to number of databases. I tried this, but got an error: ALTER TABLE table_name alter column company_id int IDENTITY ERROR: Incorrect syntax near the keyword 'IDENTITY'. any help is welcome.
View Replies !
Last GASP On &&"Insert Row In Table With Identity Field, And Get New Identity Back &&" ?
While I have learned a lot from this thread I am still basically confused about the issues involved. .I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple. To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package. 1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful. 2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key! 3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed? TIA, Barkingdog
View Replies !
Select Statement Within Select Statement Makes My Query Slow....
Hello... im having a problem with my query optimization.... I have a query that looks like this: SELECT * FROM table1 WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998) it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View Replies !
Change In Pk Field Into An Identity Field - How?
Ok, simply problem. I have a table that has an in primary key field. I want to (need to) set this up as identity, through a script. Is it right that there is no (working) alter table statement for this? After trying for two hours I finally just started to look on how Enterprise Manager handles this. And - EM basically... ...creates the new table, ...sets IDENTITY_INSERT to ON, ...copies the data over (select *, insert) ...deletes the old table and renames the new one. Frankly, this is not really nice. Anyone a better idea?
View Replies !
Change Field To Identity Field
I have a table named PERSON and a field named PERSON_ID. How can I set this field to Autonumber? I know I have to use the IDENTITY command, but when I view this field in "design" view, all the IDENTITY options are grayed out. How can I set this field with the IDENTITY properties? Thanks
View Replies !
Sql Identity Field
Hello friends, I had created a web application and uploaded the application. Now the problem is with the DB. I had created the DB on the server (using script). But the fields that have identity field yes is not been set Now how I can set the fields identity field to yes. Fields are already there. Only I want to set there identity field to yes.Let me know how this can be done. Thanks & RegardsGirish Nehte
View Replies !
Re-set Identity Field
Hi: I created a small SQL Express database/ASP.net/C# application and in the learning process. Before I implement it I would like to re-set autonumber / identity field back to 1. Also, I need to start with the blank database. I am not sure how to approach that? Can you assist? Thanks
View Replies !
&<&<identity Field&>&>
hi friends,i have an identity column in my table.(e.x : id )and it's identity increment is 1.when i insert a row the id field is 1, and in next record the field is 2.....now , i delete second record(id=2)and now when i insert a record again , the id column is 3.i want to record be 2 instead 3.plz help me.thanks
View Replies !
Identity Field
Please, How can I get the value of the identity field of the register that I was including in the data base. I am using a stored procedure in SQLSERVER in a asp .net application and I need to show that for the user, it´s like the number of the reclamation.
View Replies !
Identity Field
Folks I am inserting some values into a table with the following stmt Insert into table(number,name) values ('12','name') In the table I have one more identity column ID. I know that I cannot insert a value in that column and the value is automatically increased once I insert a record. After this insert statment, I need to get the value of the ID (the most recent one) in the next select statement. ie Select @@identity from table (any condition????) How do I get the most recent ID value? Actually I m inserting the records in a loop and the ID is increased for every insert. Thanks for the help,
View Replies !
Identity Field
Greetings ! Having an unusual problem - have created several (20) tables in a database. All of these tables have an identity field in them (defined as FIELDNAME int IDENTITY(1,1) NOT NULL ). In most of the tables this works as expected, but in 2, so far, when I do the first load (an insert based on a different database) the identity fields are all zero. Have recreated the tables, changed the field name and location, etc. Anyone have any clues ? TIA Eric
View Replies !
Identity Field
Dear Fellow Administrators, We are experiencing an identity problem on a table with 3.7 millions rows of data in the table. The identity field is not auto numbering any more and are wondering if anyone has a suggestion on how to get the auto number field working again without doing a bcp and restoring the table back to the database from bcp. Any suggestions? Thanks in Advance, Daimon
View Replies !
Bcp With IDENTITY Field
If my table has an IDENTITY field, say, the table schema is: CREATE TABLE BBB( id int NOT NULL IDENTITY(1,1), name varchar(20), job varchar(40)) My data file, which does not carry the IDENTITY field and its field terminator. The data file looks like this: debbie cao,programmer John Doe,engineer Mary Smith,consultant I tried to use a format file to bulk copy data from the data file to the table. Never had any luck. On the other hand, if I put a comma before the name field, say, the data file looks like the following: ,debbie cao,programmer ,John Doe,engineer ,Mary Smith,consultant then, bcp works fine. But, my data file is automatically generated. It does not suppose to have the leading comma. Without the leading comma, I have no idea how may I make bcp work. The SQLBOL says it can be done. Does anybody have an example to show me? Please help, thanks!
View Replies !
Identity Field Fix
I inherited a system with a SQL 2000 DB. We discovered an identity field named barcode with some values that are incorrect. About 1000 of the records contain a barcode field with 13 digits, not forteen as required. This field is a standalone field only used on an ID card. I would like to select those 1000 records and update the barcode field to 14 digits. Is there an easy way to do this? Thx
View Replies !
Identity Field
Hello... New to SQL and have the following situation.... Example of table (currently) PayID (Primary Key) PaymentNumber (Identity Field) John 1 Tim 2 Chris 3 John (same as John above) 4 Jack 5 Steve 6 Would like to see it work this way PayID (Primary Key) PaymentNumber (Identity Field) John 1 John (same as John above) 2 Tim 1 Chris 1 Jack 1 Steve 1 Can the identity column be setup to start the counter over depending on value of PayeeID...If another Jack was added then the Payment Number would be 2 for Jack since there is already a record with value 1.... Hope this makes sense... Thanks
View Replies !
Multiple Tables Used In Select Statement Makes My Update Statement Not Work?
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View Replies !
Returning Identity Field
Hi all i'm trying to get the identity field after inserting into db, what am i doing wrong? thanks a lot my sproc: CREATE PROCEDURE ng_AddCotacao(...@Codigo_cotacao int OUTPUT)ASBEGINSET NOCOUNT ONINSERT INTONegocios_cotacoes(...)VALUES(...)SELECT @Codigo_cotacao=SCOPE_IDENTITY()SET NOCOUNT OFFENDGO class file public class Cotacoes { public int codigoCotacao; } public class CotacaoAtualiza { public Cotacoes cotacoes = new Cotacoes(); public CotacaoAtualiza() { } public void AdicionarCotacao( ... ) { SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["stringConexao"]); SqlCommand myCommand = new SqlCommand("ng_AddCotacao", myConnection); myCommand.CommandType = CommandType.StoredProcedure; ... SqlParameter paramCodigo_cotacao = new SqlParameter("@Codigo_cotacao", SqlDbType.Int, 4); paramCodigo_cotacao.Direction = ParameterDirection.Output; myCommand.Parameters.Add(paramCodigo_cotacao); ... myConnection.Open(); SqlDataReader result = myCommand.ExecuteReader(); while(result.Read()) { this.cotacoes.codigoCotacao = (int) result["@Codigo_cotacao"]; } myConnection.Close(); } calling into code-behind file: CotacaoAtualiza ca = new CotacaoAtualiza(); Cotacoes cotacoes = ca.cotacoes; Response.Redirect("Cotacao_confirma.aspx?cotacao=" + cotacoes.codigoCotacao);
View Replies !
Using An Identity Field As Primary Key
I want to use the Identity field (increment 1,1) as a primary key andhave a unique constraint on my other field which is of type char.I am worried that related data in other tables may lose referntialintegrity if records in the ID table get messed up and need to bere-entered.Can you please advice on best way to do this. I definitely need anumeric id field because it makes the joins and queries so muchfaster.
View Replies !
SQL Server Identity Field
I have an identity field on a table in SQL Server. The identity seedis 1 and the identity increment is 1. If I remove a record from thistable, the identity sequence is broken. For example:Table contents prior to record delete:Fname(varchar), Lname (varchar), row_id (identity)--------------------------------------------------Smith, Jane, 1Smith, Tom, 2Jones, Mark 3Delete from mytable where row_id = 2Fname(varchar), Lname (varchar), row_id (identity)---------------------------------------------------Smith, Jane, 1Jones, Mark 3How can I re-set the identity field so that there are no gaps in thesequence?For example, I want to update the sequence after the delete so thatrecords look like this:Fname(varchar), Lname (varchar), row_id (identity)---------------------------------------------------Smith, Jane, 1Jones, Mark 2
View Replies !
SQL Server Identity Field
>> I have an identity field [sic] on a table in SQL Server ... If Iremove a record [sic] from this table, the identity sequence isbroken. <<Your problem is that you still think in terms of files, records, andfields and do not understand an RDBMS model of the world. A real SQLprogrammer does not use IDENTITY for a host of reasons. What you aredoing in SQL is mimicking a sequence magnetic tape file, circa 1950.Get a book and learn the basics before you write any more code.
View Replies !
Identity Field And IDENTITY_INSERT Is Off
ok, so i get this error, Exception Details: System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'TBL_LAPTOP_BOOKINGS' when IDENTITY_INSERT is set to OFF. i ran SET INDENTITY_INSERT TBL_LAPTOP_BOOKINGS ON before i ran my script... and i also checked, we are running SQL 2000 with service pack four, because of the cursor error.... so im just wondering, is there anyway to make identity_insert to always be on, or any other way to have a unique id in my booking_id field?? Thanks Guys, Justin
View Replies !
How To Rearrange The Identity Field
Dear All, Please help me! I have a table will a identity field the value are, for example, 1, 2, 4, 6, 8, 10, 11 .... etc. I want to make it in a sequence, that is, 1,2,3,4,5,6,7... How can I do it, Please help me, thank you for you helping. Best Regards, J.H.
View Replies !
Replication With Identity Field
1. Can someone tell me how to replicate the identity field. 2. When I use the 'NOT FOR REPLICATION' option, I keeps getting synx error. Here is the command: alter table tusers alter column {userid INT} IDENTITY (1, 1) [NOT FOR REPLICATION]. Do you know why? 3. When I use the transaction replication wizard, why some of the table do not allow me to replicate(There is a key and cross symbol on the articles selection)? Thanks
View Replies !
INSERTING Into IDENTITY Field
I have a table with the following schema: CREATE TABLE [itis].[wrk_taxon_authors] ( [wb_taxon_author_id] [int] NOT NULL , [taxon_author_id] [int] IDENTITY (1, 1) NOT NULL , [taxon_author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [kingdom_id] [smallint] NOT NULL ) ON [PRIMARY] GO I am trying to insert the following data (as you can see fields are seperated with the | ): 19||Flores-Villela and Sánchez-H., 2003|5| 20||Wiegmann, 1828|5| 16|17482|Gray, 1838|5| 17|9823|(Wiegmann, 1828)|5| I get the following error:Server: Msg 4869, Level 16, State 1, Line 1 Bulk Insert failed. Unexpected NULL value in data file row 1, column 2. Destination column (taxon_author_id) is defined NOT NULL. Server: Msg 4869, Level 16, State 1, Line 1 Bulk Insert failed. Unexpected NULL value in data file row 2, column 2. Destination column (taxon_author_id) is defined NOT NULL. Since I have defined column 2 as an identity column, I don't understand why SQL Query analyzer is upset when I do not have a value in that field. To me, I would think it would auto-insert an integer (starting at seed 1 and incrementing by 1), but it doesn't. Could someone tell me what I'm doing wrong?
View Replies !
Identity Field Issue
Hello, I have a field in my table called "MyNewCounter", where the data type is int. Unfortunately I set the identity field to "No" instead of "Yes" on this field. Since this time a few thousand records were imported in and this field is now set to a value of null for all records. When I tried to change the identity to "Yes" and save the table, I get an error indicating that the field can not contain a null value. If I populated this field with numeric values, would this correct my issue? and if so, what SQL command would I use to set the field value starting at 1 to increment for each existing record?
View Replies !
Using Conditional Statement In Stored Prcodure To Build Select Statement
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View Replies !
How To Write Select Statement Inside CASE Statement ?
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ? following part of the procedure clears my requirement. SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E can any one help me in this? please give me a sample query. Thanks and Regards, Kiran Suthar
View Replies !
Out Of Order Identity Field - Sql2000
Hi AllI am finding unexpected results when inserted into a newly createdtable that has a field of datatype int identity (1,1).Basically the order I sort on when inserting into the table is notreflected in the order of the values from the identity field.Have I been wrong in assuming that it should reflect the order from thesort?The code is ...create table tmp (A varchar(50), L float, C int identity(1,1))insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Lland I don't understand why the values in tmp.C aren't in the ordersuggested by the sort.Any comments most appreciatedBevan
View Replies !
Manage Identity Field At Subscriber
Anyone has the standard approach for handling the identity column with replication environment. Currently i am using following process. http://www.databasejournal.com/features/mssql/article.php/3483421 I want to know if someone have some generic script to do this or much easier way..
View Replies !
|
TRACKER
