SQL Server 2008 :: Compare Data Between Identical Table Structure?

Jun 18, 2015

I have two identical table structure with at least 60 or more columns!!

Table A
ID numeric(18, 0) Primary Key,
RefID numeric(18, 0),
CoNum Varchar(15),
PrevCode Varchar(15),
DispCode VArchar(15),
EffDate Date,
Sal numeric(18, 0),
SDeposit numeric(18, 0) Etc....

I need to compare Data between two table and provide result like this!

RefId ID, CoNum, PrevCode, DispCode, EffDate, Sal, SDeposit
1, ,No Match, ,20,5200,0

So condition like. If table A Column Data does not Equal Table B Column data then

If datatype is Date then display Difference of Days.

if Datatype is Numeric then display Difference between value.( it display 0 if doesn't have difference)

if Datatype is Varchar then display No Match word.

View 0 Replies


ADVERTISEMENT

SQL Server 2008 :: Compare Data / Loop Through Dates And Insert Into Table

Sep 21, 2015

I have three tables:

"PaymentsLog"
"DatePeriod"
"PaidOrders"

As per below

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PaymentsLog](

[Code] ....

Is there a way to look at the DatePeriod table and use the StartDtae and EndDate as the periods to be used in the select statement and then cursor through each date between these two dates and then insert the data in to the PaymentsLog table?

View 3 Replies View Related

SQL Server 2008 :: Transmission Table Structure In Database Is Inconsistent

Jun 1, 2015

I have an issue in my production , SQL server 2008 R2 , in Error log it is said “ An error occurred in dialog transmission Error : 9655 , State : 3 and Error : 9736 Severity : 16 State 0

I checked in sysmessages , it is said :” The transmission queue table structure in database is inconsistent. Possible database corruption “

So do you think I need to run DBCC checkdb ? Should I run it after working hours or it is fine within working hours ?

View 9 Replies View Related

SQL Server 2008 :: Compare If Row-Combination Exists In Other Table

Mar 10, 2015

I've the following two tables:

create table #temp1 (name varchar(5), id int)
insert into #temp1 (name, id)
(
select 'a', 5
union
select 'a', 8

[code]....

As a result I would need every name from #temp2, where both searchred id's (5 & 8) from #temp1 are included.In this example i would like to get 'e' as a result, because in #temp2 'e' has the id's 5, 8 and 25.I've tried using cross apply, but cross apply returns every Name that have one of the ids... in this case it would also return 'c'...

Selectdistinct b.name
from(
Selectdistinct name
, id
from#temp1
wherename = 'a'
) as a
cross join(
Selectdistinct name
, id
from#temp2
) as b
wherea.id = b.id

View 3 Replies View Related

SQL Server 2008 :: Compare Dates In Rows Of A Table?

Apr 8, 2015

I have the following information in a table. What I would like to do is pull out all the visits for each customer that are less than 30 days apart.

Customer# VisitDate
9082012-07-28 00:00:00.000
9082013-09-20 00:00:00.000
9082013-12-23 00:00:00.000
9082014-01-10 00:00:00.000
9082014-01-27 00:00:00.000
9082014-02-16 00:00:00.000
9082014-05-21 00:00:00.000
9082014-05-30 00:00:00.000
9082014-10-01 00:00:00.000
9082015-02-28 00:00:00.000
9082015-03-22 00:00:00.000
9272012-02-16 00:00:00.000
9272014-12-14 00:00:00.000
9272014-12-23 00:00:00.000

View 2 Replies View Related

SQL Server 2008 :: Compare Parent And Child Data?

Aug 4, 2015

DECLARE @ParentTable TABLE (ProductID BIGINT,ItemID BIGINT)
DECLARE @ChildParentTable TABLE (ParentID BIGINT,ProductID BIGINT,ItemID BIGINT)

--In Parent table(@ParentTable) there are 4 and 5 items each for product 101 and 102 respectively

INSERT INTO @ParentTable(ProductID,ItemID) VALUES(101,1234),(101,1578),(101,1590),(101,1237)
INSERT INTO @ParentTable(ProductID,ItemID) VALUES(102,5465),(102,5466),(102,5474),(102,5489),(102,6543)

--child products 701 and 901 are derived from parent products(partially) 101 and 102 respectively

INSERT INTO @ChildParentTable(ParentID,ProductID,ItemID) VALUES(101,701,1234),(101,701,1590),(101,701,1578)
INSERT INTO @ChildParentTable(ParentID,ProductID,ItemID) VALUES(901,102,5465),(901,102,5474),(901,102,8976)

--Here what I need is

--For product 701 there is one item missing ie 1237 which exists in its parent 101

--and For product 901 there are 3 items missing ie 5466,5489 and 6543 which exists in its parent 102

--and extra item exists ie 8976 so my result table should lokk like this

/*
ParentProductID ProductIDItemIDIsExtra IsMissing
1017011237 01
1029015466 01
1029015489 01
1029016543 01
1029018976 10
*/

View 1 Replies View Related

How Can I Get The 'entire' Database, Structure And Data, From SQL Server 2008 To SQL Server 2005?

Jun 20, 2008

1) I can't get the 'copy database' function to work from SQL Server 2008 to SQL Server 2005. I connect ok. Everything goes to the last step and then it fails.2) I cant get a SQL server 2008 backup to restore on SQL SEerver 2005 either.
The only way I know that works is to script the creation of all tables then export and import. This does work.
How can I get the 'entire' database, structure and data, from 2008 to 2005?
ThanksSQL newbie.

View 2 Replies View Related

Copy Data In Sql Server Table A To B On Same Server - Identical Schemas

Dec 13, 2006

Greetings,

I have two SQL Server tables on the same server and in the same database. I'll call them table A and table B. They have identical schemas. I need to insert all rows in table A into table B. (Don't laugh - this is just for testing and long run the tables will reside on different servers.)

