I am trying to insert values into a table, but i have an Employee ID field, which needs incrementing. How can i do this through my SQL query string? Is this possible? As it can't accept a NULL value.
Link ( GroupID int , MemberID int ) Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID, then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
Hi, I have a SQL server 2005 database with a series of multiple fields. One of the fields has a array of strings seperated by semi-colons like so: Red;Green;Blue My question is, how can i run a query on all of the fields that have the value of say Green in it. Note that these values vary in different order and numbers. Thanks
I'm very new to SQL server and can use some help. MyTable has ColumnA, which contains strings composed of 1 to 4 numeric characters (0 thru 9) followed by alphabetic characters. For example, "53ASDF". In my query, I need to create ColumnB, which takes the numeric prefix from ColumnA's string and prepends it with zeros, if necessary, to create a string of exactly 4 numeric characters. For example, I could get the following result:
ColA ColB "6abc" "0006" "457def" "0457" "7232hij" "7232"
I have implemented a temporary solution using a CASE statement: SELECT ColA, ColB = CASE WHEN ISNUMERIC(LEFT(ColA, 4)) = 1 THEN (LEFT(ColA, 4)) WHEN ISNUMERIC(LEFT(ColA, 3)) = 1 THEN '0' + (LEFT(ColA, 3)) WHEN ISNUMERIC(LEFT(ColA, 2)) = 1 THEN '00' + (LEFT(ColA, 2)) WHEN ISNUMERIC(LEFT(ColA, 1)) = 1 THEN '000' + (LEFT(ColA, 1)) ELSE '' END FROM MyTable
Because of additional complexities, I need to implement the solution with a loop instead of a CASE statement. Can someone please describe such a solution?
I'm very confused about how variables work in SQL Server, but made an attempt to implement a solution. Hopefully, someone can make corrections and describe how to use it with a SELECT statement. I would greatly appreciate any suggestions. This is what I started with:
DECLARE @ColBstring char(4) DECLARE @num int SET @ColBstring = '' SET num = 1; -- Get the numeric prefix from ColumnA's string WHILE(isnumeric(substring(colA, 1, num)) = 1) @ColBstring = (substring(colA, 1, num) num = num + 1
-- Prepend the ColumnB string with zeros WHILE(LEN(@ColBstring) < 4) @ColBstring = '0' + @ColBstring
I need to search a nvarchar field based on the format of the text. This field holds values in two formats: 000 000 000 000 and 000000. I only want to search through the records that are in the 000 000 000 000 format. Can anyone give me direction on how to go about doing this or give me some key words to search for on Google? Fixing this problem is not an option. This is a county tax DB from a poor county with almost a million records in it. Thanks for the help!
I have FeaturedClassifiedsCount field, which I would like to update each time record is selected. How do I do it in stored procedure on SQL 2005? This is my existing code:alter PROCEDURE dbo.SP_FeaturedClassifieds @PageIndex INT, @NumRows INT, @FeaturedClassifiedsCount INT OUTPUT
AS BEGIN select @FeaturedClassifiedsCount = (Select Count(*) From classifieds_Ads Where AdStatus=100 And Adlevel=50 ) Declare @startRowIndex INT; Set @startRowIndex = (@PageIndex * @NumRows) + 1;
With FeaturedClassifieds as (Select ROW_NUMBER() OVER (Order By FeaturedDisplayedCount * (1-(Weight-1)/100) ASC) as Row, Id, PreviewImageId, Title, DateCreated, FeaturedDisplayedCountFrom classifieds_Ads WhereAdStatus=100 And AdLevel=50 )
SelectId, PreviewImageId, Title, DateCreated, FeaturedDisplayedCount From FeaturedClassifieds Where Row between@startRowIndex And @startRowIndex+@NumRows-1
HiI have a field containing numbers. I want to do some simple arithmetics with it, say value=value+1 or value=value-1 or or even value+2. What is to be done, is fixed at design time. I think this could be done by loading the row or record to my program and doing the calculations there. And then storing the record back. But this seems too complicated.Is there a single query doing that in data table.
Hi, I have one Auto increment field in the table. I have a problem that suppose I have 10 Records in the table, Now If someone delete record no 3 and 5 then it has 8 records. But The field will give 11 no to new record I want it first fill 3 and 5 then give 11 to the new record. Is it possible with the auto increment field in sql server 2005.
Hey, Im building a site within Visual Studio 2005 and im using the SQL database system it provides. I currently have a number of tables each with a primary key that is set to the 'int' field type and also set to auto increment 1, 2, 3 etc etc. My question is it there a way to make this field increment automatically, providing a unique value but also automatically placing a couple of pre-defined letters before the number? Obviously it can't be an 'int' anymore because it'll be holding something like MEM1, MEM2 etc. Is there a way to do this is Visual Studio? Many Thanks!
Now I want to create usernames from #test1 by considering first character of first name and last name and if same combination found then append with 01.
Example if #test1 contains data as below:
1,Abhas, Pawar 2, Arun, Pawar 3, Ashis, Panday
Then i want to create username like:
apawar apawar01 apanday02
but if same username exists in #test2 then i want to inser records as below:
first apawar will check in #test2, if not exists insert as it is:
if apawar01 exists in #test2 then, cretae apawar02 instead of apawar01
for next create apawar03 and insert and so on...
In brief I want to check created username eith #table2 and if same exists then first check if lower value available if not then create with lower value and insert.
I was just wondering on a very simple database table with lets say a primary key set to columb ID and another columb lets say products, can you make the primary key automaticly increment its self whenever a new entry has been put in?For instance say I have this table set up with ID Being the primary KEY, Columb 1 = ID( INT ), Columb 2 = Products ( VarChar(50) ), and have the fields ID = 1, and products = my product.....and if a user inserts a new record say from a gridview or some sort of data entry the second ID Feild will automaticly be 2 and the products gets updated per user input.......I'm very sorry but I'm having a hard time putting this into words for some reason..umm basicly user adds something into the products feild and the ID field automaticly increments one number higher from the last one?ThanksAdam.
declare @kk int set @kk=0 insert into tblSSAppsOrgEntityToEmployerMapDiffer (Id, OrgEntityCode, EmployerId, [Default],
[Code] ...
In above example Id is PK for Differ tbl and Temp tbl not having field related to this. thats why i have to take and increment that Id value manually.... but like above way i m getting error ..........
I am trying to write a query that takes the max recordID on table A, and increment it by 1 for every record that is inserted into table A. The recordID field does not identity field property turned on.
Can you give me some help in getting this done? Is what I am trying to do even possible?
I have a bit of brainteaser that's going to take some serious thought.
I'm importing information from .xls files into a SQL table. The problem is I need to check for dupes and increment certain fields on success of dupe find and then not insert or delete the dupes.
For example, if I have Adam, Turner, 32, 50 already in the table and someone tries to insert Adam, Turner, 32, 50...I need it to increment to read Adam, Turner, 64, 100 and not insert the record. (Notice 2 fields were incremented.)
With that, I have created an INSERT trigger as follows:
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER Trigger [dbo].[trgInsertCheck] ON [dbo].[MyTable] FOR INSERT AS BEGIN EXEC sp_UpdateDupes EXEC sp_DeleteDupes END
The first stored procedure checks for dupes and updates if any dupes are found as follows: --------------------------------------------------------------------------------------------------------------------------------------
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER Procedure [dbo].[sp_UpdateDupes] AS
DECLARE @FirstName varchar(20), @LastName varchar(20), @Age int, @Widgets int DECLARE c1 CURSOR FOR
SELECT FirstName, LastName, Age, Widgets FROM MyTable GROUP BY FirstName, LastName, Age, Widgets HAVING COUNT(*) > 1
OPEN c1 FETCH NEXT FROM c1 INTO @FirstName, @LastName, @Age, @Widgets WHILE @@FETCH_STATUS = 0 BEGIN
UPDATE MyTable set Widgets = Widgets + @Widgets, Age = Age + @Age WHERE FirstName = @FirstName AND LastName = @LastName
FETCH NEXT FROM c1 INTO @FirstName, @LastName, @Age, @Widgets END CLOSE c1 DEALLOCATE c1
Lastly, it finds all dupes, deletes them and inserts one row back in as follows: --------------------------------------------------------------------------------------------------------------
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER Procedure [dbo].[sp_DeleteDupes] AS
DECLARE @FirstName varchar(20), @LastName varchar(20), @Age int, @Widgets int --declare all fields in table
DECLARE c1 CURSOR FOR
SELECT FirstName, LastName, Age, Widgets FROM MyTable GROUP BY FirstName, LastName, Age, Widgets HAVING COUNT(*) > 1
OPEN c1 FETCH NEXT FROM c1 INTO @FirstName, @LastName, @Age, @Widgets WHILE @@FETCH_STATUS = 0
BEGIN --Delete all dupes...the cursor remembers the current record
DELETE FROM MyTable WHERE FirstName IN (SELECT FirstName FROM MyTable GROUP BY FirstName HAVING COUNT(FirstName) > 1) AND LastName IN (SELECT LastName FROM MyTable GROUP BY LastName HAVING COUNT(LastName) > 1) AND Age IN (SELECT Age FROM MyTable GROUP BY Age HAVING COUNT(Age) > 1) AND Widgets IN (SELECT Widgets FROM MyTable GROUP BY Widgets HAVING COUNT(Widgets) > 1)
--insert the current record back into the table
INSERT INTO MyTable(FirstName, LastName, Age, Widgets) VALUES(@FirstName, @LastName, @Age, @Widgets)
FETCH NEXT FROM c1 INTO @FirstName, @LastName, @Age, @Widgets END CLOSE c1 DEALLOCATE c1
Is there an easier way to do this?
(I know Age doesn't make much sense in this example but just replace it with a field would logically be incremented such as wadgets.)
CREATE FUNCTION fctisnumericex(@c varchar(1)) RETURNS int AS BEGIN RETURN CASE WHEN ASCII(@c)>=ASCII('0') AND ASCII(@c)<=ASCII('9') THEN 1 ELSE 0 END END
CREATE FUNCTION fctstringincrement (@string varchar(255),@maxlen int) RETURNS varchar(255) AS BEGIN DECLARE @@posr int DECLARE @@posl int DECLARE @@c varchar(1) DECLARE @@token1 varchar(255) DECLARE @@token varchar(255) DECLARE @@token3 varchar(255) DECLARE @@i int /* emulates parts of the behaviour of s_modformatting::substringincrement */ /* 1. find the place where the numeric token starts from the right */ /* if we didn't find any non-numeric part then it might well be that the rightmost digit is already numeric */ IF dbo.fctisnumericex(SUBSTRING(@string,DATALENGTH(@string),1))=1 BEGIN SELECT @@posr=DATALENGTH(@string) END ELSE BEGIN SELECT @@i=DATALENGTH(@string) SELECT @@c=SUBSTRING(@string,@@i,1) WHILE dbo.fctisnumericex(@@c)!=1 BEGIN SELECT @@i=@@i-1 IF @@i<1 BEGIN BREAK END SELECT @@c=SUBSTRING(@string,@@i,1) END SELECT @@posr=@@i END /* so have we got any numeric part inside that string? */ IF @@posr>0 BEGIN /* yep. see how long it lasts */ SELECT @@i=@@posr SELECT @@c=SUBSTRING(@string,@@i,1) WHILE dbo.fctisnumericex(@@c)=1 BEGIN SELECT @@posl=@@i SELECT @@i=@@i-1 IF @@i<1 BEGIN BREAK END SELECT @@c=SUBSTRING(@string,@@i,1) END /* separate now the parts of the string */ IF @@posl>1 BEGIN SELECT @@token1=SUBSTRING(@string,1,@@posl-1) END ELSE BEGIN SELECT @@token1='' END SELECT @@token=SUBSTRING(@string,@@posl,@@posr-@@posl+1) IF @@posr<DATALENGTH(@string) BEGIN SELECT @@token3=SUBSTRING(@string,@@posr+1,DATALENGTH(@string)-@@posr) END ELSE BEGIN SELECT @@token3='' END /* increment the numeric part */ SELECT @@token=convert(varchar(255),convert(int,@@token)+1) END ELSE BEGIN /* no numeric part at all. start with 1 at the end */ SELECT @@token1=@string SELECT @@token='1' SELECT @@token3='' END /* recompose the string and trim to max length if necessary */ RETURN SUBSTRING(@@token1+@@token+@@token3,1,@maxlen) END
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits. The mfg_start_date is delcared as a string variable
option 1 I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.
I Have a table that needs to have 2 unique number.
detail_id and detail_print_id.
detail_id is already an IDENTITY.
both fields need to be different, because when importing, it imports the same data into a table twice, with only a slight data change (and id is not one of the changes).
So I thought i could do the following:
detail_id INT NOT NULL IDENTITY(1,2), detail_print_id INT NOT NULL IDENTITY(2,2), --blah blah
that way, the detail_id will always be odd, and the detail_print_id will always be even. however SQL Server 2005 only allows 1 identity per table, and both these fields need to be auto generated when the field is inserted, so as to prevent double data.
is there anyway I can create a int column to auto increment, without the column being an IDENTITY??
also, I would prefer to not have to create a second table with a single column just for this work.
I have an MS SQL Server table with a Job Number field I need this field to start at a certain number then auto increment from there. Is there a way to do this programatically or within MSDE?
Hello to all, I have a problem with ms sql query. I hope that somebody can help me. i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)). Example Datas for Table Relationships: IDMember Relationships . 3387 (2345, 2388,4567,....) 4567 (8990, 7865, 3387...) i wirte a query to check if there is Relationship between two members. Query: Declare @IDM int; Declare @IDO int; Set @IDM = 3387, @IDO = 4567; select * from Relationship where (IDMember = @IDM) and ( cast(@ID0 as char(100)) in (select Relationship .[RelationshipIDs] from Relationship where IDMember = @IDM))
But I get nothing by this query. Can Someone tell me where is the problem? Thanks
I am running a converted DTS package which executes a stored procedure which extracts fields from a SQL database table and puts them to a flat file. Two of the fields are datetime fields. The stored procedure does not convert the dates, and under SQL 2000 DTS the fields extract as strings in format yyyy-mm-dd hh:mms. When this gave me an error in SSIS, I added a data conversion task between the Ole DB Source and the Text file destination to convert the two fields to DT_STR format and used the converted fields in my mapping for the text file. I am still getting an error on this:
-1071636319,0x,Data conversion failed. The data conversion for column "mm_hdr_process_date" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page."
The target code page is 1252 (ANSI) and I have tried several variations of length, and I still get this error. Any suggestions? I would prefer not to have to change every stored procedure we have which may do this. Thanks for any help. MaryKate
I have one field in SQL Server Report Called Status which is text field. that status can be "Abesent" , Late In", "Early Out" , "Early Out and Late in" , its in table Like this.
Emp no | Name | Date | Time | Status 1 Kaisar 1-1-07 7:15 Late In 1 Kaisar 1-2-07 17:15 Early Out 1 Kaisar 1-4-07 - Absent 1 Kaisar 1-5-07 - Absent 1 Kaisar 1-6-07 - Absent
And So On...... ----------------------------------------------------------------
i want to get count some thing like this
Total Of Absent : 3 Total Of Late in : 4 Total Of Early Out :5
Hello All,I'm trying to parse for a numeric string from a column in a table. WhatI'm looking for is a numeric string of a fixed length of 8.The column is a comments field and can contain the numeric string inany positionHere's an example of the values in the column1) Fri KX 3-21-98 5:48 P.M. arrival Cxled ATRI #27068935 3-17-982) wed.kx10/26 Netrez 95860536Now I need to parse through these lines and return only the 8 digitnumbers in itThe result set should be2706893595860536This is what I've done so farDeclare @tmp table(Comments_Txt varchar(255))Insert into @tmpselect Comments_Txt from Reservationselect * FROM @tmp where Comments_Txtlike ('%[0-9][0-9][0-9][0-9][0-9][0**9]%')But it returns the entire comments field in the result set. What I needis a way to return just those 8 digits.Any Ideas??Thanks in advance!!!
Hi!I got a column, with different dates in it (Ddata type "nvarchar")...when running a SELECT on this column, I'm trying to filter thoserecordsets out, WHERE this column is NULL (I checked the table, thereare "empty" fields in the column):"SELECT bla FROM bla WHERE myColumn NOT NULL"but I still receive those "empty" fields in my resultset ...so I tried it with:SELECT bla FROM bla WHERE myColumn <> ''again, "empty" fields in my resultset ....what's going wrong there? is there a possibility to check what kind ofvalue I got in my column!?Thanks!Peter
We have account numbers that are string values, looking like: "01.02.02.00.0040.000.000".
We need to parse individual segments and pull out a range of values treating the segment like an integer. For example we would like all accounts that have the fifth segment ranging in values from .0040. through .0060. inclusive.
We've been trying to do something with the like clause: LIKE '__.__.__.__.[0-0][0-0][4-6][0-0]'.
We want 40 through 60 but we're getting 40, 50, and 60. If we change the last bracket to [0-9] we get 40 through 69.
Does anyone have any suggestions as to either fix our like clause or another approach?
I have a few columns in table with default value defined as zero length string (''). I want to insert record from DetailsView which uses SqlDataSource as DataSource. In the ItemInserting event, if the data is not valid, I want to use zero length string for the column. But I always get Null instead of zero length string. The code in ItemInserting event looks like this: If objddl.SelectedIndex > 0 Then e.Values("myFld") = objddl.SelectedItem.ValueElse e.Values("myFld") = ""End If The line: e.Values("myFld") = "" put Null in the column. How can I set a column as zero length string using the SqlDataSource? Any help is appreciated. Thanks.
