T-SQL (SS2K8) :: Combining Keys To Create A Master List?

Mar 5, 2014

I am struggling trying to clean some data and identify duplicate records. I used fuzzy grouping in SSIS and provided back a series of groups. The issue is some of the individual records can appear in multiple groups (so in reality the groups should be combined). This is best explained with an example:

Original Data
key1 key2
647942600014
647942285437
2324662490640
2324662285437
2324662066128
2222 2285437
2222 1111111
9999 1111111
9999 2222222

Should look like:

22222600014
22222285437
22222490640
22222066128
22221111111
22222222222

I only choose 2222 as the surviving key because it was the smallest number. I really do not care which number remains as long as it is the same across.

I tried playing with self joins between the tables but have had no success.

I am using Sql Server 2008 and the number of records could 500K to 1MM.

View 2 Replies


ADVERTISEMENT

SQL Server 2012 :: Combining Master And Detail Records In New Table

Sep 4, 2014

CREATE TABLE DHS(CUSTOMERNBR VARCHAR(20), CONTRACT VARCHAR(20), SUBCONTRACT VARCHAR(20) , STARTDATE DATETIME, ENDDATE DATETIME, EFLAG VARCHAR(20), HFLAG VARCHAR(20))

The data which will be going into this table is from two table which have a 1 to many relationship:

Here is the 1 side and data:

CREATE TABLE CUSTOMERS(
CUSTOMERNBR VARCHAR(20),
CONTRACT VARCHAR(20),
SUBCONTRACT VARCHAR(20),
STARTDATE DATETIME,
ENDDATE DATETIME DEFAULT '12/31/2099')

[Code] ....

Here is the Many side and data:

CREATE TABLE FLAGS(CUSTOMERNBR VARCHAR(20), FLAGCODE VARCHAR(20), STARTDATE DATETIME, ENDDATE DATETIME DEFAULT '12/31/2099')
INSERT INTO FLAGS(CUSTOMERNBR, FLAGCODE, STARTDATE, ENDDATE) VALUES('10001000101', 'H', '02/01/2014', '03/31/2014')

[Code] ....

The CUSTOMERS table holds the record date span into which the FLAGS table records have to "fit". In no case will the date spans from the FLAGS table fall outside the STARTDATE - ENDDATE span in the CUSTOMERS table for any CUSTOMERNBR. Here is an example of the final expected output in the DHS table from combining records in the CUSTOMERS and FLAGS tables:

CUSTOMERNBRCONTRACTSUBCONTRACTSTARTDATEENDDATEEFLAGHFLAG
10001000101A910400801/01/201401/31/2014
10001000101A910400802/01/201403/21/2014H
10001000101A910400804/01/201404/30/2014
10001000101A910400805/01/201405/31/2014H
10001000101A910400806/01/201412/31/2099E
10001000102A555500101/01/201403/31/2014E
10001000102A555500104/01/201404/30/2014EH
10001000102A555500105/01/201406/30/2014E
10001000102A555500107/01/201412/31/2099
10001000103A666600401/01/201410/01/2014

View 1 Replies View Related

Max Number Of Database Master Keys?

Mar 9, 2007

Is there max number of Database Master Keys that can be used in a single instance of SQL Server 2005?

If I have 12 unique client databases that all need a unique Database Master Key, can this be accomplished?

Thanks ALL!



View 6 Replies View Related

What Is The System View For Master Keys

Nov 17, 2006

What system view do i use to determine is a database has a master key?



Thanks,



Ed

View 1 Replies View Related

SQL Server, Master Detail Without Foreign Keys

Mar 27, 2007

