Hi all,
I've got a large table (3mil records) with a number of columns, but currently no way to refer to any individual column. I therefore need a primary key, but does anyone know of a SQL statement I can use that will create a column (say, ID) that is automatically filled with an incrementing 'counter'? Or, instead, how can I set unique incremental values after first creating the column?
Many thanks,
Graham
What would be the best way to insert unique values into a table/unique column ?
I cannot make that table/unique column as indentity. Right now, I use a staging table with indentity column, insert rows then insert rows back to
final table.
Suggestions are much appreciated.
Ivan
Hi
I am trying to insert values into a table that doesn't exist there yet from another table, my problem is that because it is joined to the other table it keeps on selecting more values that i don't want.
Code Snippet
SET NOCOUNT ON
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
SELECT M.MemberID, '6', CASE M.MaritalStatusID WHEN 1 THEN '7'
WHEN 2 THEN '8'
WHEN 3 THEN '9'
WHEN 4 THEN '10'
END
FROM Members M
INNER JOIN _MemberProfileLookupValues ML
ON M.MemberID = ML.MemberID
WHERE M.Active = 1
AND OptionID <> 6
When i execute that code it returns all the values, let say OptionID = 3 is smoking already exists in the MemberProfileLookupValues table then it is going to select that persons memberID
I want to insert only members values that aren't already in the _MemberProfileLookupValues from the Members table (I think that it is because of the join statement that is in my code, but i don't know how i am going to select members that aren't in the table, because i have a few other queries that are very similar that are inserting different values, so ultimately
ONLY INSERT THE MemberID the values 6 and the statusID of X if it is not in the table already.
Any ideas / help will be greatly appreciated. Please help.
Kind Regards
Carel Greaves
hi,
In the below code, i am trying to insert the identity value into the 2nd column of the Symp_Quote table. Schema of the table Symp_Quote is as follows.
CRAETE TABLE Symp_Quote
(
QuoteRevision_ID INT PK,
DocumentSeq TINYINT PK,
DocumentDisplayName VARCHAR (255)
)
1st column data is coming from the select statement. but for 2nd column i have to insert identity or some unique value. to do that i was trying withe the following code, but it failed.
INSERT INTO Symp_Quote
(
QuoteRevision_ID,
DocumentSeq,
DocumentDisplayName
)
SELECT
S.QuoteRevision_ID,
IDENTITY (INT,1,1) AS DocumentSeq,
T.Name,
FROM Symp S
INNER JOIN Trilogy T
ON
SQR.tril_gid = FQR.tril_gid
kindly correct me whr I am wrong.........
Thanks,
Rahul Jha
I am using Access 2007 and I have 10 columns of data.
I am trying to select all the data from all 10 columns where the first column's data returns only the Unique values and the other columns return all the data from the row that is returned from the first column.
I have used this
SELECT DISTINCT [SFR Rates All].Sorter, [SFR Rates All].SProgram, [SFR Rates All].S_Price, [SFR Rates All].Min_Loan, [SFR Rates All].Max_Loan
FROM [SFR Rates All];
But that returns all the data in the table and all values in the Sorter column with duplicates because each row has distinct data.
I am trying to return unique values from [SFR Rates All].Sorter (the first column) and all the data from the other 9 columns that are contain the row with the unique value in Sorter.
I know that I am missing something basic but well, I can't figure it out.
Thanks
Bob
Hi, all,
I have a question about adding a unique key column to an existing table.
what i trying to do is that: I have already created a table, now i wanna add a ID column to this table, and generate the values for ID column from 1 to the existing row number. How can I get this done?
Thanks a lot in advance for any guidance.
I have the following two tables...
tblServer
-serverID
-serverName
-serverLocation
tblSite
-siteID
-serverID
-siteName
-siteIpAddress
I need to write a select query that gets the values of all columns but only returns unique sites because some sites are load balanced across several servers and where this is the case I don't want the site to appear multiple times in the list.
Is there a way to insert multiple values into a single column based on various "tests".
For example, I want to check a sales_order table and flag all new orders coming in against previous orders placed that were determined to be fraudulent. If I were to set up i.e. five different tests(i.e. check email, credit_card number etc. against previous fraud orders), then there would be the possibility that any given order can be flagged 1 to 5 times. I want to record all of these tests within the same column if possible. Therefore the output may look something like the following:
order_number fraud_score
1234567890 a,b,d
5432109876 e
2345678901 null
3455607983 a,b,c,d,e
I was considering adding five additional columns to the table and running five different update steps, but this doesn't appear very scalable. Any suggestions would be greatly appreciated!
thanks in advance-
trevorb
I am having Test table with (id int , DataDescription xml) . I need to read the values from DataDescription column and insert into one table.
View 2 Replies View RelatedHi, I have a question regarding how to insert one column values into a table by selecting from different table..Here is the syntax..
------------
insert into propertytable values (select lastvalue+incrementby from agilesequences where name='SEQPROPERTYTABLE', 13926, 0, 4, 1, 451, 1, 8, 1)
the first column in the propertytable will be... select lastvalue+incrementby from agilesequences where name='SEQPROPERTYTABLE'
How do I do that..Help PLZ..
IF I have a table like the below one and i have to insert a number value which is inserted as varchar in an int column then what is expected behavior of this statements .
create
table stud
(id
int)
insert
into stud
values ('1').
I thought it should fail but it succeeds...
Hi,
I have at table as foolows
Table Cat3
{
ID,
Update datetime
}
and also have a master table as follows
Table Master
{
ID,
Cat1 Datetime,
Cat2 datettime
}
My requirement is to alter themaster table schema i.e to add a column with the name as of the table name i.e Cat3 and will lok lie as foolows
Table Master
{
ID,
Cat1 Datetime,
Cat2 datettime,
Cat3 Datetime
}
I would like to insert the data to this column. The sample output is as follows
Before insertng the data of table ;Cat3' into the Master table
Table Master
{
Id Cat1 Cat2
---------------------------------
1 D1 D2
2 D2 NULL
3 D3 D4
}
And The Cat3 table data is as follows
Table Cat3
{
ID Update
--------------------------
1 D5
3 D6
4 D7
}
The final putput of the master table should be as follows
Table Master
{
Id Cat1 Cat2 Cat3
------------------------------------------------------
1 D1 D2 D5
2 D2 NULL NULL
3 D3 D4 D6
4 NULL NULL D7
}
Can any one please let me know the query to achieve this
Thank you very much for your time and support
~Moahn
Hi all,
I want to produce some output for Mainframe application. For that I want to insert values from multiple table as source to a single column (huge in size)of a different table (Destination table). There may be same related records in all of the source tables with the primary key. When I export values from the source tables , each related records should be insterted to the destination table's field (multiple entries for each table). Please advise.
Thanks
I have the following objective:
1. I want to check a column to see if there are values (to Eliminate dups)
2. Once checked the values in a column, if not found insert the new value
Here is my code for this:
ALTER TRIGGER DUPLICATES ON AMGR_User_Fields_Tbl
-- When inserting or updating
AFTER INSERT, UPDATE AS
-- Declare the variables
DECLARE @AN varchar(200)
[Code] ....
A common partitioning scenario is when the partition column has the same value for every record in the partition, as opposed to a range of values. Am I the only person who wonders why there isn't an option to automatically partition a table based on the unique values of the partition column? Instead of defining a partition function with constants, you ought to be able to just give it the column and be done. This would be particularly valuable for tables partitioned on a weekly or monthly date; when new data is added it could simply create a new partition if one doesn't already exist.
View 4 Replies View RelatedHello,
I have a report in wich I show a list of countries. The length of this list differs each month and comes from a SSAS datasource.
I want to show this list on my report in a matrix like this:
country1 country6 country11
country2 country7 country12
country3 country8
country4 country9
country5 country10
How can I do this? I need some sort of check that counts the number of countries added and then adds a new column after 5 countries.
I'm sure there is somebody that did this or knows how to do this.
Thanks in advance!
Jorg.
Hi allNeed your help to do this; I got a table with these records:Supplier RegNo Status PoNumberABC sbh1309m 1DCD sbt99x 1FGJ sbg3939m 1FGJ sbg3939m 1OEE ey3939d 1Need to have a sql command to transform to :Supplier RegNo Status PoNumberABC sbh1309m 1 50001DCD sbt99x 1 50002FGJ sbg3939m 1 50003FGJ sbg3939m 1 50003OEE ey3939d 1 50004Any ideas?Thanks in advance.Rashid.
View 1 Replies View RelatedI have a transaction table having about 40 crore rows in source. It don't have timestamp and unique key columns. It have only Bill_month and Bill_Year columns. Actually for loading this table into staging I have added a new datetime column by adding default bill_date as 01. Then
* First we delete last 3 month data from staging tables.
* Get last 3 months data from source table.
* Load that 3 months data from source to staging table.
We do this because we only get update for last three months data. Now I have to include this transaction table as Fact table in DW. What will be the best practice for loading the fact table by picking data form staging table. Also we have to look up with dimensions for Foreign Keys.
* Should I implement the same method of deleting last 3 months records and loading them again.
Hi!
I have a request of having a select statement displaying a list of values in descending order and another column that tells what record it is.
something like this:
id | value | no
4 345 1
7 234 2
2 143 3
9 32 4
3 4 5
...
is this possible?
can it be something like this:
select id, value, increment(1,1) as no from tblTable
order by value desc
thanks.
Hi,
I'm inserting a datetime values into sql server 2000 from c#
SQL server table details
Table nameate_test
columnname datatype
No int
date_t DateTime
C# coding
SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI");
connectionToDatabase.Open();
DataTable dt1 = new DataTable();
dt1.Columns.Add("no",typeof(System.Int16));
dt1.Columns.Add("date_t", typeof(System.DateTime));
DataRow dr = dt1.NewRow();
dr["no"] = 1;
dr["date_t"] = DateTime.Now;
dt1.Rows.Add(dr);
for(int i=0;i<dt1.Rows.Count;i++)
{
string str=dt1.Rows["no"].ToString();
DateTime dt=(DateTime)dt1.Rows["date_t"];
string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')";
SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase);
cmd.ExecuteNonQuery();
MessageBox.Show("saved");
}
When I run the above code, data is inserted into the table
The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column.
Is there any conversion needed for millisecond values?
thanks,
Mani
Hi, all.
I want to Select query with incremental column on the fly.
For example
Use pubs
GO
select * from jobs where job_desc like '%e%' Order by max_lvl
returns
job_idjob_descmin_lvlmax_lvl
1New Hire - Job not specified1010
12Editor25100
13Sales Representative25100
...
I want to add here Rank Column numbering in order
select RankOnTheFly, * from jobs where job_desc like '%e%' Order by max_lvl
Then result will be..
Rankjob_idjob_descmin_lvlmax_lvl
11New Hire - Job not specified1010
212Editor25100
313Sales Representative25100
..
I can get the result using cursor and looping throught and inserting or Using Identity function.
But, I saw before there is just one simple Select query doing that.
Does anyone know this?
Thank you..
Hello,
I have a table with sixty columns in it, five of which define uniqueness for the records. Currently there are 190,775 records in the table. One of the records is a duplicate. I need to insert only the unique records from this table (all columns) into another table. I cannot use a unique nonclistered index with IGNORE_DUP_KEY in the destination table because of a problem I am having with the 'duplicate key was ignored' message. The destination table has a primary key with a clustered index on the same five columns.
How can I put together a SELECT statement that will give me all of the columns in the source table based on uniqueness of the five key columns?
Does my request make sense? Please let me know if you have questions.
Thank you for your help!
CSDunn
As a beginner i am having trouble with this.
i have two different tables , both have a name column, nvarchar datatype.
I would like to select from table B all the rows which contain a name which is not in table A.
Then insert these rows, into table A
tried a few different ways & just keep getting strange errors that refer to courier font ??
SQL Team Your my Hero !
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B
red - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red - pen
blue - pencil
blue - highlighter
blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
red blue green
pen 2 0 1
marker 0 1 0
pencil 1 3 0
highlighter 1 1 1
We tried Sum but not able to display in single row.
I have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.
What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column.
I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far.
Gurus,
I have two list boxes, user can move items back n forth, from second listbox I am inserting values into a table. So far everything is working fine.
Now I want to delete all the existing values from the table before inserting evertime..Please help me in this I dont know what to do.
thanks
kalloo
I'm trying to checking my production table table_a against a working table table_b (which i'm downlading data to)Here are the collumns i have in table_a and table_bDescription | FundID (this is not my PK) | Money I'm running an update if there is already vaule in the money collumn. I check to see if table_a matches table_b...if not i update table a with table b's value where FundID match up.What i'm having trouble on is if there is no record in table_a but there is a record in table_b. How can I insert that record into table_a? I would like to do all of this (the update and insert statement in one stored proc. if possible. )If anyone has this answer please let me know.Thanks,RB
View 3 Replies View RelatedI want to add $ symbol to column values and convert the column values to western number system
Column values
Dollar
4255
25454
467834
Expected Output:
$ 4,255
$ 25,454
$ 467,834
My Query:
select ID, MAX(Date) Date, SUM(Cost) Dollars, MAX(Funded) Funding from Application
COST is the int datatype and needs to be changed.
I have a table in which a non-primary key column has a unique index on it.
If I am inserting a record into this table with a duplicate column value for the indexed column, then what will be the error number of the error in above scenario? OR How could I find this out?
I am having issues trying to write a query that would provide me the unique GUID numbers associated with a distinct PID if the unique GUID's > 1. To summarize, I need a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different/unique.
Table1
GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2
The result of the query would only have PID1 because it has two unique GUID's. PID2 would not be listed has it has the same GUID3 in each row.
Result:
PID1
Hi,I have two tables such that in each table I need to make sure thatcolumn x in table A and column y in table B have a unique valuesmeaning that a user cannot insert a value to column A if its alreadyexist in column B and vice versa.How can I enforce it? Please remember that this two different tables.Thanks,Ori.
View 2 Replies View RelatedHi, I have been asked to write some code that can check a large table for duplicate values in a non pk column. The table may have up to 1000000 rows. The PK column is an auto increment field. For performance reasons the column in question could not be set to unique values only for inserts, an algorithm is used to create unique no's before the insert but what I am doing is double checking that their have been no duplicates created accidently. If their are duplicates I need to know what rows they occurred on.
Thanks
Hi,
Suppose a table [Inventory]:
Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 10
Table Red 20
Table Yellow 30
Chair Blue 40
Chair Red 50
I'm wondering if there is a group state like this:
Select Item, ?Function(Color), Sum(Quantity) From Inventory Group by Item
which returns this:
Table Blue,Red,Yellow 60
Chair Blue,Red 90
Does anyone has an idea how this can be achieved?
Regards,
Manolis Perrakis