Hello,
We're trying to do the following...
Declare @Name Varchar(15)
Select @Name = 'TableName'
Select @Name = @Name + Rand(SomeSeedValue)
Select *
Into @Name
From Someothertable
This just gives us syntax errors, anyone else had any success with this ?
Hello
I tried the Beta 1 of the service pack 1 to .net 3.5. If I try to add an entity (and try to save this), I get the Exception "No support for server-generated keys and server-generated values".
How can I add entities to my Sqlce- database?
I tried to give the id- column (primary key) in the database an identity, another time without identity, only primary key --> none of them worked. I always get the same error.
What do I have to change to make successfully a SaveChanges()?
Thanks for your help,
Gerald
Hello everyone,Small and (I think) very simple quesiton;-) which makes me creazy.Let's say I have two tables listed below:T1====IDX====134T2===============IDD fk_IDX===============A1A2A4B1B3B4C4D1D2D3D4I would like to select from table T2 all distinct records IDD whichhave all of fk_IDX containded in T1.The select statement should return in this case ONLY:B and Dbecasue:B has 1,3,4andD has 1,2,3,4 so it has this combination 1,3,4 contained in the T1also.I've tried to do that with group by, with having, in and it neverworks (I always became all records which one of them is in this T1table).Maybe some one from you did try something like that, and can give afast answer.I will be very greatfullGreatingsMateusz
View 2 Replies View RelatedHi,
These aren't my tables, but lets dumb it down for my sake!
I have 2 tables:
CompanyInfo: IDNumber, EffectiveDate, CompanyName
TransacationInfo: TransactID, CompanyID, TransDate, Amount
There are several records in TransactionInfo for each record in CompanyInfo.
I want to:
SELECT CompanyInfo.IDNumber, CompanyInfo.EffectiveDate, CompanyInfo.CompanyName,
SUM(TransactionInfo.Amount)
How can I do this? Can I?
is there some way to allow this to execute, using a function or something?:
select * from 'cusomer'
you see i have a list of about 300 table name in string format, and i want to use a cursor to itterate through each table, but as all the table names are in string format i cant get it done. any ideas??
This is, I'm hoping, a simple thing that I just don't know how to do. But I've searched my books and read a lot of threads but so far, no luck in what I'm trying to do. Here's the basic layout of 2 tables from my d/b (MSDE):
tblCity
cityID (p/k)
stateID (f/k to tblState.stateID)
cityName (varchar 35)
tblStudent
studID (p/k)
cityID (f/k to tblCity.cityID)
hometownID (f/k to tblCity.cityID)
studFName (varchar 25)
studLName (varchar 35)
...
I *hope* that what I tried to do there is allowed. Basically what I'm trying to do is to tie the student to both his current City (by foreign key cityID) and his hometown city (also by foreign key cityID).
So, if New York is (127, 33, 'New York') and Birmingham is (1050, 1, 'Birmingham') (in tblCity), then in the student table (tblStudent), John Smith would be
Quote: 12150, 127, 1050, 'John', 'Smith', ...
How would I format my SQL statement to get both cityNames? I thought this would work
Code:
SELECT s.studID, c.cityID, c.hometownID,
s.studLName + ', ' + s.studFName AS studNameName,
c.cityName, c.cityName AS swapconNum
FROM tblStudent AS s INNER JOIN tblCity AS c
ON (s.cityID = c.cityID)
WHERE s.studID = 12150
Can I do this? If so, what am I doing wrong? Sorry, I'm kinda sorta new to SQL.
RudeDog
hi, i am sure this is very simple, but its just got me stuck! i have a table which i use for inserting products, now its not very exciting to see the cat_id so i wanted to put a dropdown box on there which would show the cat_description from tbl_cat and then put the tbl_cat.cat_id into tbl_products.cat_id
any help would be greatful
Jez
hi there, i have a page where on my button click i need to select address, pcode from tbl_person where person_id = querystring and update or insert those values into tbl_del where order_ID = querystring 2 below is the code i am using, although im stuck on the sql stringSqlConnection objConnAddy = new SqlConnection(sConnectionStringCustInfo);using (objConnAddy)
{
objConnOID.Open();
// insert values into delivery table, second line updates price + VAT
string sqlAddy = "?????";
SqlCommand objCmdAddy = new SqlCommand(sqlAddy, objConnAddy);
objCmd1.ExecuteNonQuery();
i had a look on the net and here but i cant seem to find exactly what i want, any help would be great!
Cheers Jez
How can i just select filed name from the name without data. What query should we write foe that.
View 2 Replies View Relatedi ve a table named "Employee" in wh i ve fields "Emp_Id","Name" and "Email". i want to select each employee "Emp_Id" one by one from "Employee" table and insert it into another table named "Concerned_Department". like if i ve three rows in "Employee" table with "Emp_Id" 1,2 and 3 respectively,in this case i want to first select "Emp_Id"=1 then "Emp_id"=2 and lastly "Emp_id"=3 and insert them into "Concerned_Department"table.
after that the process starts again like inserting "Emp_Id"=1 in "Concerned_Department" and so on.
plz note that i want to insert "Emp_Id" one by one not all the fields together.
can u plz tell me the query for that wh i ve to write in SQL
Thanks and happy valentine day to all of u
regards
Ahmed Bilal Jan
lets say that this select statement will give me 100 records..
"Select UserID from Users where location = 'FL'"
from these 100 records, I want to randomly get 5 records. whats the best way to go about doing this
Hi, my sql is not too hot so i hope someone can help me. I need to select all the records from one table that do not exist in 2 other tables. I know it sounds simple enough but for some reason i can not get it working. It may have something to do with the fact that the field i am searching on are datetime fields. Here is a shortened version of my code.
SELECT DateOfStats
FROM table1
WHERE (DateOfStats NOT IN
(SELECT dateofstats
FROM table2)) and (DateOfStats NOT IN
(SELECT dateofstats
FROM table3))
Thanks for the help in advance.
i need to select records from table "A" if only the "PK" of "A" exists in table "B". I need to return a resultset not just a single record. The problem is table "B" is not a table in database instead a user supplied table which can be a datatable in memory.
View 2 Replies View RelatedHi!
I need to create a table and populate it with other table's contents. I know this can be done DTS, I wonder if there are means to make it throught a single TSQL command.
Thanks,
Fabio
I have a temporary table that contain 1000 rows. I want to insert records from 900 to 1000 into another temporary table. Is ther any query that will do this in SQL server. With cursors I can do that but I need one shot query. Please give me some suggestions.
I using this query but it is not returning any rows.
select * into #t2 from #t1 t1 where not exists( select top 900 * from #t1 t2)
I was given this query but could not build it successfully.
can anybody help me out.
Consider the following scenario:
we have a table with single field of type Varchar(100).
we have few hundred records in it.
we want to retrieve last 'n' records from table.
i know this is bad idea but we need to have some solution to work it out.
i'm breaking my head but to no use.
Please help
I've got an update statement with a subquery (I'll post the code further down) that I need to either make dynamic or do something else to make sure it does what I want.
The query is as follows:-
UPDATE dbo.tbl_Process_List_Control_Table
SET LastUpdateDateTime = (
SELECT ISNULL(MAX([LatestRowUpdateDateTime]), @LastUpdateDateTime)
FROM [wtbl_Process_List_Patient]
)
WHERE ProcessList = @ProcessName
This is a called proc with the following parameters:-
@LastUpdateDateTime is the date of the last record to be loaded
@ProcessName is the name of the process that was started/finished.
I need to make [wtbl_Process_List_Patient] dynamic so it looks at a different table based on a passed parameter.I've tried making the whole thing dynamic but it states I need to declare @LastUpdateDateTime which I can't see how to do as it's already passed to the proc (as is the process name).
Hi
I've created a linked table connection to an external data source within sql server 2005.
The link looks OK, I can see all the tables etc, but I want to create a view on another database to some of the tables on the linked database.
How do I perform something as simple as a select statement as I can't seem to be able to select anything.
Is there something that i need to do first in order to be able to select fields from the linked table?
Thanks
Good Day guys, sorry i'm just new in SQL Server.
My problem is like this:
I want to pass Table Object to a declared variable and make View.
Usually we make like this:
"Select * from Table1"
I want like this:
"Select * from @Table1"
I want to pass Table1 to a variable "@Table1" which the variable
reads as Table.
Please help me guys, i really appriciated everything.
Thanks,
Ero-Sennin26
hi!I have two tables with same structure. I need to run a select query thatwill return only the rowsfrom one table that are not in the other.In MySQL it would beselect * from table1 MINUS select * from table2.but in MsSql I can not find the apropriate way to do this.Any Help?Zvonko
View 3 Replies View RelatedI have table A with Primarykey column, AId, Identity field.
I have table B with foriegn key column , AId,
I have same number of rows in both tables (over million), but in Table B, column AId is null at present, as it was added later.
Now I need to select all AId values and update them in existing rows.
Any idea, how would my T-SQL look like???
Many Thanks,
Hello,
I have to design a DTS package (not SSIS ) in which i want to select the destination table dynamically. Can any one help me out.
Thanks
MV
I've got a big problem that I'm trying to figure out:
I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.
I don't need this for an individual ID select, I need it applied to every record from the table.
My address table has some columns that look like:
[AddressID] [int]
[LocationID] [int]
[Type] [nvarchar](10)
[Address] [varchar](50)
[City] [varchar](50)
[State] [char](2)
[Zip] [varchar](5)
[AddDate] [datetime]
[EditDate] [datetime]
AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table.
So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.
How can I do this efficiently with perfomance in mind???
Thank you in advance for any and all replies...
I have table HITS with columns ID and hitDate and table DATESS with columns startDate and endDate.
I can have alot of entries in the HITS table with hitDate between the times of a startDate and endDate entry in the DATESS table.
What I am trying to do is to select all the ID's of the entries where hitDate is between startDate and endDate. (In the end I would use min and max to get the firt and last hit between those times)
I have tried:
SELECT ID from hits,Datess WHERE hitdate between startDate and endDate
but it does not give the required results. I think the problem is the tables are not related in any way. Is there another way to do this.
Thanks
I have table 1 from which I select some values belonging to users.In table 2 I store tips a user might give: tipid, tiptitle, tiptext,tipcreatedateA user may give more than one tip.But now I want a query that selects the info of a SINGLE user and the LATEST tip he created, so resultset might look like:username lastname sex tiptitle tiptext tipcreatedateSo even though a user might have given more tips, only the latest will be retreived...and thus 1 row for a particular user is returned...How would I construct such a query? The problem is that I want to get exactly 1 row but more than 1 column from the table 2, so I think I cannot use the SQL Server "TOP" command...
View 6 Replies View RelatedI have a sql select query that I'm pulling from a "Years" table to link to 3 columns in an Items table.ZCValuesYear table has two colums: YearID and YearYearID Year1 20042 20053 20064 20075 2008...I want to bind the "Year" value to the three colums in the ZCItem table: ItemUseFirstYearID ItemUseLastYearID ItemYearIDThe query below will pull all the "ID's" for each of the colums, but how would I make it pull the "Year" value (instead of record 4, it would pull 2007 instead)?<asp:SqlDataSource ID="sqlItemSelect" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnString %>" SelectCommand="SELECT ZCPartVault.PartVaultID, ZCPartVault.PartVaultItemID, ZCValuesYear.Year, ZCItem.ItemName, ZCItem.ItemUseFirstYearID, ZCItem.ItemUseLastYearID FROM ZCPartVault FULL OUTER JOIN ZCItem ON ZCPartVault.PartVaultItemID = ZCItem.ItemID FULL OUTER JOIN ZCValuesYear ON ZCItem.ItemUseLastYearID = ZCValuesYear.YearID AND ZCItem.ItemUseFirstYearID = ZCValuesYear.YearID AND ZCItem.ItemYearID = ZCValuesYear.YearID" > </asp:SqlDataSource>
View 2 Replies View RelatedHello.
I need to select a random record from TABLE. It might look easy with using RAND() function, but the tricky part is that ID's which are the PRIMARY KEY, were assigned as a random number.
So right now ID's in that TABLE look some thing like that: -18745, 45809, 129, -5890023, 487910943, -209, etc...
If any one have any ideas please respond.
Thanks in advance.
Hey all,
I trying to figure out if you can get a list of table names from a particular database by writing a single SQL statement.
E.G. In Oracle, from SQLPlus - you can say:
Select table_name from user_tables;
Is there anything like that SQL that I could run from Query Analyser to get a list of table names from a particular SQL Server database?
Thanks in advance,
Tommy
Hy!
I'm trying to write a query which returns only some rows in my table...
For example :
I want all the rows included between 30 and 50 from table XYZ
(30 and 50 are not keys but really the numbers of records)
Is there someone out there who has a hint for me???
Thank you for your help and sorry for my english!
RadiFluide
I need to select the last 6 rows from a table... I was doing it like this:
select * from table where id > (select max(id)-6 as id from table)
But if one of the records is deleted it will only show 5.... Is there any easy way to make it show the bottom 6 records?
much thanks sql gods...
Hi all,
I Have the following situation:
CREATE TABLE First_Table (id INTEGER IDENTITY(1,1) NOT NULL,
titre VARCHAR(50) NOT NULL,
annee INTEGER NOT NULL,
idMES INTEGER,
genre VARCHAR(20) NOT NULL,
resume TEXT,
codePays VARCHAR(4),
CONSTRAINT PKFilm PRIMARY KEY (idFilm),
FOREIGN KEY (idMES) REFERENCES Artiste,
FOREIGN KEY (codePays) REFERENCES Pays);
I'ld like fill in this tables records inserting in the column id values I got in the one other table. In Oracle it is possible to do it using sourceTable.nextval where sourceTable is created as: CREATE SEQUENCE sourceTable;
How can I do it in MS SQL or Transact-sql?
Thanx all
I want to figure out if a student has not been to class in the last 3 scheduled days. The days do not have to be consecutive.
I have 2 tables. One table with schedules and one with attended time.
What I really need is to the find the 3rd highest MAX() from the schedule table for each student. I was wondering if someone has any idea how I could find these record?
Example scheduled records
student1 2/4/2004 7 hours scheduled
student2 2/4/2004 8 hours scheduled
student1 2/3/2004 8 hours scheduled
student1 2/2/2004 6 hours scheduled <---Need this one
student2 2/2/2004 8 hours scheduled
student2 1/30/2004 4 hours scheduled <---Need this one
...thousands more...
It has been a real brain teaser for me. If anyone has even an inelegant solution, I would love to see it.
Hey all
This is probably the wrong category to post my question in. Apologies if it is.
I have got a Data Flow Task that has an OLE DB Source and a Flat file destination.
Is there a way to do the following:
SELECT id,'31809','C:LCImportDataImages' & id & '.jpg', '1' FROM table1
This obviously doesn't work but I want to insert the id from the table, followed by the value '31809', followed by 'C:LCImportDataImagesid.jpg', followed by '1'
So, the id in 'C:LCImportDataImagesid.jpg' is the same as the id from the table.
I want the file to look like:
1, 31809, C:LCImportDataImages1.jpg, 1
2, 31809, C:LCImportDataImages2.jpg, 1
5, 31809, C:LCImportDataImages5.jpg, 1
etc...
I would really appreciate it if someone could point me to the right direction.
I am trying to work on it..will put up a solution if I find one myself.
Many thanks,
Rupa