As part of a project, I'm not allowed to use foreign keys, well can't.   But I have a problem with this master detail relationship (and a very simple one too).
Tables to begin with:
Invoice:
invoiceid : bigint indentity autoincrement primarykey
Detail:
invoiceid: bigint  -  detailnumber:bigint  -  desc : char20
Okay - I want to set these two tables up such than when I create a new invoice, and subsequently get the new invoiceid, and I start to add detail records to the detail table, I want the detailnumber to autoincrement when I do an SQL Insert.  Right now I have detailnumber as the primary key, so when you view the detail records, it looks like:
(invoiceid, detailnumber, desc) 1,1,text - 1,2,text - 1,3,text - 2,4, text on another invoice - 3,5, text
I want it to work like:
(invoiceid, detailnumber, desc) 1,1,text - 1,2,text - 1,3,text - 2,1, text on another invoice - 3,1, text
where the invoiceid and the detail number make up the primary key.
I seem to recall you could do this is PHP before it had foreign keys & such, and I'm completely drawing a blank on this.  BTW - I'm using enterprise manager to set this up, but I can use QA with a sample script to acheive the same.
I realize this may be (and probably is) off-topic here, but I can't figure out where to ask.
Thanks in advance.
 
 
 
 
 

View 3 Replies View Related

Combining Like Rows If More Than One UserID In The List

Jun 6, 2014

I have the following query:

Code:
SELECT DISTINCT [WL].[Id]
,[WL].[UserId]
,[WL].[DIF]
,[WL].[MW]
,[WL].[Notes]
,[WL].[WDate]

[Code] ....

And the error i get is:

> Column 'Wsite.dbo.WLog.Id' is invalid in the select
> list because it is not contained in either an aggregate function or
> the GROUP BY clause.

What I am wanting to do is just conbine the data if there are more than one **UserID** in the list.

As an example:

Code:
Id | UserId | .... | Id | UserName | SLength | ....
5843| 99304 | .... | 99304| Bob Barker | 14 | ....
5844| 06300 | .... | 06300| Dean Martin | 104 | ....
5845| 99304 | .... | 99304| Bob Barker | 8 | ....
5846| 99304 | .... | 99304| Bob Barker | 11 | ....
5847| 7699 | .... | 7699 | John Doe | 0 | ....

So it should look like this:

Code:
Id | UserId | .... | Id | UserName | SLength | ....
5843| 99304 | .... | 99304| Bob Barker | 33 | ....
5844| 06300 | .... | 06300| Dean Martin | 104 | ....
5847| 7699 | .... | 7699 | John Doe | 0 | ....

Notice that Bob Barker's SLength was combined (14+8+11=33).

View 1 Replies View Related

Set Custom Identity Keys For Master Tables(auto-incrementing):

Jun 17, 2007

I need some help for designing the IDs / Primary keys for some master tables in my database. Following are master tables. Client_Master, Buyer_Master & Seller_Master; I want to set Client_Id, Buyer_Id & Seller_Id as their respective primary keys and they should have following properties

Client_Id :- a) should be auto-incrementing value, b) unique & c)should be of the format – CLXXXXXX, where “CL” {Constant start characters} & “X” {any number 0-9}
Similarly::
Buyer_Id :- BYXXXXXX
Seller_Id :- SLXXXXXX

We are implementing the database in MS-SQL 2005 & MySQL

Can anyone help me find a solution to this, especially in MS-SQL.

View 2 Replies View Related

T-SQL (SS2K8) :: Combining Multiple Rows Into One Row Per Employee

Apr 3, 2014

I'm working on a project where I need to retrieve employees data and then combine the data into single row per employee.

Sample Data:

WITH SampleData (PERSON, [DATA], [FIELD]) AS
(
SELECT 1234,'04/02/2014','Date'
UNION ALL SELECT 1234,'123','Department'
UNION ALL SELECT 1234,80.0,'Rate'
)
SELECT *
FROM SampleData;

The results from the above are as follows:

PERSONDATA FIELD
123404/02/2014Date
1234123 Department
123480.0 Rate

The desired results would be:

PERSONDate Department Rate
123404/02/2014 123 80.0

View 7 Replies View Related

