I have the following data
MASTER
id
name
DETAIL
id
master_id
name
I want a perform a query where i can get all the rows of the master table which have no relationed rows in detail table.
How can I do that???
I know that this is legal sql: "SELECT 1 AS Blah"
I want to do something like this except for I need to select multiple rows each with a different value for Blah. The query needs to be legal to be passed to the SqlCommand.ExecuteReader Method. Is this possible?
I am stuck in the following scenario.
Tbl_Loan
Trans_ID
Emp_ID
Guarantor_1_ID
Guarantor_1_ID
TRN_01
EMP_01
[code]....
I use Ftp to import a comma delim file.Problem:Day 1 file has 5 records. During the course of Day 1 ,users makechanges to a field in the five records.Day 2 has same 5 records as they were at the beginning of Day 1(withoutchanges made during Day 1) plus 1 new record.I need a statement that will only add the 1 new record to the mastertable and leave the other 5 fields alone.I have a composite key that prevents duplicate addition.My original idea was to have a master table(Day 1) and a dailytable(Day 2) and do some kind of join that would give we only therecords in Day 2 that do not appear in Day 1 using the Composite key.This has not worked.Any ideas?
View 2 Replies View RelatedI have a scenario,
We have equipment table which stores Equipment_ID,Code,Parent_Id etc..for each Equipment_ID there is a Parent_Id. The PK is Equipment_ID Now i want to select the Code for the Parent_Id which also sits in the same table. All the Parent_Id's also are Equipment_ID's.
Equipment table looks like :
Equipment_ID Code DescriptionTreeLevelParent_Id
6132 S2611aaa 4 6130
11165 V2546bbb 3 1022
15211 PF_EUccc 5 15192
39539 VP266ddd 4 35200
5696 KA273eee 3 3215
39307 VM2611fff 4 35163
39398 IK264ggg 4 35177
There is another table for Equipment_Tree which has got Equipment_Tree_ID,Parent_Id and Equipment_ID does not has the Code here.
Select query where i need to select the Code for all Parent_Id's.
Hi,
Can anyone tell me is it possible to put static data (a string) inside details section of a table?
I've tried to find some more pieces of information in the Report Definition Language Specification, but to no avail.
When I put some strings as a detail's cells values they are displayed in the preview window in VS.
But thay are invisible when I open my report using ReportViewer in my app.
So does it means that the only accepted value of a cell within the details section of a table is a name of the field from DataSet (something like =Fields!FieldName.Value) ?
Regards,
Stan
I've been scratching my head over this one.
It should be pretty easy to do, but I keep going over the same problem.
I have 3 tables (Suppliers, Categories & Suppliers_To_Categories).
Suppliers_To_Categories contains which suppliers are related to which categories.
The set-up is so (simplified):
Suppliers
id
1 Supplier 1
2 Supplier 2
3 Supplier 3
4 Supplier 4
Categories
id
1 Category A
2 Category B
3 Category C
4 Category D
Suppliers_To_Categories
id supplierId categoryId
1 1 1
1 1 2
1 1 3
2 2 3
3 3 1
3 3 3
What I am after is an SQL statement which tells me for a particular category, which suppliers ARE NOT related to it, so they can be assigned.
E.g.
Category D has Suppliers 1-4 unassigned
Category C has Supplier 4 unassigned
Category B has 2, 3, 4 unassigned
Category A has Supplier 2, 4 unassigned etc...
I've got the other side of the SQL statement which tells me which suppliers are assigned to a category (see below):
SELECT Suppliers.*
FROM CatToSupplier INNER JOIN
Suppliers ON CatToSupplier.supplierId = Suppliers.supplierId
WHERE (CatToSupplier.CatId = @CatId)
This is probably really easy, but I've become unstuck!
i have 2 tables (both containing the same column names/datatypes), say table1 and table2.. table1 is the most recent, but some rows were deleted on accident.. table2 was a backup that has all the data we need, but some of it is old, so what i want to do is overwrrite the rows in table 2 that also exist in table 1 with the table 1 rows, but the rows in table 2 that do not exist in table one, leave those as is.. both tables have a primary key, user_id.
any ideas on how i could do this easily?
thanks
I created am inventory table with few columns say, Servername, version, patching details, etc
I want a tracking of the table.
Let's say people are asked to modify the base table and I want a complete capture of the details modified and the session of the user ( ) who (system_user) is actually modifying the details.
Hi All,
I have 2 tables People & PeopleCosts.
PeopleID in People Table is the primarykey and foreign Key in PeopleCosts Table. PeopleID is an autonumber
The major fields in People Table are PeopleID | MajorVersion | SubVersion. I want to create a new copy of data for existing subversion (say from sub version 1 to 2) in the same table. when the new data is copied my PeopleID is getting incremented and how to copy the related data in the other table (PeopleCosts Table) with the new set of PeopleIDs..
Kindly help. thanks in advance.
Myl
Hi all, I am working with SQL Server2000 and I have done a horrible thing here.
We here have an script that delete all data from all user tables of a database, and I run it in the master DATABASE.
As we don't made backups of this database, now somethings of the database aren't working.
Here is the script:
declare @table_name sysname
declare @alter_table_statement varchar(256)
declare @delete_statement varchar(256)
-- definindo o cursor...
declare table_name_cursor cursor local fast_forward for
select
name
from
sysobjects
where
xtype = 'U'
and
name <> 'dtproperties'
-- desligando os vínculos...
open table_name_cursor
fetch next from table_name_cursor into @table_name
select @alter_table_statement = 'alter table ' + ltrim(rtrim(@table_name)) + ' nocheck constraint all'
exec(@alter_table_statement)
while @@Fetch_Status = 0
begin
fetch next from table_name_cursor into @table_name
select @alter_table_statement = 'alter table ' + ltrim(rtrim(@table_name)) + ' nocheck constraint all'
exec(@alter_table_statement)
end
close table_name_cursor
open table_name_cursor
fetch next from table_name_cursor into @table_name
select @delete_statement = 'delete from ' + ltrim(rtrim(@table_name))
exec(@delete_statement)
while @@Fetch_Status = 0
begin
fetch next from table_name_cursor into @table_name
select @delete_statement = 'delete from ' + ltrim(rtrim(@table_name))
exec(@delete_statement)
end
close table_name_cursor
open table_name_cursor
fetch next from table_name_cursor into @table_name
select @alter_table_statement = 'alter table ' + ltrim(rtrim(@table_name)) + ' check constraint all'
exec(@alter_table_statement)
while @@Fetch_Status = 0
begin
fetch next from table_name_cursor into @table_name
select @alter_table_statement = 'alter table ' + ltrim(rtrim(@table_name)) + ' check constraint all'
exec(@alter_table_statement)
end
close table_name_cursor
deallocate table_name_cursor
I have tried to restore master table with the restore function, but it doesn't work. When I try to do this I received a message informing that it can't copy the data because one file was in use. The server was in a single user mode.
Is there anyway to recover the data that I have lost?
Perhaps this task is not for MDS.... But another tool for rapid development & startUp - we don't have. And nevertheless....
We created table managers_plan in MDS :
year
month
id_manager (domain attr)
POS (domain attr)
plan_sum_USD
plan_unit
----------------------------
Entities:
Managers ~ 800 records
POS ~ 100 000 records
managers_plan Total records for
1 year = 100K x 12 = 1 200 000
managers_plan - table partitions
- Will bemade ?
I am newbie to MDS of SQL,want to know how we can transfer tables from two different SQL Databases to MDS.Suggest me the steps to proceed with any examples.
View 2 Replies View RelatedI have two table with some identical fields and I am trying to populate one of the tables with a row that has been selected from the other table.Is there some standard code that I can use to take the selected row and input the data into the appropriate fields in the other table?
View 3 Replies View RelatedLet us say that I have a table with two sets of values as such:Item Extension--- ----100023 1100025 1100025 2100028 1100029 1100029 2100029 3[...]Note that a given item number can appear multiple times if it has morethan one extension number.I want to be able to select the first N entries as grouped by itemnumber. So if N = 3, that would return 100023, 100025 and 10028 withtheir associated extentions. I would also like to be able to selectsay, the 2nd through 9th entries grouped by item number.I've tried something like this to give me row counts:select rank = count(1), t1.item, t1.extensionfrom ItemTable t1 inner join itemTable t2on t1.item >= t2.itemgroup by t1.item, t1.extensionorder by rankBut that gives me this sort of result:Rank Item Extension---- --- ----1 100023 13 100025 13 100025 24 100028 17 100029 17 100029 27 100029 3[...]Any suggestions would be welcome.
View 2 Replies View RelatedI got a File with sales orders and their details.
Step 1. First I am filtering the Sales Order information and inserting it in my Sales Orders table.
Step 2.Then I am filtering the details from the sales Order and inserting them in the respective table.
My Problem is that the Sales Order File does not contain the Sales Order key (ID), this is generated by the SQL Server. How can get it in order to use it in the second step? I need it because it is a foreign key in the details table.
Any Idea?
Select top 100 * from tab1 order by col1 ---gives me the top 100 order by col1
Select top 200 * from tab1 order by col1 ---gives me the top 200 order by col1
How do I select the top 100 to 200
I have two db-tables:
"TAB01" with structure:
CREATE TABLE TAB01 (
ID int NOT NULL,
Name varchar(64) DEFAULT NULL,
PRIMARY KEY (ID)
)
It contains only name of some object.And "TAB02" with structure:
CREATE TABLE TAB02 (
ID int NOT NULL,
TAB01_ID int NOT NULL,
PositionA int,
PositionB int,
StringVal varchar(32) DEFAULT NULL,
PRIMARY KEY (ID)
)
which contains following data:
ID|TAB01_ID|PositionA|PositionB|StringVal|
1|1|1|1|A|
2|1|1|2|B|
3|1|1|3|C|
[code].....
and I need to find a sequence of values in column "StringVal", for example: A B.I look for a suitable SELECT, that returns (in this case) following result:
TAB02.ID|TAB01.Name|TAB02.PositionA|TAB02.PositionB|
1|ABC|1|1|
10|DEF|2|3|
25|JKL|4|3|
30|MNO|5|1|
36|MNO|5|7|
41|PQR|6|4|
46|STU|7|2|
Data inserted in "TAB02" represents real-life structure:
ID|Name|P01|P02|P03|...|Pxy|
1|ABC|A|B|C|D|E|A|D|
2|DEF|J|K|A|B|F|F|B|
3|GHI|B|A|C|A|F|G|A|F|
4|JKL|F|E|A|B|B|E|E|
5|MNO|A|B|C|B|A|D|A|B|
6|PQR|D|E|F|A|B|A|F|
7|STU|A|A|B|B|E|B|E|
Hi,
I have a table of row IDs, order numbers, and status. Status is a value of 1 or 2. Some rows only exist as a 1, some only as a 2, and some exist twice -- once as 1 and once as 2. I want to select orders that are 2 as well as rows that are 1 but do not also exist in the table as 2. Ultimately I will only see each order number once. Is there a better/faster way than doing multiple selects and comparing them to each other?
Example data:
RowID OrderNum Status
1 101 1
2 102 1
3 102 2
4 103 2
Desired results when selecting ID and ordernum from that table:
1 101
3 102
4 103
Thanks.
I have SQL mobile database. Due to the size and speed, I would like to select only rows that start with a certain set of alphabets. Is there a way to do this? I just don't want to query everything and use only a small number of rows since the recources on the device is limited.
Thanks.
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?
I am new to MDM profisee tool and currently working for Addres verification project for my organization.I wanted to clear my doubts here about Unique Identifer in Stage table and how it works.. Here what i understand till now:
Step 1) I created an Entity using MDM profisee UI and it generated a stage table in MDS database called stg.Address_leaf
Step 2) I have loaded data from external source to MDS stage table using ETL and passed Import type as 2 and Import status id as 0
Step 3) I have run store procudure system generated something stg.udp_Address_leaf to load the model and passed the version name as Version_1, Log flag as '1' and Batch tag as 'Address'
Now my below are my questions:
1) What is the field i can use in MDS stage table to populate my unique intifier value coming from source? (lets say Address_Id is my Unique value for all the records coming from source)
2) Where/how Unique Identifier is useful in this process? Will this be useful in next time load from stage to Model?
3) If i truncate and load my MDS stage table in next run and few earlier records has been updated how it will update those records in Model? will this process (code present in SP) recognize by Unique Identier column present in MDS stage table?
I have a very large table , and from that table I need just 2 records with column1 = 'A' and column1 = 'B' .
Here I don't think if I can not use OR or IN or Case operators because I need exactly 2 records not more.
Hi,
I would like to know how to add SELECT row to a table variable. It's not for my SELECT syntax(code following is just an ugly example) that I want help it's for the use of table variable.
Your help will greatly appreciate!!!
ex :
DECLARE @MyTestVar table (
idTest int NOT NULL,
anotherColumn int NOT NULL)
SET @MyTestVar = (SELECT idTest, anotherColumn FROM tTest)-- This cause an error :-- Must declare the variable '@MyTestVar'. ???? What?
In Master tabel i have these date datas
2015-05-10
2015-05-11
2015-05-12
SO when i try to load from Master table to parent and child table i am using using expresssion like
select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ON
A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER AND
A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN
WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE()) and convert(date,B.Ftpdate) = convert(date,getdate()) ;
If i use this Expression i am getting the current system date data's only from Master table to parent and child tables.
My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and child tables.
If i run this expression to remote server i cannot change the system date in server.
while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.
What is the Expression on which ever date i am trying load in the master table same date need to loaded in Parent and child table without changing the system Date.
In SQL Server 2005,here are two tables, created by the following SQL Statements:
CREATE TABLE student(
ID CHAR(6) PRIMARY KEY,
NAME VARCHAR(10),
AGE INT
);
CREATE TABLE score(
ID CHAR(6) PRIMARY KEY,
SCORE INT,
FOREIGN KEY(ID) REFERENCES student(ID)
);
For the length of Column ID is not enough, So I want to alter its length.The alter statement is:
ALTER TABLE student ALTER COLUMN ID CHAR(20)
For the table student is referenced by table score, the alter statement can not alter the column of the table student, and the SQL Server DBMS give the errors.
But, I can manually alter the length of the column ID in SQL SERVER Management Studio. How to alter column length of the master table(student) along with the slave table(score)?
Thanks!
Hi,
Is this anyway to finding updated/ deleted recored using anyother data flow transformation tasks without using sql task.
Can find the new records using merge join task.
Is there better way to merge master table using staging table?
Thanks in advance.
DF.
Now i have master Table for a device Utility. There is a attribute called "Device Type " in the table. Every Device Type has specific Device Attributes associate with it . Now attribute of Diffrent Device type are stored in Different Tables. Now when i select a particular value of Device Type ( lets say Type 1 or TYPE 2 ... ) then the table with has the attribute associated with particuter device type only has to be selected .
So how can I do this ???
How to form a realtion between the tables,... ????
hi
I want to do a "bulk" insert and then get the id (primary key) of the inseret rows:
insert into table1 select * from table2
If I get the value of the @@identity, I always get the last inserted record.
Any idea how to get the ids of all inserted values?
Thx
Olivier
I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.
What I tried, but what returns NULL for purchasedamount:
SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,*
FROM giftregistryitems gi
LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id
WHERE gi.registryid=2
How can I achieve what I need?
Here are my table definitions and data:
/****** Object: Table [dbo].[giftregistryitems] Script Date: 02-05-15 22:37:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[giftregistryitems](
[id] [int] IDENTITY(1,1) NOT NULL,
[code].....
I have a table in which each record has a initial date and a final date. I would like to create I query that gives me one row for each month between the initial date and the final date. It would be something like this:
Original:
Product|price|initial_date|final_date
A|20.50|2014-08-10|2014-10-01
B|50|2015-01-15|2015-02-20
Resulting view:
A|20.5|2014-08-01
A|20.5|2014-09-01
A|20.5|2014-10-01
B|50|2015-01-01
B|50|2015-02-01
I would like to do that, because these dates correspond to the time in which the products are in possession of sellers, so I would to use this resulting query to generate a pivot chart in Excel to illustrate the value of the goods that are with our sellers in each month.
Is it possible to do it? I think I could do that direct in VBA, but I think that maybe it would be faster if I could do it directly in SQL.
By the way, I am using MS SQL Server and SQL Server Manegement Studio 2012.
I need to randomly order the selected rows from my table. Is this better to do on the Application level or in a stored procedure using "orderby NewID()"?
Which is faster? There will be about 100 rows returned with 10 columns.
I am using SQL SERVER 2008R2, not Denali, so I cannot use OFFSET FETCH Clause.
In my stored procedure, I am doing a SELECT INTO #tblTemp FROM... Working fine. This resultset is going to be used in an SSIS package which will generate a pipe-delimited .txt file... Working fine.
For recoverability sake, I am trying to throttle back on the commit chunks to 1000 rows per commit until there are no more rows. I am trying to avoid large rollbacks.
Q: Am I supposed to handle the transactions (begin/commit/rollback/end trans) when the records are being inserted into the temp table? Or when they are being selected form the temp table?
Q: Or can I handle this in my SSIS package for a flat file destination? I don't see option for a flat file destination like I do for an OLE DB Destination (like Rows per batch, Maximum insert commit size).