Can someone please tell me the correct task to use for this and the connection type I need for both the source and destination?

Thanks,

Black Cat Bone

View 9 Replies View Related

SQL Server 2012 :: Moving Identical Data From One Table To Another - Error On Converting To Float

Jun 11, 2014

So I have two tables with similar columns etc, unfortunately the Insert to the History table fails. Noto sure how to format.

Source Table.
CREATE TABLE [dbo].[SKUCURRENT](
[UID] [int] IDENTITY(1,1) NOT NULL,
[SKU] [nvarchar](100) NOT NULL,
[WHSELOC] [nvarchar](50) NOT NULL,
[WEIGHT] [nvarchar](50) NOT NULL,

[Code] ....

View 1 Replies View Related

SQL Script To Compare Two Identical Tables On Two SQL Servers

May 23, 2007

Hi all,
I have two tables A and B on two both tables have similar architectures bout contain some deferent records.

I like to compare them and find and view the differences in records.


Thanks for any help.

Abrahim

View 3 Replies View Related

SQL Server 2012 :: Compare Two Table Data And Insert Changed Field To Third Table

Aug 12, 2014

I want Compare two Table data and insert changed field to the third table ...

View 9 Replies View Related

Structure Compare

Nov 19, 2007

Hi everybody
I want to compare structure of two database
how could I do it?

Thanks
Mokhtari

View 4 Replies View Related

SQL Server 2008 :: How To Compare String

Jun 19, 2015

I need to compare to string in sql

requirement is

Select Reson_id from reason_data where reason='ryete / werewr ddsad '

is there any way without string function? bcz string function take more exaction time

View 9 Replies View Related

How To Compare Structure Of Two Database ?

Oct 3, 2006

I have one databaseand I create new database by copy everything from first db I then alter some column on new database and now I would like to compare two databaseHow can I do it ? 

View 4 Replies View Related

How To Compare Structure Of Two Db Objects?

Feb 8, 2005

Hi,

I would like to compare an object inside a local and remote db and I would like see if any changes has been made to the object structure (table) since the last update.

Thanks

View 2 Replies View Related

SQL Server 2008 :: How To Compare The Date Column

Mar 30, 2015

I need to compare the date column. The date column is 2012-01-22 in this format.

So I need to convert getdate() into this format yyyy-mm-dd.

What is the style number I have to use?

I used the link [URL] but I didn't see this format.

View 5 Replies View Related

SQL Server 2008 :: Inserting Data From Staging Table To Main Table

Feb 23, 2015

I am trying to insert bulk data into main table from staging table in sql server 2012. If any error comes, this total activity is rollbacked. I don't want that to happen. I want to know the records where ever the problem persists, and the rest has to be inserted.

View 2 Replies View Related

SQL Server 2008 :: Compare Up To 9 String Variables For Similarity?

Mar 6, 2015

We're converting to new student info system. Sometimes registrar entered the same school into the schools table but spelled it differently. Trying to find all student assigned transfer credits from the same school but the school name is different. My db shows a max of 9 different schools students have rec'd transfer credits. Spending too much time trying to figure out best way to do it w/o a ton of IF stmts. Looking at Soundex and Difference functions. Still looks like a lot of coding. how to compare up to 9 string variables in sqlserver 2008?

View 2 Replies View Related

Integration Services :: Compare STRUCTURE 2 Tables

Apr 30, 2015

I was wondering if there's a way to compare 1 table with other (in other DB), I mean:

Table1 DB1
ID
NAME
DESCRIPTION

Table1 DB2
ID
NAME
DESCRIPTION
ADDRESS.

And when the query(or other case) gets that the table 1 doesn't contain ADDRESS, alter the table and add this field that is missing.

View 4 Replies View Related

Transact SQL :: Table Structure - Inserting Data From Other Temp Table

Aug 14, 2015

Below is my table structure. And I am inserting data from other temp table.

CREATE TABLE #revf (
[Cusip] [VARCHAR](50) NULL, [sponfID] [VARCHAR](max) NULL, GroupSeries [VARCHAR](max) NULL, [tran] [VARCHAR](max) NULL, [AddDate] [VARCHAR](max) NULL, [SetDate] [VARCHAR](max) NULL, [PoolNumber] [VARCHAR](max) NULL, [Aggregate] [VARCHAR](max) NULL, [Price] [VARCHAR](max) NULL, [NetAmount] [VARCHAR](max) NULL,

[Code] ....

Now in a next step I am deleting the records from #revf table. Please see the delete code below

DELETE
FROM #revf
WHERE fi_gnmaid IN (
SELECT DISTINCT r2.fi_gnmaid
FROM #revf r1, #revf r2

[Code] ...

I don't want to create this #rev table so that i can avoid the delete statement. But data should not affect. Can i rewrite the above as below:

SELECT [Cusip], [sponfID], GroupSeries, [tran], [AddDate], [SetDate], [PoolNumber], [Aggregate], [Price], [NetAmount], [Interest],
[Coupon], [TradeDate], [ReversalDate], [Description], [ImportDate], MAX([fi_gnmaid]) AS Fi_GNMAID, accounttype, [IgnoreFlag], [IgnoreReason], IncludeReversals, DatasetID, [DeloitteTaxComments], [ReconciliationID],

[Code] ....

If my above statement is wrong . Where i can improve here? And actually i am getting 4 rows difference.

View 5 Replies View Related

SQL Server 2008 :: Join Two Tables - Compare Amounts (Rent)

Jun 20, 2015

Refer to my query thread on msdn SQL site, how I can achieve the result.

[URL] .....

View 0 Replies View Related

SQL Server 2008 :: Easy Way To Compare Contents Of Objects Between 2 Different Databases?

Jul 20, 2015

Is there an easy way to compare the contents of objects between 2 different databases? For example, say I had 2 databases, My_DB_1 and My_DB_2, each with a SEC_User table. Say I wanted to do an object-to-object comparison between databases to see if there were any differences. Here's some sample SQL:

use My_DB_1
select * from sys.dm_sql_referencing_entities('dbo.sec_user', 'object')

use My_DB_2
select * from sys.dm_sql_referencing_entities('dbo.sec_user', 'object')

Say that the result sets above both returned a SEC_GetUser sproc object ref. Is there a way to write SQL that will compare the SEC_GetUser sprocs (and other objects in the above rowsets) from both databases? For example, if My_DB_1.SEC_GetUser returns an extra column in the result set than My_DB_2.SEC_GetUser then I'd like my comparison SQL to return a single column "IsDifferent" with SEC_GetUser as a row....

View 1 Replies View Related

SQL Server 2008 :: How To Add A Record When Data Is Not In The Table

Feb 13, 2015

I have a report that summarizes hospital readmissions. Some months may only have a female or male patient that is readmitted but, I want to show both months either way.

create table dbo.Scott_TEST
(
YearMonth char(9),
Gender char(1),
NumOfFemale int,
NumOfMale int

[Code] ....

View 2 Replies View Related

SQL Server 2008 :: Pivot Data In Table?

Jun 24, 2015

MyTable has the following data (Just a sample)

parent | NAme | Checked | contactmethod|Check2 | Other
974198 | Employment | true | Face to Face | true | null
974224 | Other | true | Face to Face | true | skills
974224 | Other | true | Contact | true | skills

I'd like to pivot on "parent"

In a perfect world I'd like to see output like

974198 | Employment | true | Face to Face | true | null

974224 | Other | true | Face to Face, Collateral Contact | true | skills

If there are more than one name or contactmethod for the same parent then they would be strung along with commas

View 1 Replies View Related

SQL Server 2008 :: Update One Row With Data From Different Row In Same Table

Sep 11, 2015

I need to update the Denominator column in one row with the value from the Numerator column in a different row. For example the last row in the table is

c010A92NULL

I need to update the Denominator, which is currently NULL, with the value from the Numerator where the MeasureID=c001 and GroupID=A.

This value is 668 so, the row should look like

c010A92668

create table dbo.TEST
(
MeasureID varchar(10),
GroupID char(1),
Numerator float,
Denominator float
)

[Code] .....

View 7 Replies View Related

Copy Table Structure Only - Not Data

Oct 18, 2007

We have SQL Enterprise Manager (8.0).  Is there any way to Copy a Table from one database to another with only the Structure (design) - not all the Data? 
I can't find any option in the Import Data Wizard that only copies the Table structure.
 Any help is appreciated.  Thanks
 

View 1 Replies View Related

Changing The Structure Of Data In A Table

Feb 20, 2004

I have a table that looks like this:

ID Type
123 Phone
123 Meeting
123 Phone


and I would like the data to look like this

ID phone Meeting
123 2 1


How do I do this?

View 3 Replies View Related

SQL Server 2008 :: Inner Join Database Table With XML Data

Jan 25, 2011

I have a XML data passed on to the stored proc in the following format, and within the stored proc I am accessing the data of xml using the nodes() method

Here is an example of what i am doing

DECLARE @Participants XML
SET @Participants = '<ArrayOfEmployees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Employees EmpID="1" EmpName="abcd" />
<Employees EmpID="2" EmpName="efgh" />
</ArrayOfEmployees >'

SELECT Participants.Node.value('@EmpID', 'INT') AS EmployeeID,
Participants.Node.value('@EmpName', 'VARCHAR(50)') AS EmployeeName
FROM
@Participants.nodes('/ArrayOfEmployees /Employees ') Participants (Node)

the above query produces the following result

EmployeeID EmployeeName
--------------- -----------
1 abcd
2 efgh

How do I join the data coming out from the above query with another table in the database, for example Employee in this case

i.e. somewhat like this (note the following query does not perform the join correctly)

SELECT Participants.Node.value('@EmpID', 'INT') AS EmployeeID,
Participants.Node.value('@EmpName', 'VARCHAR(50)') AS EmployeeName
FROM
@Participants.nodes('/ArrayOfEmployees /Employees ') Participants (Node)
INNER JOIN
Employee EMP ON EmployeeID = EMP .EmployeeID

My desired output after the join would be

EmployeeID EmployeeName Email Home Address
--------------- ----------- --------------- -----------
1 abcd abcd@abcd.com New York
2 efgh efgh@efgh.com Austin

View 8 Replies View Related

SQL Server 2008 :: Bulk Insert Data Into Table

Mar 23, 2015

I want to bulk insert data into a table named scd_event_tab inside a database named rdb.

When I do select * from rdb.dbo.scd_event_tab, i get :

JOB_ID RUN_ONPRIORITYPAYLOADTIMEOUT_INTERVALSTATUSPICKUP_TIMESCD_TYPESCHEDULE_IDDB_ADMIN_LOGIN_REQUIRED_YN

I saved the result into a csv file and then truncated the table. Now, I am trying to bulk insert the data into the table. So I used:

bulk insert
rdb.dbo.scd_event_tab from 'C:userssluintel.ctrdesktopeventtab.csv'
with
(
codepage = 'RAW',
datafiletype = 'native',
fieldterminator = ' ',
keepidentity,
keepnulls
);
go

However, I get this error:

Msg 4867, Level 16, State 1, Line 1
Bulk load data conversion error (overflow) for row 1, column 1 (JOB_ID).
Msg 4866, Level 16, State 5, Line 1

The bulk load failed. The column is too long in the data file for row 1, column 3. Verify that the field terminator and row terminator are specified correctly.

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

View 9 Replies View Related

SQL Server 2008 :: Where Clause - Look For 5 Records Data In A Table

Apr 14, 2015

I have a query needs to look for 5 records data in a table. Basically i need to hardcode. Below is my query which didn't work out.

select BF_ORGN_CD, BF_BDOB_CD, BF_TM_PERD_CD,data
from BF_DATA
WHERE (BF_ORGN_CD,BF_BDOB_CD,BF_TM_PERD_CD) in ***** i guess this is the wrong query****
('A1', 'B1', 'C1')
('A2', 'B2', 'C2')
('A3', 'B3', 'C3')
('A4', 'B4', 'C4')
('A5', 'B5', 'C5')

but if i use the query below it will generate more records than these 5 records

select BF_ORGN_CD, BF_BDOB_CD, BF_TM_PERD_CD,data
from BF_DATA
WHERE (BF_ORGN_CD) in ('A1', 'A2', 'A3', 'A4', 'A5')
and (BF_BDOB_CD) in ('B1', 'B2', 'B3', 'B4', 'B5')
and (BF_TM_PERD_CD) in ('C1', 'C2', 'C3', 'C4', 'C5')

View 4 Replies View Related

SQL Server 2008 :: Creation Of XML File With Data In Table

Jun 1, 2015

I want to create a XML file with data in my table. I have a question about tags.

SELECT
-- Root element attributes
'http://tempuri.org/Form.xsd' AS 'xmlns',
'http://www.w3.org/2001/XMLSchema-instance' AS 'xmlns:xsd',
(
SELECT
-- Creating a default element

[Code] ....

This is my query. When I use 'xmlns' namespace the result is below:

<Form xmlns="http://tempuri.org/Form.xsd" xmlns:xsd="http://www.w3.org/2001/XMLSchema-instance">
<Veri xmlns="">
<Satir>
<Id>1</Id>
</Satir>
</Veri>
</Form>

I dont want to see 'xmlns' in 'Veri' tag. When I use xmlna or another word instead of 'xmlns' the result is below:

<Form xmlns="http://tempuri.org/Form.xsd" xmlna:xsd="http://www.w3.org/2001/XMLSchema-instance">
<Veri>
<Satir>
<Id>1</Id>
</Satir>
</Veri>
</Form>

How can I remove 'xmlns' from 'Veri' tag while using it in 'Form' tag

View 2 Replies View Related

SQL Server 2008 :: Getting Duplicate Data When Connecting To Another Table

Jul 25, 2015

I have the following two tables:

CREATE TABLE [MailBox].[Message](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[SenderId] [bigint] NOT NULL,
[Message] [nvarchar](max) NOT NULL,
[SentDate] [datetime] NOT NULL,
CONSTRAINT [PK_MailBox.Message] PRIMARY KEY CLUSTERED

[Code] ....

I'm building a messaging functionality in to my application, I'm able to insert a message into the database and this message then appears inside the other users inbox. The issue I have it when I click on this message to view the conversation I make a call to the following sp as shown here:

@UserId bigint,
@SenderId bigint
AS
BEGIN
SET NOCOUNT ON;

[Code] .....

The problem with this is I'm trying to connect to the user photos table to return their profile picture, but for some reason even though I have specified IsProfilePic I get all the photos returned, instead it should be two photos, one for the @UserId and the other for the @SenderId, its equivalent to me doing this:

Select *
From [User].[User_Photos]
where (UserId = 1 or UserId = 2) and IsProfilePic = 1

And this returns me the correct information.

View 3 Replies View Related

SQL Server 2008 :: Import Table Data From Production To Development Server

Feb 18, 2015

Production and development servers are on different domains and they do not trust each other. How do I import data from the table t1 from a database db1 in production and load it into table t1 inside database db1 in development?

View 3 Replies View Related







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