T-SQL (SS2K8) :: Combining Result In Column Level

May 14, 2014

Below is my sample table and data

With Item as(
Select 1 as ItemId,'ItemName1' as ItemName,100 as position union all
Select 2 as ItemId,'ItemName2' as ItemName,200 as position union all
Select 3 as ItemId,'ItemName3' as ItemName,300 as position union all
Select 4 as ItemId,'ItemName4' as ItemName,400 as position union all
Select 5 as ItemId,'ItemName5' as ItemName,500 as position union all
Select 6 as ItemId,'ItemName6' as ItemName,600 as position union all
Select 7 as ItemId,'ItemName7' as ItemName,700 as position),

Mapping as (
Select 1 as Parent, 2 as child union all
Select 1 as Parent, 3 as child union all
Select 1 as Parent, 4 as child union all
Select 5 as Parent, 6 as child union all
Select 5 as Parent, 7 as child )Expected Result:

ParentItemIdParentItemNameParentpositionChildItemIdChildItemNameChildposition
1ItemName11002ItemName2200
1ItemName11003ItemName3300
1ItemName11004ItemName4400
5ItemName55006ItemName6600
5ItemName55007ItemName7700

I was thinking to achieve using union all but if i use union all it will combine the result in rows level. but i need in column level.

View 5 Replies View Related

List Of Keys, Indexes

Oct 29, 2005

Hello,
 
I am new in SQL Server, I have to deal with this big database with many tables, is there any way I can get a list of all primary keys , foreign keys and indexes and on all tables?
 
Thanks,

View 1 Replies View Related

How To List Tables With Primary Keys

Jun 9, 2007

Hello,We imported a bunch of tables from a database and realized that theprimary keys weren't copied to the destination db. In order to re-create the keys, we need to know which tables have them. Is there acommand that I can use (on the source db) to find out which tablescontain primary keys? The db has hundreds of tables and I'd rather notgo through each one to see which has a primary key.Also, for future reference, is there a way to include the primary keyon an import?Thanks,Peps

View 2 Replies View Related

List Primary And Foreign Keys

Jan 10, 2006

I have a DB with 100 tables. I was wondering if anybody knows a quick way to list primary and foreign key with the column name for all the tables.

Your help would make my life a lot easier

 

thanks

 

View 1 Replies View Related

T-SQL (SS2K8) :: Combining Costs From Across Tables To Populate Temporary Table?

Nov 17, 2014

I have a temporary table (@tblResults) that has 4 columns that need to be populated with a calculation made from columns held within 2 other tables.

Joins
@tblResults tr JOIN dbo.MarketPrice mp
ON tr.Item = mp.Item
AND tr.[Month] = mp.[Month]
AND tr.[Year] = mp.[Year]
AND mp.[Date] BETWEEN tr.LatestStartDate AND tr.PriorEndDate

[code]....

Where the 2 dbo.MarketPrice and dbo.MillDifferentials date fields are NOT equal, the last (chronologically) dbo.MillDifferentials.Diff value should be used (or '0' if no previous value found).

so expected results where @tblResults.Id = 1:

The dbo.MarketPrice.Price value of '2014-10-29' should be combined with the dbo.MillDifferentials.Diff value of '2014-10-06' - this produces the combined Max value of 184.50
The dbo.MarketPrice.Price value of '2014-04-28' should be combined with the dbo.MillDifferentials.Diff value of '2014-04-28'
The dbo.MarketPrice.Price value of '2014-01-22' should be combined with the dbo.MillDifferentials.Diff value of '2014-01-20'
The dbo.MarketPrice.Price value of '2014-01-21' should be combined with the dbo.MillDifferentials.Diff value of '2014-01-20' - this produces the combined Min value of 111.50
The dbo.MarketPrice.Price value of '2014-01-04' should be combined with '0.00' if there is no matching or previous dbo.MillDifferentials.Diff value OR the top 1/max (most recent) dbo.MillDifferentials.Diff value if a record is found before the specified @tblResults.LatestStartDate

