Mapping Between Tables

Aug 23, 2014

We have a Item table and a Price table. Structure is mentioned below. An Item need to be matched with the Price table (or vice versa is also fine) and get the Cost from the Price table. The challenge (which I feel :) ) is, mapping or Columns to be looked up between these two tables are not fixed.For an Item, Country, City and Number of Days columns can have a value or can be null. For ex, Paper, US, New York <10 days is one combination which should be matched to the Item table. For Paper, UK, NULL, <5 days is another combination which need to be checked. it is kind of dynamic look up of columns between the two tables.

Price Table
Item Country City Number of Days Cost
Paper US New York <10 100
Paper UK <5 150
Paper Chicago >10 200
Pen China <10 250

Item Table
Item Country City Number of Days
Paper US New York 5
Paper UK London 3
Paper US Chicago 15
Pen China Shangai 5
Paper China Beijing 15
Paper US Chicago 5

View 2 Replies


ADVERTISEMENT

Mapping Fields From Two Tables

Apr 1, 2004

Hi all,

I'm not sure how easy of a task this is, or how to go about it, so I thought I'd ask here. I am doing a major overhaul to my site and am re-programming the backend which involves re-formatting my tables, table names etc.

What I want to do is set up a new table in the format I want for the new site, but still be able to have the live data that is being submitted to the site now, be entered into the new table so that I don't have to shut down the site for days to transfer and reformat data. Example

CURRENT SITE

Table 1
Field1: Name
Field2: Address

NEW SITE

Table 1
Field1: Name
Field2: myAddress
Field3: City

I want to be able to somehow map the two together so that when data is entered into the Name field on the current site, that same name is also inserted into the new site table as well. Mapping like this:

(current site)Name -> (new site)Name
(current site)Address -> (new site)myAddress

The city field would be left unaffected as that data would only be entered once the new site is up and running.

I know I could do this from a stored procedure on the exiting site or by re-writting all of my SQL to insert into both tables, but right now all of the SQL is hardcoded into the pages and I have A LOT of pages. Is there a way to map the tables together from within SQL Server?

Cheers,
Jeff

View 5 Replies View Related

SSIS Parameter Mapping With Oracle Data Type Mapping!

Mar 19, 2008

Hi Friends,

I have a small problem in parameter mapping for Execute SQL Task.
I am using a delete statement with 2 conditions.
Followed by another Execute SQL Task which contains commit statement.

delete from tname where c1 = ? and c2 =?

where c1 is number(4) datatype and c2 is of varchar2(20) datatype in oracle.


The connection manager i am using is ORacle OLE DB provider.
I am passing 2 global variables i.e g_v1 of Int32 and g_v2 of String Type.

In the parameter mapping of the Executing SQL task, i am mapping these 2 variables for
c1 and c2 and changed the datatypes inside parameter mapping as Numeric for c1 and Varchar for c2.

I also set the property as ByPassPrepare = True.

When i am executing the package i getting INVALID NUMBER ERROR.
i believe the SSIS is unable to perform the implict datatype converison.

For the next run, i changed the g_v1 varible datatype to Double and also i changed the parameter mapping for c1 as Doble datatype.
This time it is working fine. I can see the Green signal for the 2 SQL Tasks.

But when i connected to Oracle check the count in the table, the data is not getting deleted.

Also,
I set the property RetainSameConnection = TRUE for oracle connection manager.
I am not able to trace this logical error.

The same is working fine in my local machine.
But i am facing the problem when i deployed the same on the client machine.


Is there any problem with parameter mapping?
What should be equialent Datatype for Oracle NUMBER datatype that should be used inside the SSIS package while declaring the global variable and
inside the parameter mapping.

Any thoughts!

View 5 Replies View Related

Re-Mapping

Feb 18, 2007

Hi;
Can anyone please tell me how to do re-mapping? or give some resources address that I can learn easily?
Thanks.

View 5 Replies View Related

Mapping

Sep 5, 2006

hi! i have two different databases (SQL 2005 and Oracle) and i need to map their tables with one another. how will i do this?

thanks

View 6 Replies View Related

NHibernate Mapping

Jul 20, 2007

 I'm using NHibernate with SQL Server Express Edition and I've been problems with the data type to be defined in the "type" attribute on the "property" tag in the XML file mapping.Has someone worked with that?I need to match the appropriate NHibernate type to the following SQL Server Types: uniqueidentifier;bit;ntext;I have been getting "type mismatch" error when the code is trying access the data. Thanks a lot!     

