Transforming Tables In Sql 2005
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
ADVERTISEMENT
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
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
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
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
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 24, 2008
I'm new to my company, although not new to SQL 2005 and I found something interesting. I don't have an ERD yet, and so I was asking a co-worker what table some data was in, they told me a table that is NOT in SQL Server 2005's list of tables, views or synonyms.
I thought that was strange, and so I searched over and over again and still I couldn't find it. Then I did a select statement the table that Access thinks exists and SQL Server does not show and to my shock, the select statement pulled in data!
So how did this happen? How can I find the object in SSMS folder listing of tables/views or whatever and what am I overlooking?
Thanks,
Keith
View 4 Replies
View Related
Mar 15, 2006
I've tried creating a simple SSIS package but I get the error:
[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".
THis is a known bug and while we wait for SP1 to fix this error, is there another way to simply copy tables (with indexes and without having the identity column renumbered) ?
Any help is appreciated!
View 5 Replies
View Related
Nov 7, 2007
In previous versions we could query the system tables to get metadata.
In SQL 2005 they seem to be hidden. They are there. You can query them, but I can't find them.
Question: Is there any way to see them in SSMS?
Yeah, I know you aren't supposed to, but hey, they are usefull and I like them.
Microsoft has given us a confusing collection of alternatives for finding metadata;
System views, Catalog Views, Dynamic Management Views, Compatibility Views, Information_schema views.
I have tried to organize some thoughts on what these are and here is what I make of them.
If any of this is in error, I would like some corrections.
SYSTEM VIEW - the stuff below and more. Shows system information.
...CATALOG VIEW - is a system view on the system tables
...DYNAMIC MANAGEMENT VIEW - is a system view on system tables and various buffers for current status info.
...COMPATIBILITY VIEW - is a system view which shows ONLY 2000 information. It is there for compatibility.
...INFORMATION_SCHEMA VIEW - each database has it's own set. Holds db-level metadata.
Hayden
View 6 Replies
View Related
Oct 29, 2005
I want to know how to partition a table using two columns (Example: Salesman, OrderDate).
View 8 Replies
View Related
May 15, 2008
Hello ,
I am trying to implement paritioned tables in SQL 2005. So far, I am successfull implementing partioned tables
using filegroups. I have created three filegroups and partitioning table in those three file groups using PARTITION FUNCTION,PARTITION SCHEME and I am successfully partitioned table on my local machine .
But now I need to implement a partitioned tables residing on multiple machines. Would anyone help me for this topic?
I am using filegroups to partitioning table. Is there any way to partition tables across multiple database servers
without using filegroups?
Thanks
Rohit
View 1 Replies
View Related
Jan 10, 2006
I have problems importing tables from db2 using the import wizard using IBM OLE DB Provider for DB2
The tables and data ('Preview...') can be viewed but when trying to map the columns ('Edit...' next to the tables) or import the data an error is recieved
[DB2/NT] SQL0443N Routine "SYSIBM.SQLCOLUMNS" (specific name "COLUMNS") has returned an error SQLSTATE with diagnostic text "SYSIBM:CLI:-727". SQLSTATE=38553
Schemas is used in both DB2 and Sql 2005
There is no problem importing to Sql 2000
View 1 Replies
View Related
Oct 18, 2006
Hello All,I am trying to use a SQL Server 2005 Temporary table with a hash(pound sign) in front e.g. '#OrderTmp'. . ASP.NET is objecting with the message viz "Incorrect syntax near '#OrderTmp' ". I guess it is objecting to the hash.Has anyone any ideas about how to specify this temporary table correctly.Thanks for your help,Peppa
View 2 Replies
View Related
Mar 3, 2007
Could anyone describe the pitfalls and best practices with temp tables in SQL Server 2005 for me?Locking, concurrency issues etc
View 1 Replies
View Related
May 1, 2005
Hi,
whats the best way to copy a table from sql server 2005 to 2000?
Thanks for any help...
View 3 Replies
View Related
Jan 20, 2006
I found a nice demo of using AJAX here
http://weblogs.asp.net/scottgu/archive/2005/12/26/433997.aspx
and want to use some of the code in my own website. But it uses a SQL DB and tables.
So without recreating thme manually what is the way to get these tables in MY DB?
View 2 Replies
View Related
May 9, 2007
HI,
I current have two 2005 boxes running 9.0.3050 in different DMZ with the source running a DTS to drop and copy its tables to the source every night. It was working up until last thursday. Nothing has changed in the FW rules and getting no errors. One is 3.5GB which copies fine, the other is 21GB and runs all night with only getting a fraction of the tables populated. I'm the hardware guy, but have some understanding w/ sql. Thanks in advance for any help.
View 1 Replies
View Related
Dec 26, 2007
Hi all,
Iam reading this forum from some time but its my first post here
Today i found by accictend this really nice PDF poster with SQL 2005 internal System Tables and thought that maybe someone here will be interested having it.
Poster is really huge, just like db schema on it ;)
But ok no more words, just grab it from here if You want http://rapidshare.com/files/79265626/sql.2005.sys.tables.pdf
Regards, Tom
PS.
1. If You can't read PDF after download with FireFox (like me) turn off pdf plugin and then download starts normally
2. If someone from admins think that its not good forum for this post pleas move it to somewhere that it best fits.
View 1 Replies
View Related
Nov 7, 2006
Can I know is it possible using database mirroring for only certain tables from the database, not for the whole database in sql server 2005. Hopefully able to get any assistance here. Thx alot.
Best Regards,
Hans
View 4 Replies
View Related
Aug 22, 2006
hi. changes were made to a dababase and we need to read the log file to see how much exactly was changed and what. how do i read the log file in sql server 2005 with out buying 3rd party software?
View 5 Replies
View Related
Apr 17, 2007
Hello!
I have a application where users are supposed to edit data from a table in a datagrid.
I want to use a dropdownlist to let the user choose a table to edit.
The users are members of different windows-goupes and different users have only access to edit there own tables. Let's say that they own data in different tables.
Is it possible to use windows authentication to find out what tables to show in the dropdownlist?
If it is, how do I get the names of the tables?
Best regards
Per
View 3 Replies
View Related
Mar 25, 2008
How can i print or export all fields for all tables in my sql 2005 db?
View 3 Replies
View Related
May 22, 2008
I am trying to add a linked table to my server, it is an access table.
Here is the code i am using but i get an error:
EXEC sp_addlinkedserver
@server = 'AITdb_be2',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'Access',
@datasrc = '\kcapp03deptsCommonEIQAIT DBTestAITdb_be.mdb'
GO
-- Set up login mapping using current user's security context
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'AITdb_be2',
@useself = 'false',
@rmtuser = 'Admin',
@rmtpassword = ''
GO
-- List the tables on the linked server
EXEC sp_tables_ex 'AITdb_be2'
GO
ERROR:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "AITdb_be2" returned message "The Microsoft Jet database engine cannot open the file '\kcapp03deptsCommonEIQAIT DBTestAITdb_be.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "AITdb_be2".
View 3 Replies
View Related
Jan 16, 2007
Hi ,
I need help regarding list of all sql server 2005 system tables
Satish
View 1 Replies
View Related
Nov 21, 2007
Hello,
I have the following Datawarehouse problem (SQL Server 2005 and SSIS 2005):
2 Fact tables and both with some foreign surrogate keys of dimensions (some of them with common dimensions, other with different dimensions).
I have to create another FACT table that is an aggregation and join table between the 2 ones.
How can I do?
View 6 Replies
View Related
Jan 31, 2007
I am racking my brain on this one. I am migrating Oracle data to SQL Server and would like an SSIS package that will copy the oracle data from every table to SQL server. I already have the schema set up in SQL and a straight copy of a specified table works fine between the OLEDB source and OLEDB destination. However, when I set up a loop which sets a global variable for the table names, which are identical on both dbs, and try to set the OLEDB Source and Destination OpenRowsetVariable to the global variable, the process fails with these errors with all validation turned off:
[OLE DB Source [18]] Warning: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
[OLE DB Source [18]] Error: Column "ID" cannot be found at the datasource.
[DTS.Pipeline] Error: component "OLE DB Source" (18) failed the pre-execute phase and returned error code 0xC0202005.
To sum up, I want to set the tables dynamically and have them infer the mappings. The names of the columns are already the exact same on each schema, and the data types are mapped accordingly. I have already verified that the names of the tables have been formatted correctly for each database. If I can't do it this way then how?
View 14 Replies
View Related
Apr 21, 2006
I converted a program from SQL 2000 to SQL 2005 all went well. I created a number of tables and stored procedures after the conversion. I backed up my .mdf and .idf files. I was having problems with SQL so I uninstalled and re-installed it. Once I re-installed it I could no longer display some tables and files. Since I am the dbo, I think I should be able to access them. There obviously is something I am missing, hopefully not the tables and sps.
I would appreciate any suggestions.
Thank you.
LitePipe
View 4 Replies
View Related
Jan 17, 2007
Probably a stupid and regularly asked question but I can't seem to find an answer, so here goes,
we have 16 .txt files, some with over 350 columns.
That info from each individual file needs importing to multiple sql tables.
need to look at sql table1 does record exist? if not create new then add in data once its been transformed eg datetime from yyyymmdd into datetime values [managed to get this using derived column] for first 20 columns, otherwise do update for the 20 columns...
then look at sql table2 and repeat for next n columns....
So I was wondering is it going to be better to write this as a dtsx package? if so can you point me to an example
or should I just write the code as part of a code behind page that scrapes the info and does a standard update/insert procedure?
Any help would be welcome.
thanks
View 3 Replies
View Related