View 3 Replies View Related

SQL Server 2012 :: Combining Values Into Comma Separated List - Serialize

Apr 18, 2014

I have a requirement for SSRS where the input has the following structure:

Store NumberStore Owner
542 Jaklin Givargidze
542 Raymond G. Givargidze
557 Hui Juan Lu
557 Tong Yu Lu

but the user would like to see the following:

Store Number

View 1 Replies View Related

SQL Server 2012 :: Combining Values Into Comma Separated List - Serialize?

Apr 18, 2014

I have a requirement for SSRS report where part of the input has the following structure:

Store NumberStore Owner
542 Jaklin Givargidze
542 Raymond G. Givargidze
557 Hui Juan Lu
557 Tong Yu Lu

but the user would like to see the following:

Store Number Store Owner
542 Jaklin Givargidze, Raymond G. Givargidze
557 Hui Juan Lu, Tong Yu Lu

I am sure that this can be coded, just don't know how. I believe that proper term is to "serialize" the values.

View 2 Replies View Related

Creating A Master List

Apr 16, 2008

How can I create a master list of a particular column, in other words is there a SELECT query I can do that would bring back one of every type of? here's an example: calls table -- bring back one of every kind of call_descr thx for any help.

View 1 Replies View Related

List Of User Sp From Master Db

Jun 15, 2006

Is their any sql to find the list of sp created by user's in master db?

View 9 Replies View Related

List All Tables In Master

Jan 23, 2007

Hi,I need to list all the tables in northwind database.I'm using sql server 2000.In query analyser i'm in master database.I don't want to change the database to northwind.How to list all the tables in northwind database when the databasecombobox has master in query analyser.I want to run this query with database name.I won't want to run forcurrent database.Kindly help me to solve the problem.Thanks & Regards,Mani

View 1 Replies View Related

Get Master And Transaction Tables List

Apr 16, 2008

Pls Tell

Me How to get Name of Master and Transaction Table From The DataBase




Yaman

View 3 Replies View Related

T-SQL (SS2K8) :: Copy Master Record And Its Detail Records

Aug 26, 2014

I have been asked to give the users the ability to copy a set of records within the database. The current setup is

Master Table: JSA1
JSAID: (PK, int, not null)
JOBTITLE(nvarchar(200), null)
PlantNumber(int, not null)

Detail Table: tblSteps
STEPID (PK, int, not null)
JSAID (FK, int, not null)
StepNo (int, null)
BasicSteps (nvarchar(200), null)
DBPhoto(varbinary(max), null)

The plant number field is a location based field that the application uses to filter/select data on for the end users. What they want to be able to do is to select a record, select another location from a dropdown list and then click a button that duplicates the master record and the detail records to the new location.

I am thinking that a stored procedure passing the JSAID and new Location number to do it, I am just not sure how to get the new ID when I go to copy the detail records.

View 4 Replies View Related

T-SQL (SS2K8) :: MASTER And MSDB - Defrag Using Standard Alter Index Command?

Oct 10, 2014

On a 2008r2 server, I ran the frag utility against master and msdb and noticed they were severely fragmented.

Is it ok to defrag them using the standard Alter Index command?

View 7 Replies View Related

Create Table With Foreign Keys

May 30, 2007

How do I create a table with a foreign key

View 6 Replies View Related

Create An Indexes/Keys Property With T-SQL

May 19, 2004

is there a function that i can use in a store procedure that allow me to create an Indexes/Keys Property

thanx

View 5 Replies View Related

T-SQL (SS2K8) :: List From 1 To Another Number?

Sep 12, 2015

@BoxesNeeded will change every time it runs. This method works.

declare @i as int,
@BoxesNeeded as int
select @i = 0,
@BoxesNeeded = 15

--drop table #temp