View 2 Replies View Related

User Mapping

Sep 24, 2007

Hi all, I have to provide user mapping between two databases on the same server so that the tables and content of both the databases can be accessed by each other. I know how to do it using Enterprise Manager, but I have to do it via script as in my asp.net application the databases are created dynamically. If I am not wrong, then "sa" login will be required to execute this script, but no problems as we will be having the "sa" password also. The server is sql server 2005.
 Can anybody please let me know how to do it exactly. Its very urgent. Thanx a lot in advance.

View 2 Replies View Related

Table Mapping

Dec 26, 2007

hi friends..
i want to insert data in sql server2005 table through asp.net
column names of this  table comes from diffrent tables.....
like Rollno--- in my new table....but in old table1 this is like RNO & from old table2 this is R_no
so i want insert this two old diffrent table data into my  new table...but i have problem coz of diffrent column names of old table...
can you help me 4 that...

View 3 Replies View Related

SQL Databases Mapping

Aug 25, 2006

Hello,

I have a requirement to link two databases on the same server. Besides using the microsoft link option is there any other method by which we can link these two databases. Responses will be highly appreciated.

Thanks

View 3 Replies View Related

The Best O/R Mapping Tool

Aug 6, 2007

I'm trying to find out the best O/R mapping tool for .NET. A perfect tool would begin with the database model and would be capable of generating 2 layers: the Domain layer (normal classes) and the Persistence layer (methods for storing and retrieving the domain layer objects from the database.

This tool would also be capable of generating the corresponding Visual Studio solution and projects.

I've already tried NetTiers, but it generates so many layers that the code is very dificult to manage and understand.

So any of you know a tool similar to the described above?

View 1 Replies View Related

T-SQL User Mapping

Apr 29, 2008

I need to get a list of users and databases that a sql login is mapped to. Is there a view or a sp that can get me this information?

View 18 Replies View Related

User Mapping

May 5, 2008

In an event of a system restore from development to production, what would cause the mappings for a database user to be whiped out?

Thank you in advanced for the clarifications.

Al

View 9 Replies View Related

Mapping Several Different Values To One Value

Apr 1, 2008

Hi Guys

I have a table with a column and value like below.







A1JAN

A1FEB

A1MAR

A1APR

A1MAY

A1JUN

A1JLY

A1AUG

A1SEP

A1OCT

A1NOV

A1DEC

A2JAN

A2FEB

A2MAR

A2APR

A2MAY

A2JUN



A2JLY

I want to map all these values to one value like AL AVG

How do i implement the solution so that all those different values map directly to one value.

Thanks.










View 5 Replies View Related

Mapping Documentation

Mar 21, 2007

I need to generate a document with the fields mapping from a DataFlow Task. Anybody knows how to do this?

Know I'm using screenshots from the destination object mapping.

Thanks

View 5 Replies View Related

Changing Mapping

Jun 20, 2007

I read in my record. As we proceed down the pipeline, I want to put kind of record (with length 742) into one path, and a different kind of record (with length 242) into a different path. My problem is that I have (obviously) different fixed width column mappings for the two kinds of records. How can I change the column mapping mid-stream? Can I? Do I need to just write out to flat files and go from there?

Thanks!

Jim Work

View 3 Replies View Related

User Mapping In Sql Server DB

Aug 22, 2006

Hello, I am developping a web application who is connected to SQLServer 2005 via the connection sring as below
<add name="LocalSqlServer" connectionString="Data Source=IBM7;Initial Catalog=Reclamation;Integrated Security=True "
providerName="System.Data.SqlClient" />
I want to know which user (bydefault ) who is connecting to the DB .  and how is mapped this user with user in SQL server?
Thanks in advance  

View 3 Replies View Related

Mapping Of Columns In SqlDataReader

Mar 14, 2007

Hi,I use SqlDataReader to read one row from database and than set some properties to values retrieved like this:string myString = myReader.GetValue(0) // this sets myString to first value in a rowIf, however, I change order of columns returned by stored procedure myString would be set to wrong value. Is there a way to do something like this: string myString = myReader.GetValue["ColumnName"]; 

View 7 Replies View Related

Attaching DB And Mapping Users

Oct 15, 2007

