Update Target Table Using Metadata And Source Table

Oct 4, 2013

I'd like to figure out how to use the @FieldDescription table below as an intermediate table between the @SourceData and @Stops data.

declare @Stop table (StopId int, UserField varchar(20))
declare @FieldDescription table (Label varchar(10), ColumnName varchar(10))
declare @UpdateSource table (HasPathway varchar(10))
insert into @Stop (StopId, UserField)
values (1, 'Yes')

[code]...

I want to update @Stop.UserField with thevalue from @UpdateSource where @UpdateSource.HasPathway=@Stop.UserField...but I need to use the @FieldDescription table to determine how to map the columns.

View 3 Replies


ADVERTISEMENT

Create Target Table Dynamically Based On Source Table Schema?

Sep 13, 2005

I’ve got a situation where the columns in a table we’re grabbing from a source database keep changing as we need more information from that database. As new columns are added to the source table, I would like to dynamically look for those new columns and add them to our local database’s schema if new ones exist. We’re dropping and creating our target db table each time right now based on a pre-defined known schema, but what we really want is to drop and recreate it based on a dynamic schema, and then import all of the records from the source table to ours.It looks like a starting point might be EXEC sp_columns_rowset 'tablename' and then creating some kind of dynamic SQL statement based on that. However, I'm hoping someone might have a resource that already handles this that they might be able to steer me towards.Sincerely, 
Bryan Ax

View 9 Replies View Related

Transpose Source Data From A System Via Metadata Lookup Table Into Destination Table

Apr 1, 2014

I am stuck on finding a solution to transpose source data from a system via a metadata look-up table into a destination table. I need a method to transpose/pivot the source data into columns (which are by various data-types). The datatypes for each column are listed in a metadata table.

Source Data Table:

Table Name: Source

SrcID AGE City Date
01 32 London 01-01-2013
02 35 Lagos 02-01-2013
03 36 NY 03-01-2013

Metadata Table:

Table Name:Metadata

MetaID Column_Name Column_type
11 AGE col_integer
22 City col_character
33 Date col_date

Destination table:

The source data to be loaded into the destination table(as shown below):

Table Name: Destination

SrcID MetaID col_int col_char col_date
01 11 32 - -
01 22 - London -
01 33 - - 01-01-2013
02 11 35 - -
02 22 - Lagos -
02 33 - - 02-01-2013
03 11 36 - -
03 22 - NY -
03 33 - - 03-01-2013

View 7 Replies View Related

I Want To Join One Table From The Source To The Other Table In The Target Diff Database

May 1, 2008




Hello I have a Source database and a Target database.

I want to join one table from the source to the other table in the target.

Please can some one write a sql query for this.

i gues its something like

select tablesource.col,tabledest.col
from database..tablesource,database..tabledestination

Ok One more question is where do I execute this Query in which database.. IF at all its possible to this.

View 4 Replies View Related

Row Count Validation Between Source Flat File To Target Database Table

Aug 8, 2006

Hi all,



I am new in SSIS. Anyone know how to valify number of record that I load from csv file to SQL database table?



For example, the source file call product.csv and target table in database named DSS table name PRODUCT. I load data from flat file to table then I need verification if count between source and target not match send e-mail to me.



Thanks.

Grace

View 5 Replies View Related

Issue With The Use Of OLEDB Command Task To Update Db2 Target Table

Dec 6, 2007



I am having a problem on updating data in DB2 target table.

I followed BJ Custard's (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1058272&SiteID=1&mode=1)(thanks amillion!) post and configured OLEDB destination to insert data. But I have to also update or delete data from the target table based on flag from source.

I tried using OLEDB command which uses the OLEDB connection created by following the steps posted in above link.

Trail 1real requirement):
When I used the SQL query:


delete from table where Col1=? and Col2=?

I am unable to map to the parameters. When I click refresh button after writing the query, I get "There is a data source column with no name. Each data source column must have a name." message. Added to before message, there are no parameters to map to.


Trail 2:
When I hard code the parameters :

delete from table1 where Col1='abc' and Col2='xyz'

