Transforming Structurally Identical Tables In A Loop
Dec 12, 2007
I'd like to extend a package functionality.
I created it drag/drop way with hard-coded table names.
Now for the same source and destination connections I'd like somehow in a loop transform 20 source tables of the same structure to 20 destination tables of the same structure providing table names in a loop.
I also have in the package preparation SQL tasks such as dropping destination table if exists, and then re-creation , so it needs to consume a table name as parameter from my loop.
Is it doable ?
View 6 Replies
ADVERTISEMENT
Oct 31, 2006
Does anyone know how i can transform the following table:
ID
Name
StartQuantity
EndQuantity
Price
1
Title1
1
10
2.55
1
Title1
11
999999999
1.35
2
Title2
1
10
2.55
2
Title2
11
35
1.55
2
Title2
36
999999999
0.55
3
Title3
1
999999999
5.55
4
Title4
1
10
2.32
4
Title4
11
999999999
2.00
5
Title5
1
999999999
1.99
Into multiple tables like so:
ID
Name
1-10
11+
1
Title1
2.55
1.35
1
Title4
2.32
2.00
ID
Name
1-10
11-35
36+
2
Title2
2.55
1.55
0.55
ID
Name
1+
3
TITLE3
5.55
5
TITLE5
1.99
In Sql 2005?
View 6 Replies
View Related
Feb 18, 2015
I have a database with three different tables having the exact same fields. New records are written to table1, before moving to table2 and ultimately table3. I was wondering if it's possible to run the same query on all three tables at the same time. I need to get all unique instances in the JC field from each table after a specified date. I get an "Ambiguous column name" error on the JC and TimeID fields.
SELECT distinct [JC]
FROM [table1], [table2], [table3]
where timeid > '20090900';
View 1 Replies
View Related
Apr 2, 2008
Hi,
I have two tables named Tab1 and Tab2. Both are identical in structure. The only diff is Tab2 has two more additional fields (FromDate and ToDate).
The structure is like below :
Col1
Col2 (Date field)
Col3
Col4
Also Tab 2 have
Col5 (From Date)
Col6 (To Date)
Now I want to transfer some set of reocrds from Tab1 to Tab2. The additional Tab2 field (Col5 and Col6) values should be the minimum and maximum values of Tab1 date field for the current set.
How to accomplish this? Kindly help me in this regard.
Thanks
Somu
View 1 Replies
View Related
Jul 30, 2007
Does anyone have a good query that would return records from two tables that are found in one, but not it the other table? In my situation I have 2 tables that are duplicate tables and I need to find an additional 3000 records that were added to one of the tables. I also have a composite key so the query would have col1, col2 and col3 as the composite key. So far I have tried concatenating the 3 columns and giving the result an alias and then trying to show the ones that were not in both tables, but have been struggling. Thanks..
View 4 Replies
View Related
Nov 18, 2005
I have two different tables... one for all Staff, and another for all Temp Staff. I need both to output to a datagrid, and so I need to grab both tables from a SQL query to output to my datagrid, but I can't seem to get the logic right for it to work. Can someone give me some suggestions on why my results are blank when I'm running this query? I thought a simple join would allow both sets of identical column names to coexist in peace...SELECT TOP 100 PERCENT dbo.StaffDirectory.UserName, dbo.StaffDirectory.LastName, dbo.StaffDirectory.FirstName, dbo.StaffDirectory.Dept, dbo.StaffDirectory.Title, dbo.StaffDirectory.EMail, dbo.StaffDirectory.LocationFROM dbo.StaffDirectory INNER JOIN dbo.TempStaff ON dbo.StaffDirectory.Location = dbo.TempStaff.Location AND dbo.StaffDirectory.EMail = dbo.TempStaff.Email AND dbo.StaffDirectory.Title = dbo.TempStaff.Title AND dbo.StaffDirectory.Dept = dbo.TempStaff.Dept AND dbo.StaffDirectory.FirstName = dbo.TempStaff.FName AND dbo.StaffDirectory.LastName = dbo.TempStaff.LName AND dbo.StaffDirectory.UserName = dbo.TempStaff.UName AND dbo.StaffDirectory.MDNo = dbo.TempStaff.MDNoIs something wrong here? It just doesn't work =(Any suggestions would be really appreciated.Thank you
View 5 Replies
View Related
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
Oct 29, 2014
Selecting the data from these two tables? The columns 'host_ext_id' have the same data BUT in 'adrmst' all data is preceeded by A0000.
table is 'adrmst'
Host External IDAddress NameAddress Line 1Address Line 2City StatePostal Code
A000042401 T-3803VC 1530401 00 WENGER STTOPEKA KS66609
A000042402 CO INC 960 PP TOMLIN MILL RDSTATESVILLENC286258332
A000042403 CO INC 1420 PP BLVD GARYSBURGNC278319748
A000042405 CO INC 1419 PP BROWN RD KISSIMMEEFL347463415
A000042405 CO INC 962 PP COMMERCE DRVALDOSTAGA316011206
table is 'shipment'
Shipment IDHost External IDcar_move_idP_DEST_LOC_ID
42401 42401
42402 42402 SDQD_00862TAGSDQD
42403 42403 SDQD_00863TAGSDQD
42404 42404 SDQD_00863TAGSDQD
42405 42405 SDQD_00863TAGSDQD
View 4 Replies
View Related
Oct 31, 2007
Im wondering if it is possible to write a procedure that check two identical tables for any missing records. The table design is excatly the same, but some records (of the 40,000) have not copied over to the second table.
Any help would be great, cheers.
View 3 Replies
View Related
Oct 25, 2005
We have written an application which splits up our customers data intotheir individual databases. The structure of the databases is thesame. Is it better to create the same stored procedures in eachdatabase or have them in one central location and use the sp_executesqland execute the generated the SQL statement.Thank you.Mayur Patel
View 4 Replies
View Related
Jan 30, 2008
I'm trying import 7 tables from each of 30 SQL2005 databases into a SQL2005 Consolidation database. I can simply create data flow tasks for each one but instead I would like loop through a list instead.
I've created a table to house the names of the databases from which I want to import the data.
I've created SQL task to return the database names from the table as a "Full Result Set".
I've assigned the result set to a user variable (type = Object) an named the result name 0
What I'd like to do is create a data flow task which connects to each of the databases and imports 7 specified tables from each database appending the table name with my database name in the result set.
I'm stuck on how I'd set the connection strings in my OLE DB Source in my Data Flow task. Any insight would be greatly appreciated.
Thanks in advance.
Bill Webster
View 4 Replies
View Related
Dec 30, 2005
Sorry if this is a super-basic question...I'm used to join selects but not sure how to approach an append select (or something like it)
I have two tables with identical field structures: a Master table with 10,000 rows and a Custom table table with 1,000 rows
To keep it simple, let's say the two tables each have a FirstName field and a LastName Field.
Is it possible to use a View or a Select statement (or any other method) to 'append' the rows of both tables so that the result set still has only the two columns (FirstName and LastName) and has 11,000 rows?
Thanks for your help!
Randy
View 4 Replies
View Related
Apr 30, 2007
I am replicating an 80GB database between NY can CT and would like toknow why table sizes are different between the two.Here is an example of sp_spaceused::NY IOI_2007_04_23 rows(279,664) reserved(464,832)data(439,960) index_size(24,624)CT IOI_2007_04_23 rows(279,666) reserved(542,232)data(493,232) index_size(48,784)Thanks,
View 1 Replies
View Related
Jun 8, 2007
CREATE TABLE [RS_A] ([ColA] [varchar] (10)[ColB] [int] NULL)CREATE TABLE [RS_B] ([ColA] [varchar] (10)[ColB] [int] NULL)INSERT INTO RS_AVALUES ('hemingway' , 1)INSERT INTO RS_AVALUES ('vidal' , 2)INSERT INTO RS_AVALUES ('dickens' , 3)INSERT INTO RS_AVALUES ('rushdie' , 4)INSERT INTO RS_BVALUES ('hemingway' , 1)INSERT INTO RS_BVALUES ('vidal' , 2)I need to find all the rows in A which do not exist in Bby matching on both ColA and ColBso the output should bedickens 3rushdie 4So if i write a query like this , I dont get the right result setSELECT A.ColA, A.ColBFROMRS_A AINNERJOIN RS_B BONA.ColA <B.ColAORB.ColB <B.ColBBut if i do the following, i do get the right result, but followingseems convoluted.SELECT A.ColA, A.ColBFROMRS_A AWHERE ColA + CAST(ColB AS VARCHAR)NOT IN (SELECT ColA+CAST(ColB AS VARCHAR) FROMRS_B B)
View 6 Replies
View Related
Dec 17, 2007
On one of the workstations being used by a developer we see the following entries in the event log:
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (4)
Event ID: 17828
Date: 17/12/2007
Time: 10:24:25
User: N/A
Computer: <removed>
Description:
The prelogin packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library. [CLIENT: <local machine>]
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
<removed>
Searching on the internet we don't get a satisfying answer. This connection to the database is made through a .NET web service using the default client libraries from the Base Class Library. We are talking about a fully patched SQL Server 2005 Developer Edition and .NET Framework 3.5.
Any clues?
I only found the following "relevant" link:
http://www.webservertalk.com/message1539518.html
But I am the kind of guy that likes to know what went or goes wrong instead of trying out hacks like the one described in the above link.
View 4 Replies
View Related
Mar 23, 2007
I have a legacy SQL 6.5 system where they have articles that they relate to multiple topics by using a column with comma seperated values example ArticleID: 1, Topics 3,5,8,9. Im trying to find a way to automate this in SSIS to create a cross ref table. Is there any transformation componets that can help me with this?
View 4 Replies
View Related
May 9, 2006
Hi SQL Champs!
I have a flat file source where some data are an question mark (?). Importing this to SQL serever, I want to exchange these (?) with an NULL -value.
How do I do this most easy in SSIS? Do I need a another tmptable first, and then derived?
Many thaks
kurlan
View 3 Replies
View Related
Oct 26, 2005
Is there a way to change the collation against all the tables and columns within a database?
View 2 Replies
View Related
Aug 9, 2006
Hello,
I am very new to TSQL however I do have a little programming in my background. I am trying to run a query to select several datafields from about 45 tables within a database. I have written the below code to loop through all of the tables and extract the information however I need to find some way to join the results using PCTimeStamp as the key. Any help anyone can give me is greatly appreciated!
while (@Table_Number<18240) /*Loop to read data from all Turbine all Tables*/
BEGIN
if (@Table_Number=17990 or @Table_Number=18234) /*ERROR AVOIDANCE for Non-Sequential Turbine #'s*/
BEGIN
set @Table_Number=@Table_Number+1;
END;
set @Table_Insert = cast (@Table_Number as NVARCHAR); /*Converts Table Number to Format usable in String below*/
set @SQLString = N'SELECT Id, Status_Avg_NacellePos, Status_Avg_BladeAPos, Status_Avg_BladeBPos, Status_Avg_BladecPos, Grid_Avg_ActivePower, Status_Avg_Windspeed1, Status_Avg_Windspeed2, Status_Avg_WindDirection1, Status_Avg_WindDirection1 FROM T_'+@Table_Insert+'_10MINDATA where PCTimeStamp=(select max(PCTimeStamp) from T_'+@Table_Insert+'_10MINDATA)';
set @Table_Number = @Table_Number+1; /*Increment @Table_Number to control Loop*/
if (@Table_Number <18006 or @Table_Number>18235) /*ERROR AVOIDANCE for Non-Sequential Turbine #'s*/
BEGIN
EXEC(@SQLString); /*Execute String to retrieve data from table*/
END;
END;
I tried to format that in this text box as best I could but I apologize for the result. In summary Table_Number is used as the key to loop through the sequentially numbered tables (with some numbers missing) and extract the required fields from the most recent entry (PCTimeStamp=max).
Other notes: the code as written outputs all of the required data with no error messages but the data column titles are displayed i.e:
id windspeed direction time
1 12 000 1200
id windspeed direction time
2 12 010 1200
...................
looking for
id windspeed direction time
1 12 000 1200
2 12 010 1200
Thanks again
View 7 Replies
View Related
Jul 23, 2005
Hi All,I'm not sure how best to describe what I'm really looking to do, soI'll just give you my example and hopefully someone can help.I have a table that looks similar to this:Primary Key Field Type Field Value--------------------------------------1234 FName Sally1234 LName Smith1234 Phone 555-555-55557777 FName John7777 LName Riley4444 LName Johnson4444 Phone 222-234-5555etc...Which I want to move into a format that looks like this:Primary Key FName LName Phone-----------------------------------------1234 Sally Smith 555-555-55557777 John Riley4444 Johnson 222-234-5555What is the most efficient way to do this? I'd rather not create aseparate query for each of the field types (there are more than 3 in myactual situation).Any help you can offer would be appreciated!Jeff
View 2 Replies
View Related
Aug 14, 2006
Hi all, using transactional replication in SQL 05, I have a schema that is different at the subscriber and wanted to know the best approach for transforming the published data. For example, the subscriber is more normalized than the data being published causing 1 published record broken out into to many tables at the subscriber. Also need to applying scalar funtions to a few published columns before committing at subscriber.
I've read DTS, although backwards compatible, is being deprecated in a future release. I suppose I can use either SSIS or custom procedures. I didn't see any tie-in to SSIS in the system procedures to setup replication (only DTS). Any advice on which would work in this type of scenario?
Much appreciated, Pat
View 1 Replies
View Related
Jan 4, 2006
Hi,
I have read that the old method of using DTS to transform data during replication will not be supported in 2005.
In SQL Server 2005, is there a way to use an SSIS package to modify data you want to replicate?
View 4 Replies
View Related
Nov 2, 2000
Can anybody tell me, how to write a T-SQL loop,
that goes through all tables in a database to
realize some updates there?
Thanx a lot in advace
Gert
View 2 Replies
View Related
Apr 24, 2006
I have two tables that I wish to return values for and populate a text file with the returned values. This part works well, however, I get strange values when I rung the Select statement against both tables.
For example, if I run this statement:
Select count(page_count), sum(Page_count) from Contracts
this will return the values I expect
If I were to add another table to this query then things go crazy. For instance, if I took the same query and added the Maps table to it as such:
Select count(page_count), sum(Page_count) from Contracts, Maps
I get crazy numbers that are many times more then the actual numbers
My goal is to get the page count, number of records for Contracts and number of records from Maps all in one query
View 5 Replies
View Related
Jan 14, 2002
Hi ... I need to read rows from a large source file , check if the data selected already exists in the destination and if it does, upate the destination , else insert a new row . Now i could use a sp, but that means i have to call it for each row in the text file ...
any ideeas on what kind of package can be created /
cheers
benjamin
View 1 Replies
View Related
Aug 19, 2015
I'm having a hard time trying to transform a date that comes in for import in this format: 2015-04-08T18:00:48.000Z to DT_DBTIMESTAMP, I get potential loss of data error. (I've tried other SSIS data types.)
I had never seen this type of date before so I'm at a loss.
View 2 Replies
View Related
Mar 8, 2012
I would like to run the same query on multiple tables. So say I have a list of tables
@tableList = a|b|c|d
And then I have my query looping through the tables
for (@table in tableList)
{
update from @table
set = ''
}
Is there a simple way to do this in an mssql query, if so how do I get to loop through the query switching the table name?
View 4 Replies
View Related
May 21, 2007
Hi,
I have 2 Tables
Table 1, Row 1
1. Id = 1
2. GraphPoints = 023, 045, 078 (text - data type)
I need to move data to Table 2.
Table 2 should have
1st row
1. Id = 1
2. GraphPoint = 023 (float data type)
2nd row
1. Id = 1
2. GraphPoint = 045 (float data type)
and so on
How do I do that?
Thanks.
View 3 Replies
View Related
May 10, 2007
As part of xml parsing, I use multicast to direct output of nodes to their corresponding relational tables and I do have a comma-delimited list for some nodes which basically needs to be converted into rows as illustrated below
ID Products
--------------------------------------------------------------------------
1 12, 45
2 10, 20
and I would like to have results as
ID Products
--------------------------------------------------------------------------
1 12
1 45
2 10
2 20
I would appreciate if someone could offer me some guidance here.
View 11 Replies
View Related
Jan 12, 2015
We are having folder table and team table as like below structure.
Folderlist (F)Table: (
==============
id ,folder_name, parent_id
1, c, 101
2,b,202
3,c,203
Teamlist table (T)
============
team_id, Team_name, Parent_folderid
101 , mobile,101
202 ,Tab,200
200, Phone,200
203,apple,205
205,nokia,208
208,samsung,208
If F.parent_id(101)=T.team_id(101) and T.team_id(101)=T.parent_folder_id (101)
then output should come as 'Mobile/c' (this is for f.parent_id=101)
If F.Parent_id=T.team_id and T.team_id!=T.parent_folder_id
then parent_folder_id have to start search on team_id column where it got match and pick the Team_name from that corresponding id
Ex: F.parent_id=202 is matching with T.Team_id (202) but this T.team_id(202) is not matching with T.parent_folderid(200) , so this T.parent_folderid (200) have to search on T.id (200) ,if now T.id(200) is matching with T.Parent_folder_id(200) then it have to give the names from the starting hirache
like phone/tab/b (this is for F.parent_id=202)
View 1 Replies
View Related
May 28, 2010
difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.
View 4 Replies
View Related
Feb 22, 2006
I have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks
View 6 Replies
View Related
Aug 22, 2007
I have a Client-Server - App where every Client-User has his own DB. The server is monitoring
changes to all Client-DB's via SqlDependency.
My problem can be reproduced with a small application, it even might be a €œfeature€? and not a €œbug€?:
- Consider two Databases TestDb1 and TestDb2 running on one SQL Server 2005 instance.
- Both DB€™s have identical Schemas.
- Consider the two DB€™s have each one table named €œTable1€?.
- Both tables have the same schema as already mentioned (the fields Id and Text).
- Now I setup a SQLDependency object on each Database:
private void InitSQLDependencies()
{
string connstr1 = €œData Source=localhost;Integrated Security=SSPI;Initial Catalog=TestDb1€?;
string connstr2 = €œData Source=localhost;Integrated Security=SSPI;Initial Catalog=TestDb2€?;
SqlDependency.Start(connstr1);
SqlDependency.Start(connstr2);
using(SqlConnection connection = new SqlConnection(connstr1))
{
string ssql = €œSELECT Id,Text FROM dbo.Table1 €œ;
SqlCommand command = new SqlCommand(ssql , connection);
SqlDependency dependency =new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
}
using(SqlConnection connection = new SqlConnection(connstr2))
{
string ssql = €œSELECT Id,Text FROM dbo.Table1 €œ;
SqlCommand command = new SqlCommand(ssql , connection);
SqlDependency dependency =new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
}
}
If I make any changes to the Table in TestDb1 I get two notifications with the different Id€™s but the same Info,Source,Type (saying e.g. Data,Change,Update).
If I make changes to the Table in TestDb2 I again get two notifications with the same result. As soon as I rename the Table in one of the Db€™s (e.g. Table2) and also change my Sql-Query in the code €“ I get just one
Notification as expected. This behaviour is the same even If I change the connectionstring so that it points to another machine.
So it somehow seems to fire a notification for every change to a table with the same name €“ regardless of the connectionstring where the physical change was done.
Does anybody know if this is a wanted behaviour of SqlDependency ?
Does anybody know how I can set this up so I can have two DB€™s with identical Schemas and only get a Notification from the DB I actually changed ?
View 19 Replies
View Related