I am trying to attach a db to SQL Server 2005 and assign a User to the DB that can read and write.  I am using one user (CreateDBs) to attach the DB and a second user (TestUser) to do the reading and writing.  I run the following code and the DB is attached but I get the following error when I run the code below.
********************ERROR********************
System.Data.SqlClient.SqlException: Cannot open database "hello2" requested by the login. The login failed.
Login failed for user 'CreateDBs'.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at NewSite.submit_Click(Object sender, EventArgs e) in X:InetpubRMA4NewSite.aspx.vb:line 120
 
********************CODE********************
'Need to attach to SQL Server
Dim objConn As New SqlConnection("Server=Web-testbedSQLExpress;uid=CreateDBs;pwd=ABC123;database=master")
objConn.Open()Dim objCmd As New SqlCommand("EXEC sp_attach_db @dbname = N'" & sitename & "', @filename1 = N'X:sites" & sitename & "Database" & sitename & ".mdf', @filename2 = N'X:sites" & sitename & "Database" & sitename & "_log.ldf'", objConn)
objCmd.CommandType = CommandType.Text
objCmd.ExecuteNonQuery()
objConn.Close()objConn = New SqlConnection("Server=Web-testbedSQLExpress;uid=CreateDBs;pwd=ABC123;database=" & sitename)
objConn.Open()objCmd = New SqlCommand("EXEC sp_addrolemember db_datareader, TestUser", objConn)
objCmd.CommandType = CommandType.Text
objCmd.ExecuteNonQuery()
objConn.Close()objConn = New SqlConnection("Server=Web-testbedSQLExpress;uid=CreateDBs;pwd=ABC123;database=" & sitename)
objConn.Open()objCmd = New SqlCommand("EXEC sp_addrolemember db_datawriter, TestUser", objConn)
objCmd.CommandType = CommandType.Text
objCmd.ExecuteNonQuery()
objConn.Close()
 
It still doesn't work if I use sa as the user.  I know I am missing a step, what is it? 

View 2 Replies View Related

Mapping Columns To Rows

Mar 13, 2008

Hi
I have 2 tables defined as follows:
Table1 = uid, Field1, Field2, Field3 ... Fieldn, FormUID
Table2 = FormUID, Label, Position
When I query Table1 I would like to replace the column name of Field1...Fieldn with the Label from Table2 where the Position = n value of Field lable
e.g. lets say Table2 contains the following
1, customerName, 1
1, customerTitle, 2
1, customerDOB, 3
and Table1 might contain
1, Paul Jones, Mr, 21/09/1987, 1
when I query Table1 I would get
uid = 1, Field1 = Paul Jones, Field2 = Mr, Field3 = 21/09/1987
what I would like to get is
uid = 1, customerName = Paul Jones, customerTitle = Mr, customerDOB = 21/09/1987
I have up to 20 Fieldn columns so need to do this for all columns even if there is no matching columns.
any help would be great
regards
 

View 3 Replies View Related

Data Mapping And Migration

May 28, 2004

Has anyone used DTS packages for migrating old data to a new schema?

If so are there any tutorials on this?

I'd prefer not to do this by hand. ;-)

View 3 Replies View Related

BCP By Mapping A Directory On The DB SERVER

Jul 18, 2000

I have a question regarding BCP. We are doing a BCP operation from one machine to another machine. (i.e) Flat file from the web server are BCP on to the DB server. For this operation we have mounted the FTP directory of the web server on to the DB server. Say G: drive of the DB server is mapped to the Ftp directory of the web server.

Now when we run the BCP we get the following error.