no parameters will come up, so no mapping. So when I execute the mapping I get the following error:



Error: 0xC0202009 at Load .....................................................: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E00.
Lookup on above error codes show those are related more to target Db2 database.

I am sure some one might have used the OLEDB command task, not only just insert task.

Any help is really appreciated.

View 14 Replies View Related

How To Update The Source Columns'metadata Automatically?

Dec 14, 2006

Take a Dataflow Task, with an OLEDB Source Component and an OLEDB Destination Component in it. The Source component's Source is stored in a SQLQuery variable, and the Destination component's Destination is stored in a TableName variable. The Dataflow Task is put into an For Loop container.

I just want to do one thing:

In the For Loop, everytime it send a new value to the SQLQuery variable and the TableName variable, then I could use it to transfer many tables in a same logic.

My question is: Really, I could send new values to the variables and make it go. But everytime it says "The external metadata column collection is out of synchronization with the data source columns." Because the external metadata was recorded and not be change automatically everytime of Loop.

How to update the source columns'metadata automatically?

View 3 Replies View Related

INSERT New Record Works OK In Local Table, BUT Not If The Target SS DB/table Is In A Different Physical Server

Apr 23, 2008



Hi... I was hoping if someone could share me some thoughts with the issue that I am having at the moment.

Problem: When I run the package in my local machine and update local SS DB/table - new records writes OK in the table. BUT when I changed my destination meaning write record into another physical SS DB/table there is no INSERT data occurs. AND SO when I move/copy over that same package into another server (e.g. server that do not write record earlier) and run it locally IT WORKS fine too.

What I am trying to do is very simple - Add new records in a SS table using SSIS . I only care for new rows and not even changed rows.
Here is my logic -
1. Create Ole DB source to RemoteSERVER - using SELECT stmt
2. I have LoopUp component that will look for NEW records - Directs all rows that don't find match and redirect rows (error output).
3. Since I don't care for any rows that is matched in my lookup - I do nothing or I trash the rows
4. I send the error rows (NEW rows) into OleDB destination

RESULTS when I run the package locally and destination table is also local - WORKS FINE;
But when I run the package locally and destination table is in another Sserver (remote) - now rows is written.

The package is run thru BIDS manually so there is no sucurity restrictions attached to it.

I am not sure what I am missing. And I do not see error in my package either. It is not failing.

Thanks in advance!



View 6 Replies View Related

Could You Tell What's Wrong When I Split Table To The Target Partition Table?

Jan 22, 2007

Could you tell what's wrong when I split table to the target partition table?USE TEST--ADD FILEGROUP---------------------------------------------------------------------ALTER DATABASE TEST ADD FILEGROUP FG_01ALTER DATABASE TEST ADD FILEGROUP FG_02ALTER DATABASE TEST ADD FILEGROUP FG_03--ADD FILE--------------------------------------------------------------------------ALTER DATABASE TEST ADD FILE (NAME = DF_01,FILENAME = 'D:TESTDF_01.ndf',SIZE = 10MB,MAXSIZE = UNLIMITED,FILEGROWTH = 10MB)TO FILEGROUP FG_01ALTER DATABASE TEST ADD FILE (NAME = DF_02,FILENAME = 'D:TESTDF_02.ndf',SIZE = 10MB,MAXSIZE = UNLIMITED,FILEGROWTH = 10MB)TO FILEGROUP FG_02ALTER DATABASE TEST ADD FILE (NAME = DF_03,FILENAME = 'D:TESTDF_03.ndf',SIZE = 10MB,MAXSIZE = UNLIMITED,FILEGROWTH = 10MB)TO FILEGROUP FG_03--CREATE PARTITION FUNCTION---------------------------------------------------------CREATE PARTITION FUNCTION PF_HIS_HTTP_LOG(datetime)AS RANGE LEFT FOR VALUES ('20070101 23:59:59.997','20070102 23:59:59.997')--CREATE PARTITION SCHEME-----------------------------------------------------------CREATE PARTITION SCHEME PS_HIS_HTTP_LOGAS PARTITION PF_HIS_HTTP_LOG TO ( FG_01, FG_02, [PRIMARY])--CREATE PARTITION TABLE -----------------------------------------------------------CREATE TABLE HIS_HTTP_LOG ( USERID varchar(32) , USERIP varchar(15) ,USERPORT numeric(5,0) , OBJECTIP varchar(15) , OBJECTPORT numeric(5,0) , URL varchar(256) , HOST varchar(64) , DN varchar(64) , VISITIME numeric(5,0) , STARTIME datetime , ENDTIME datetime ) ON PS_HIS_HTTP_LOG(STARTIME)--INSERT DATA,PARTITION 1 20070101-------------------------------------------------DECLARE @i intSET @i = 1WHILE @i <= 100BEGININSERT INTO HIS_HTTP_LOG VALUES(CAST(@i AS varchar(32)),'192.168.1.1',5,'202.103.1.57',6,'www.sohu.com',11,CONVERT" target="_blank">http://sina.com.cn','','www.sohu.com',11,CONVERT(datetime,'20070101 13:25:26.100',121),GETDATE())SET @i = @i +1END--INSERT DATA ,PARTITION 2 20070102-------------------------------------------------SET @i = 1WHILE @i <= 200BEGININSERT INTO HIS_HTTP_LOG VALUES(CAST(@i AS varchar(32)),'192.168.1.1',5,'202.103.1.57',6,'www.sohu.com',11,CONVERT" target="_blank">http://sina.com.cn','','www.sohu.com',11,CONVERT(datetime,'20070102 11:25:26.100',121),GETDATE())SET @i = @i +1END--CREATE A TABLE -------------------------------------------------------------------CREATE TABLE TMP_HTTP_LOG( USERID varchar(32) , USERIP varchar(15) ,USERPORT numeric(5,0) , OBJECTIP varchar(15) , OBJECTPORT numeric(5,0) , URL varchar(256) , HOST varchar(64) , DN varchar(64) , VISITIME numeric(5,0) , STARTIME datetime , ENDTIME datetime ) ON FG_03--INSERT DATA TO TMP_HTTP_LOG 20070103-----------------------------------------------DECLARE @i intSET @i = 1WHILE @i <= 400BEGININSERT INTO TMP_HTTP_LOG VALUES(CAST(@i AS varchar(32)),'192.168.1.1',5,'202.103.1.57', 6,'www.sohu.com',11,CONVERT" target="_blank">http://sina.com.cn','','www.sohu.com',11,CONVERT(datetime,'20070103 09:25:26.100',121),GETDATE())SET @i = @i +1END--ADD CONSTRAINT--------------------------------------------------------------------ALTER TABLE TMP_HTTP_LOGWITH CHECKADD CONSTRAINT CK001CHECK (STARTIME >= '20070103 00:00:00.000' AND STARTIME <= '20070103 23:59:59.997')--SPLIT RANGE ,SWITCH DATA----------------------------------------------------------ALTER PARTITION SCHEME PS_HIS_HTTP_LOG NEXT USED FG_03ALTER PARTITION FUNCTION PF_HIS_HTTP_LOG() SPLIT RANGE ('20070103 23:59:59.997')ALTER TABLE TMP_HTTP_LOG SWITCH TO HIS_HTTP_LOG PARTITION 3--==========================================�======================================Why is the error in step of“ALTER TABLE TMP_HTTP_LOG SWITCH TO HIS_HTTP_LOG PARTITION 3�error infomation:message_id 4972,level 16,severity 1ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'TEST.dbo.TMP_HTTP_LOG' allows values that are not allowed by check constraints or partition function on target table 'TEST.dbo.HIS_HTTP_LOG'.Please tell me why ? check constraints ?Thank you very much !

View 1 Replies View Related

Update Or Insert Into SQL Table From Excel Source

Aug 29, 2006

Hi all,



I've had little success gooling/searching for this (so far).



Given a simple spreadsheet:

StoreNumber StoreName

1
UPDStoreName_1

2
UPDStoreName_2

3
UPDStoreName_3

4
NEWStoreName_4



I want to have an SSIS package that will update a table: mystores (storenumber int, storename nvarchar(255))

StoreNumber StoreName

1
StoreName_1

2
StoreName_2

3
StoreName_3

5 StoreName_5



.. what I need to do is insert the new, update the existing and leave the remaining unchanged. i.e. :



StoreNumber StoreName

1
UPDStoreName_1

2
UPDStoreName_2

3
UPDStoreName_3

4
NEWStoreName_4

5 StoreName_5



(the UPD and NEW are added to simplify the example).



Now the default action of an excel source into an ole db destination is
an insert into the table - so PK constraints causes failures.



Now, given that the table is referred to by other table, and is in a
24x7 website, how do I change the SSIS package such that, on a
row-by-row basis, anUpSert (update or insert) is performed?



The only idea I have so far is:



create temp table

insert excel data into temp table

iterate through the table, using if exists ... update else insert logic <-- this to be done in a SP



Isn't there a better way?

View 7 Replies View Related

Update Table From Flat File Source

Aug 31, 2006



Hi all,

I'm trying to work out how to update a table from a flat file source.

I could use the simple way of using OLE DB Command with

UPDATE <tblName> SET Column1 = ?, Column2 = ?, <etc> WHERE ID = ?

then using the mapping to join the parameter fields.

However, this (maybe!) a slow process when you have potential 500k+ records in the source file (and approx 150 files to process!)

What I would like to do is the following statement but I'm not sure if this is possible?

UPDATE <tblName> SET Column1 = sourceColumn1, Column2 = sourceColumn1, <etc> FROM <sourcefile> WHERE ID = sourceID

I've also thought about importing the sourcefile into a Recordset Destination and trying to use the variable in the SQLCommand but I'm not sure how to do this...

Does anybody have any ideas on how this could work or an alternative solution?

Thanks in advance....

View 4 Replies View Related

Loop Through A Table Which Has Table Metadata

Feb 18, 2015

I am trying to loop through a table which has table metadata and create a:

- 'STORED PROCEDURE'
- This will SELECT all the data in the table and add a hashed column at the end
- Each table has a unique ID
-

Instead of a cursor would there be a set-based approach to achieving this?

METADATA TABLE :

IDTableNameColumnNameHashByteCalculation
111dbo.TableAColA CAST(ISNULL(LEFT(CONVERT(VARCHARColA, 120), 10),'NA') AS varchar) + '|' +
111dbo.TableAColBCAST(ISNULL(LEFT(CONVERT(VARCHAR,ColB, 120), 10),'NA') AS varchar) + '|' +
111dbo.TableAColCISNULL(ColC,'NA') + '|' +
111dbo.TableAColDISNULL(ColD,'NA') + '|' +
222dbo.TableBColAACAST(ISNULL(LEFT(CONVERT(VARCHAR,ColAA, 120), 10),'NA') AS varchar) + '|' +
222dbo.TableBColBBISNULL(ColBB,'NA') + '|' +
222dbo.TableBColCCISNULL(ColCC,'NA') + '|' +

From the above data I want to generate:

SELECT
ColA
,ColB
,ColC
,ColD
, (CAST(ISNULL(LEFT(CONVERT(VARCHARColA, 120), 10),'NA') AS varchar) + '|' +

[Code] ....

View 2 Replies View Related

Integration Services :: Metadata Change In Source Disturbs All Configuration Of Source

Jun 18, 2015

I have a got a package with source as sql table which has got 50 columns. We are using only 10 columns out of this. Recently one column name has changed and thus throws error invalid mapping. When I open the source to do the changes noticed that all the colums are prselected now and also the datatypes got changed to default ( I had changed the datatypes as per my requirement while i developed). So now I had to select required columns from source and redo the datatype changes in advanced editor.Is there any option which doesnt disturb this settings and we just need to correct the mapping alone. 

View 4 Replies View Related

Integration Services :: Any New Record Inserted Or Update In Source Table SSIS Package Automatically Run

Aug 7, 2015

Any new record inserted or update in source table SSIS package automatically run, how can we achieve without using service broker.

View 7 Replies View Related

How To Get The Metadata Of A Table

May 11, 2007

As title, such as the PK, data type of each column, etc.



Thanks,

Ricky.

View 6 Replies View Related

SQL Table And Column Metadata

Jun 21, 2006

SQL table and column metadata

Is there a way to add or update the column or table (using the extended properties) description metadata via T-SQL (from within a stored procedure) or via a program (such as VB.NET using ADO)?

These metadata properties are available via the SSMS interface:
Columns via the Column Properties/Table Designer/Description
Tables via the Table Properties/Extended Properties/[Extended Property Name]

Thanks in advance,
Mark

View 6 Replies View Related

DTS Help - Identity Column In Target Table

Mar 26, 2001

Having an identity column in target table, how do I use SQL 7 DTS ?

In Import wizard, I chose 'ignore' in source, checked Enable Indenity Insert - DTS failed with a msg like - a column cannot have NULL...

In DTS package, had Fast load and Enable Indenity checked - no luck !

What would be the standard procs for this since this sounds like a very common
senario.

Appreciate your help very much !

-Ivan

View 1 Replies View Related

Insert Records When Not In Target Table

Dec 29, 2014

I have need to insert records when not in the target table.

sample ddl for source and target tables. Notice the target table is missing the 'Upstairs' Rows:

CREATE TABLE source (
property_id INT,
unit_type VARCHAR(30),
care_type_id VARCHAR(1),
propert_room_type_id INT,
rate MONEY)

[code]....

View 2 Replies View Related

Replication Target Table Shrinks To Zero

Jul 20, 2005

I want to replicate a database to a subscriber that will be used as a readonly copy. The data has to be replicated as close to instantly as possible.To do this I set up a database export of objects and data to populate thesubscriber, then I set up transactional replication. To verify thatreplication is working successfully, I count the rows in each table, thereare 3 tables in total. For one of the tables, the replication completes butalmost immediately afterward, the table starts to shrink, and after severalhours the record count is zero. This isn't happening to the other twotables, and I can't figure out why.If you have no idea what might be causing this, perhaps you can suggestsome places to start looking. This is Win2k SP4 with SQL 2000 SP3.Thanks much.

View 1 Replies View Related

Can I Use A Variable To Hold The Target Table Name.

Apr 11, 2007

We need to pull from a table that is named tablename_mmddyy and populate a table with the same format tablename_mmddyy. The date will be different every month so I want to be able to build the tablenames every month. Is there a way to do this in SSIS? Thank you.

View 13 Replies View Related

Calculated Values In Target Table

Dec 12, 2007

Hi,

is there any way to fill up tables with calculated valued? My Source Table is build-on like this example:

CustNo / Year / Variable / Value
100 / 2005 / var1 / 321.90
100 / 2005 / var2 / 44.20
100 / 2006 / var1 / 12.09
100 / 2006 / var2 / 7.91
101 / 2005 / var1 / 23.78
101 / 2005 / var2 / 67.22

My Target Table should have this result:

KPI / CustNo / Year / Value
KPI1 / 100 / 2005 / 366.10
KPI1 / 100 / 2006 / 20.00
...

What I want to accomplish? I want to add var1 and var2 from Customer 100 in Year 2005, but is this possible with standard tasks in SSIS?

bye
Jonas

View 6 Replies View Related

Generating Sequence Numbers In Target Table

Dec 4, 2007

Hi,
What transformations can be used to generate sequence numbers in a data flow?

View 2 Replies View Related

Row Count From Flat File To Table In The Target

Aug 22, 2006

We have project in SSIS:

Control Flow
Load Data Flow Task to Copy Data Flow Task to Scrip Task

Data Flow under
Load Data Flow Task has
Flat File Source to Row Count1 to OLE DB Destination (ODS database)
Variable name for Row Count1 is RowCount

Data Flow under
Copy Data Flow Task has
OLE DB Source to RowCount2 to OLE DB Destination (WIMS database)
Variable name for Row Count2 is RowNumber

Data Flow under Script Task is code:

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.VisualBasic
Imports System.Diagnostics
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO



Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()
Dim varMyRowCount As Variable = Dts.Variables("RowCount")
Dim varMyRowNumber As Variable = Dts.Variables("RowNumber")
Dim varPackageName As Variable = Dts.Variables("PackageName")
Dim varStartTime As Variable = Dts.Variables("StartTime")
Dim varInstanceID As Variable = Dts.Variables("ExecutionInstanceGUID")
Dim varMailMsgtext As Variable = Dts.Variables("MailMsgText")
Dim PackageDuration As Long
Dim Filenum As Integer
Dim FilNam As String



'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
' Event log needs
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Dim sSource As String
Dim sLog As String
Dim sEventMessage As String
Dim sMachine As String
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

PackageDuration = DateDiff("s", varStartTime.Value, Now())

sSource = "RowCountReporting from SSIS"

' We need the message posted to the Application event log.

sLog = "Application"
sEventMessage = "Flat File Row Process: " & Chr(10) _
& " case Rows:" + varMyRowCount.Value().ToString + Chr(10) _
& "Wims Rows Processed: " & Chr(10) _
& " case Rows:" + varMyRowNumber.Value().ToString + Chr(10) _
& "Started: " & varStartTime.Value().ToString _
& Chr(10) _
& "Current Time:" & System.DateTime.Now _
& Chr(10) _
& "=============================================" _
& Chr(10) _
& "Package Run Duration in seconds: " & PackageDuration _
& Chr(10) _
& "Execution GUID: " & varInstanceID.Value().ToString
sMachine = "."

varMailMsgtext.Value = sEventMessage

If Not EventLog.SourceExists(sSource, sMachine) Then
EventLog.CreateEventSource(sSource, sLog, sMachine)
End If

Dim ELog As New EventLog(sLog, sMachine, sSource)

ELog.WriteEntry(sEventMessage, EventLogEntryType.Information, 777, 2)


Dts.TaskResult = Dts.Results.Success
End Sub

End Class


We want to verify flat file count should be same as the data load to WIMS database.
Now if I run this job I get the error message:
Error: Failed to lock variable "RowCount" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

My co-work said even I get balance from RowCount1 and Rowcount2 still need to have query from table in Wims database because for SQL server sometimes process counts not same as the data results load in table so need select count(*) from table. How and where I can put at the job.

My design is row count from flat file put one variable then row count from final table put the other variable then send e-mail to me show both variable for testing.
For real job I need if both variables same ok else send me an e-mail.

Any one has any idea? I will very appreciate.


Thanks.
Grace

View 1 Replies View Related

Transact SQL :: Dynamically Alter Schema Of Stage Table When Source Table Structure Changed?

Oct 25, 2015

we have a table in our ERP database and we copy data from this table into another "stage" table on a nightly basis. is there a way to dynamically alter the schema of the stage table when the source table's structure is changed? in other words, if a new column is added to the source table, i would like to add the column to the stage table during the nightly refresh.

View 4 Replies View Related

Integration Services :: Column Metadata In Table

Apr 10, 2015

I am having one store procedure which use to load data from flat file to staging table dynamically.everything is working fine. staging_temp table have single column.all the data stored in that single column below is the sample row.

AB¯ALBERTA ¯93¯AI
AI¯ALBERTA INDIRECT ¯94¯AI
AL¯ALABAMA ¯30¯ 

after the staging_temp data gets inserted into main table.my probelm is to handle such a file where number of columns are more than the actual table.if you see the sample rows there are 4 column separated by "¯".but actual I am having only 3 columns in my main table.so how can I get only first 3 column from the satging_temp table.output should be like below.

AB¯ALBERTA ¯93
AI¯ALBERTA INDIRECT ¯94
AL¯ALABAMA ¯30

View 45 Replies View Related

Integration Services :: Load Incremental Data Into Fact Table When Source Table Not Have Timestamp And Unique Key

Sep 24, 2015

I have a transaction table having about 40 crore rows in source. It don't have timestamp and unique key columns. It have only Bill_month and Bill_Year columns. Actually for loading this table into staging I have added a new datetime column by adding default bill_date as 01. Then

* First we delete last 3 month data from staging tables.
* Get last 3 months data from source table.
* Load that 3 months data from source to staging table. 

We do this because we only get update for last three months data. Now I have to include this transaction table as Fact table in DW. What will be the best practice for loading the fact table by picking data form staging table. Also we have to look up with dimensions for Foreign Keys. 

* Should I implement the same method of deleting last 3 months records and loading them again. 

View 3 Replies View Related

T-SQL (SS2K8) :: MERGE Insert Not Working Into Target Table

Mar 16, 2014

I am trying to insert new records into the target table, if no records exist in the source table. I am passing user specific values for insert, but it does not insert any values, nor does it throw any errors. The insert needs to occur in the LOAN_GROUP_INFO table, i.e. the target table.

MERGE INTO LOAN_GROUP_INFO AS TARGET
USING (SELECT LGI_GROUPID FROM LOAN_GROUPING
WHERE LG_LOANID = 22720
AND LG_ISACTIVE = 1)
AS SOURCE

[Code] .....

View 8 Replies View Related

Opening Table Causes Exception Thrown By The Target Of An Invocation

Oct 24, 2007

I just opened a large table with about 800 columns and 300,000 rows.
Doing the right click open on table displays message: Exception has been thrown by the target of an invocation.
Please help me determin what the error is and how to solve it.
I have google it for days now and no one has similar situation as mine. Many have same error, but their fix is not relevant to my issue. If you know about some SQL query limit please let me know.
Funny thing is that if I right click table and do script ---> select, then it does pull the data. ONly doesn't work when I do "Open Table."

Thanks for your help.

View 1 Replies View Related

Update One Colum With Other Column Value In Same Table Using Update Table Statement

Jun 14, 2007

Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani

View 3 Replies View Related

Can I Query Metadata To Find Table The Owns Field?

Mar 9, 2008

I have to write some reports for a database I am not familiar with. Is there a query I can use to find a table name if I know the field name?

example: Select table_name from database where field_name = 'my_field'

Mike

View 1 Replies View Related

Metadata Driven SSIS Package--table Variable

Aug 16, 2005

Is there anyone know if a simple SSIS package (moving data from source table to target table) or task can be called repeatedly using a variable that obtains value once a time from a metadata table containing only table names. Basically, I would like to pass in a table variable to the SSIS package or task to start the ETL for different tables. Thanks a lot!

View 12 Replies View Related

SQL Server 2008 :: Snapshot Replication Of 1 Table To A Target DB With 100 Tables

Apr 25, 2015

Regarding Snapshot replication....

If I have 200 tables on my source DB and 100 tables on my target DB... and I set up a publication w/ just 1 article to be published....

Will the invocation of the snapshot/apply affect any tables on my target other than the article I'm replicating?

View 2 Replies View Related

Transact SQL :: MERGE Statement Did Not Catch Existing Row In Target Table

Nov 8, 2015

I am new to use MERGE statement. The MERGE cannot find any match Cardnumber in the target table.  It inserts row into an existing row on the target table causing SQL rejected with duplicate key not allowed. The CardNumber is defined as a primary key on the target table with no duplicate allowed. Below snippet stop when MERGE insert a row exists on the target. The source table contains multiple rows with the same Cardnumber because it is a transactional table with multiple redemptions. 

If MERGE cannot handle many (source) to one (target) relationship, what other method that I can change to in order to update the target GiftCard table which keeps track of gift card balance? 

Below is the error message:

Msg 2627, Level 14, State 1, Line 5
Violation of PRIMARY KEY constraint 'PK_GiftCard'. Cannot insert duplicate key in object 'dbo.GiftCard'. The duplicate key value is (63027768).

The statement has been terminated.

DDL of the target table:

CREATE
 TABLE [dbo].[GiftCard](
[CardNumber] [varchar](50) NOT NULL,
[Year] [int] NULL,
[Month] [int] NULL,
[Period] [int] NULL,
[TransAmount] [decimal](10, 2) NULL,

[Code] ....

View 8 Replies View Related







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