select @i as DDL
into #temp where 1=2
while @BoxesNeeded > @i begin
set @i = @i + 1
insert into #temp
select @i
end
select DDL
from #temp

View 3 Replies View Related

Create Identical Symmetric Keys On Two Servers?

Apr 17, 2015

why my script is not allowing me to DECRYPTBYKEY once I restore my DB from PRODUCTION BACKUP....

-- SET Staging to Single User Mode to be able to RESTORE DB---

-- STEP 1 (Works No Problems Here)

USE master;
GO
ALTER DATABASE Staging
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
RESTORE DATABASE Staging
FROM DISK = 'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBackupMyDBRestore.bak' ;
GO

-- STEP 2 - USE ONLY IF THE ABOVE IS UNSUCCESSFUL ||| FAILURE ****** RESTORE RUN THE FOLLOWING SCRIPT -----

--If the above is successful the DB sets itself back to MULTI_USER

--ALTER DATABASE Staging
--SET MULTI_USER;
--GO
--ALTER DATABASE Staging
--SET READ_WRITE
--GO

-- STEP 3 (Works No Problems Here)

--------------- @@@@@@@@@@@@@@ IMPORTANT UNCOMMIT AND RUN @@@@@@@@@@@@@@@@@@@@@@@@@ MANUAL STEPS ----------------------
-- RBD - Recreate Security ID'S AND PERMISSIONS FOR Stored Procedure EXECUTE RIGHTS, because PASSWORDS are different on lower
-- environments
--USE [Staging]
--GO
--/****** Object: User [WebUser] Script Date: 4/13/2015 11:15:51 AM ******/
--DROP USER [WebUser]

[code]....

View 1 Replies View Related

T-SQL (SS2K8) :: CTE Has More Columns Than Were Specified In Column List

Mar 22, 2014

I get the following error , when I execute my CTE on SQL Server 2008 R2.CTE1 has more columns than were specified in the column list

--SQL CODE IS AS BELOW