SQLState = 08001, NativeError = 6
Error = [Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL server not found.
SQLState = 01000, NativeError = 53
Warning = [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (Create File()).

The connection is using NAMED PIPES.

Say when we BCP out the above error message comes but the flat file is generated with out any records in it.

Are we missing any parameter settings.

Please help.

View 1 Replies View Related

Mapping Drives---URgent Pls

Jul 13, 2000

Hi ,
We are having 4 sql7 servers. All are in network only.
serverA: drives c,d
serverB: drives c,e
serverC: drives c,d
serverD: drives c,d,f
Now my question i want map all the drives to each other. So that i can use the space wherever available, because in serverA i dont have space but in ServerC have lot of free space.
Can anyone pls tell me in detail how we have to map the drives.
Thanks

--Siva

View 2 Replies View Related

Dynamic Transformation Mapping In DTS

May 11, 2006

Hi,

I need to export data from SQL tables to AS400 files(the SQL table has the
same file name and column names as the file on the AS400) .
I created a DTS that has the following tasks: dynamic properties task, SQL
server connection, transform data task and a other connection(ODBC data
source).
I'm using global variables to dynamically set the source and destination
tables names on the transform data task. The problem is the transformations
are not automatically mapped and I get an error message when the
DTS package is executed with a source and destination that has
different columns than the ones specified in the transformation.

Any ideas or possible workaround would be greatly appreciated.
Thank you very much.

View 4 Replies View Related

T-SQL (SS2K8) :: How To Handle 1 To N Mapping

Jul 15, 2015

In my project I have user and group defined as 1 to 1 relationship, meaning one user can have only one group assigned.

I just received a change request to enable any user could have more than one groups assigned, so I am thinking how to handle this request and want to hear options from this forum, here is my preliminary thought:

1. add a new field (let's say byGroup) in User table, this byGroup is a varchar and value like 2,3,9 indicates the user has privilege to group 2,3,9
2. when doing user related info retrieval function, I need to convert the string 2,3,9 to array and then loop the array to get the right info

View 9 Replies View Related

Mapping Many Values In Column To One Value

Apr 2, 2008

Hi Guys,here is my story.

I have a table with values stored in it and the Code Column contains sets of values that need to be mapped to a single value.For example i want the values ALMW,ARBAC to map to AL ARB and the values ARBIT,ARBOP,ARBSC to map to CU ARB and A1JAN,A1FEB,A1MAR,A1APR,A1MAY,A1JUN should map to AL AVG.

The values under Code are already in a database table and the ones under New Code are the new mappings for the values under code and are not in the database.



This table is referenced by other tables for the Code and i want those references to be done to the New Code instead.How do i modify the existing table or design a new table to preserve the current Codes and also map them to the new Code.

Code New Code
ALMW AL ARB
ARBAC
ARBIT CU ARB
ARBOP
ARBSC
A1JAN AL AVG
A1FEB
A1MAR
A1APR
A1MAY
A1JUN






In god we trust,everything else we test.

View 1 Replies View Related

Mapping Table Columns

Apr 18, 2008

Hi Guys

I have a table that contains codes for commodities.Some of the codes in this table have changed and some of them have not.So now i want to design a solution that enable me to map the new codes in a different mapping table to the old ones in the other table.I also want to retain the old codes because most of the archived data used the old codes.

Where there is no new code, the current code is being retained.How do i design my table and queries so that i can use the new codes as if i was using the old code.I want to select products with a certain code but using the new code and mapping to the old codes or vice versa.

The structure of the data is like this.
Code Name
AA AA
AL Aluminium
ALM ALM
ALT Aluminium in tonnes
AR AR
AUD Australian Dollars
AUJPY AUJPY
CAQ CAQ
CC CC
CCF CCF
CER Carbon Emmission Reduction

The mapping table is like this:
XAA AA
XAL AL
XMA ALM
XAL ALT
XAR AR


In god we trust,everything else we test.

View 8 Replies View Related

Mapping Remaining Coupons

Oct 10, 2013

I have two SQL tables

Coupon and Coupon CouponStore

In Coupon there is a column of CouponID and in CouponStore the same column name.

Also in CouponStore there is a column called StoreID that has the value of each store 1, 2, 3 and 4.

How would I write a script to map any CouponID in the Coupon table to each Store Value that are not already mapped to a StoreID?

View 6 Replies View Related

Rows And Columns Mapping?

Nov 3, 2014

The Source Data is

ID--VAL1--VAL2--VAL3--VAL4--VAL5--VAL6
1---151---- ----- ----- Y----- -----
2---151---- -----Y----- -----Y-----Y
3---152----Y----- ------ ----- ------
4---152---- -----Y------ ----Y------

The Expected Output should be

ID--VAL1--VAL2--VAL3--VAL4--VAL5--VAL6
1--151----- ----Y------Y-----Y-----Y
2--152-----Y-----Y------ -----Y-----

View 1 Replies View Related

Dynamic Column Mapping

Apr 18, 2007

Hi everyone,
So I have to export from a SQL 2005 table to a dBaseIV table using SSIS. Easy enough, however the catch is that the tables being exported will vary. I can send variables to the the package calling the sql table name and creating the DBF file with the same columns on the fly.

The problem is mapping the columns. From what I've been reading there is no way to alter the mapping in a package at runtime. I was hoping that there would be some sort of auto mapping setting that would match on the field names but I guess not. Anybody run in to this issue and have a work around? Thanks in advance....

Scott

View 1 Replies View Related

Script For User Mapping

Sep 24, 2007

Hi all,
I have to provide user mapping between two databases on the same server so that the tables and content of both the databases can be accessed by each other.
I know how to do it using Enterprise Manager, but I have to do it via script as in my asp.net application the databases are created dynamically.
If I am not wrong, then "sa" login will be required to execute this script, but no problems as we will be having the "sa" password also.
Can anybody please let me know how to do it exactly. Its very urgent.
Thanx a lot in advance.

Snehal Bhayani

View 2 Replies View Related

No Mapping Between Account Name And Security ID

Jul 4, 2007

Hi,I created a user account on my active directory service. I then triedto assign a service located on my SQL server to be executed by thisaccount. However, when I try to configure my SQL server service, Iget the following error message:WMI Provider Error"No mapping between account name and security ID was done"Do you know what I am doing wrong?thanks

View 1 Replies View Related

Mapping Table Problem

Oct 1, 2006

HI guys
I need to put multiple coniditions on my table based on some combinations.

Here is my main table-














Practitioner
DHBName
PHOName
Practicename
PractitionerName
Locum
YCount
NCount

4
Canterbury DHB
Partnership Health (Canterbury)-596721
Linwood Avenue Medical Centre
Christine Abbasi
No
0
1

32
Canterbury DHB
Partnership Health (Canterbury)-596721
Felicia House Clinic (Pearson)
Nicci Pavey
No
0
1

197
Canterbury DHB
Partnership Health (Canterbury)-596721
Woodham Road Health Care
Peggy Shelton-Agar
No
0
1

394
Hawkes Bay DHB
Hawkes Bay PHO Limited-587862
The Doctors Napier
John Laird
YES
1
0

549
Canterbury DHB
Partnership Health (Canterbury)-596721
Linwood Avenue Medical Centre
Denise Armstrong
No
0
1

1286
Canterbury DHB
Partnership Health (Canterbury)-596721
Darfield Medical Centre Limited
Katherine Muscroft-Taylor
No
0
1

1501
Counties-Manukau DHB
Procare Network Manukau Limited-573183
Manukau City Health Centre
Caroline Shephard
YES
1
0

1932
Hawkes Bay DHB
Hawkes Bay PHO Limited-587862
Clive Medical Centre Ltd
Karen Irwin
YES
1
0

2226
Canterbury DHB
Partnership Health (Canterbury)-596721
Sumner Health Centre
Anne Scott
No
0
1

3243
Waitemata DHB
Harbour PHO Ltd-610733
Byron Medical
Rex Livingstone Sinclair
No
0
1

3263
Canterbury DHB
Partnership Health (Canterbury)-596721
Sumner Health Centre
Peter Wynn Nicholson
No
0
1

3381
Otago DHB
Otago Southern Region Primary Health Organisation-597004
Dr Visagie's Practice
Fay Young
NO
0
1



Here is my mapping table-










Locum Flag
No count
Yes count
Percentage

No
1
0
1

No
1
1
0.75

Yes
1
1
0.25

No
2
0
0.5

No
3
0
0.3333

No
4
0
0.25

Yes
2
1
0.125

No
2
1
0.75

Yes
0
1
0.75

Yes
0
2
0.375

Yes
0
3
0.25

Yes
0
4
0.1875

I have to add an additional column called 'percentage' in my main table by refering the mapping table.

For ex for a particular practitioner, Locum flag is 'Yes', nocount is 1 and yes count is 1, then percentage should be '0.25'



How should I do this??

View 6 Replies View Related

Mapping Of An Xml Column To Variable

Aug 23, 2006

I have a For Each Loop that iterates over a recordset stored in a variable. One of the columns in the recordset is type xml and I want to map it to a variable using Variable Mappings of the For Each Loop container. I am getting this error:

Error: 0xC001C012 at FELC Loop thru report defs: ForEach Variable Mapping number 4 to variable "User::Parameters_xml" cannot be applied.

I have tried changing the type of the Parameters_xml variable to Object and String, but I get the same error. Any ideas?

View 3 Replies View Related







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