WITH CTE1 (AUCTIONID,[Open Time], [Response SLA Broken], [Response SLA Deadline], [Response SLA Actual], [Responsetime SLA Broken Before Transfer], [Resolution SLA Broken], [Resolution SLADeadline], [Resolutiontime SLA Broken

[code]...

View 2 Replies View Related

SQL 2012 :: Create Table With Both Keys As Foreign (error)

Dec 15, 2014

I'm trying to create a table in Microsoft Server Management Studio 2012. The table has two fields which are both foreign keys.

I created the following:

create table tblRoomEquipment(
RoomID nvarchar(8),
EquipmentType nvarchar(1),
foreign key (RoomID) references tblRoom(ID),
foreign key (EquipmentType) references tblEquipment(Type)
)

Both tblRoom and tblEquipment have the red line error which when I highlight say the they both reference an invalid table!

Both tables are there and have primary keys defined as ID & Type. I have searched around and all I could find was that there maybe a permission problem.

View 6 Replies View Related

T-SQL (SS2K8) :: Isolating A List With Pattern Search

Jul 14, 2014

I'm trying to pull up a report of restored databases from our bug tracking software to audit and see if any of them can be taken down, and having some trouble figuring out how to pull the list of databases out of the entire request text, since they usually come in via email. Some sample data is below with the paltry beginning of a solution that I came up with.

WITH bug (BugID, BugComment) AS (
SELECT 1, 'Hello DB_001000, DB_001000, DB_001000 Blick'
UNION ALL
SELECT 2, 'Hi DB_001000 DB_001000 DB_001000 DB_001000 Flick'
UNION ALL
SELECT 3, 'Urgent DB_001000 DB_001000 Glick'

[Code] ....

View 6 Replies View Related

T-SQL (SS2K8) :: List When Both Column Values Are Null

Feb 24, 2015

I have the following tables:

tbl_Person (personID, first_name, lastname)
tbl_student (studentID)
tbl_stupar (personID, StudentID, relationship)
tbl_person_Phone (personID, phone)

I need to list all students who have both parents phone number is null. The parent relationship values 1 and 2 indicates the person is either Mom (value 2) or dad (value 1) of the student. Note: I am using student parent as an example to write my query.

View 4 Replies View Related

T-SQL (SS2K8) :: Query To List Rows Into Columns?

Aug 10, 2015

I have requirement to list each row into column. I have tried with pivot query but unable to get it. I am using SQL Server 2008 database.

Here is the sample data:

CREATE TABLE dbo.test (
action_id numeric,
action VARCHAR(20) NOT NULL,

[Code].....

View 3 Replies View Related

T-SQL (SS2K8) :: Get List Of All The Tables Used In Multiple Scripts

Aug 28, 2015

I am trying to figure out an automated way to get a list of all the tables used in a set of 275 T-SQL scripts. i do not want to go through each script manually, look at all the from clauses and write down the tables used.

I have managed to get all the 275 scripts into a table on SQL Server 2008 R2 database. one column per each script, so there are 275 rows in the table.

FYI: each query can have multiple result sets, so multiple from clauses can exist.

For Example:

Query 1 = SELECT * FROM Table1 a
INNER JOIN Table2 b ON b.Field = a.Field
LEFT OUTER JOIN Table3 c ON c.Filed = b.Field

UNION ALL

SELECT * FROM Table4 a
INNER JOIN Table5 b ON b.Field = a.Field
LEFT OUTER JOIN Table6 c ON c.Filed = b.Field

Desired Output = Table1, Table2, Table3, Table4, Table5, Table6

View 9 Replies View Related

Workaround CREATE MASTER KEY??

Apr 10, 2008

At some point in time, when I release my code from developemnt to production, somebody will onvoke the SQL Script containg my certificates and symettric keys based on my master key. Unfortunately this seems a bit of a weekness as my SQL SCRIPT contains the CREATE MASTER KEY ENCRYPTION BY PASSWORD stement which has the password itself in clear. (script gets invoked from a command line in a batch script which is all under documeny mangement control). Obviously I would not like my password to be be in clear anywhere - i.e. not in document control nor viewable from whoever invokes the script. What is best pracrice to adopt on this? - encrypt the script file?

Thanks

View 5 Replies View Related

Create Login From Master

Jan 25, 2008

Is there any way I can create a login for a database while the database context is master or not the particular database.
I am trying to do something like this.......
USE [master]
GO

DECLARE @STRSQL VARCHAR(2000),
@DATABASE_NAME VARCHAR(50),
@DB_PATH VARCHAR(200),
@DB_USER VARCHAR(25)
SET @DATABASE_NAME='TDB4x'
SET @DB_PATH='D:Database'
SET @DB_USER='TDB4Test'

DECLARE @SQL_DB varchar(20),
@STRSQL1 varchar(500),
@STRSQL2 varchar(500),
@STRSQL3 varchar(500),
@STRSQL4 varchar(500)

SET @SQL_DB='USE '+@DATABASE_NAME
SET @STRSQL1=(' IF NOT EXISTS (SELECT *
FROM sys.server_principals
WHERE name='''+@DB_USER+''')')
SET @STRSQL2=(' BEGIN
CREATE LOGIN '+@DB_USER+'
WITH PASSWORD=N'''+@DB_USER+''',
DEFAULT_DATABASE=['+@DATABASE_NAME+'],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
END')
SET @STRSQL3=(' EXEC sys.sp_addsrvrolemember @loginame = N'''+@DB_USER+''', @rolename = N''sysadmin''')
SET @STRSQL4=(' ALTER LOGIN ['+@DB_USER+'] DISABLE')
EXEC (@SQL_DB+@STRSQL1+@STRSQL2+@STRSQL3+@STRSQL4)

The login is successfully created, but I cannot find the user in the specific database. Is there any other way this can be accomplished?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved