I have a database (MSSQL). To demonstrate the problem let me show a fictive Tablestructure. I don't want to discuss about how to save the data differntly, because the structure is fix and I can't change it.To get this result I would do a sql query with a lot of joins like that:
SELECT firstname, lastname, email.value, phone.value
FROM Customer
INNER JOIN
(
SELECT Customer_Properties.id, Customer_Properties.value
FROM Customer_Properties
[code]...
I don't think that this is really performant and the SQL-Queries get very complicated. Give it a other methode for that? I can't change the data structure.
hiI want to increase a varchar(5000)to varchar(8000) on a table thathas approximately million rows.....What is the impact on the serveror any good recomendations of a action to accomplish this in the best andfastest way.thanks davep
I have a simple query which is taking about 2.5 minutes to execute. What can be done to speed it up ? I cannot change the table structure. even without the group by, and without the float and division, it takes about 2.25 minutes.
Select 'SV00302' as FileSource, [Service_Call_ID], sum(0) as ActualHours, sum(cast([Estimate_Hours] as float)/100) as EstHours, max(MODIFDT+Modified_Time) as ModifiedDateTime
Hello,I have created the following trigger:CREATE TRIGGER tr_UDFTest ON UserDefinedFields FOR INSERT, UPDATEASDECLARE @foobar varchar(100)SELECT @foobar= foobar FROM insertedIF ( @foobar = 'foobar') INSERT INTO LogTable (LogText) values ('Found foobar')ELSE INSERT INTO LogTable (LogText) values ('Did not find foobar')GOBasically, the trigger monitors the values begin put in the foobar field,and acts accordingly based on what it finds. In practice, my needs are abit more complex (the trigger will be programmatically generated, based ona set of rules) but the principle is much the same.ErrorTable is defined as :create table LogTable (LogText varchar(128))UserDefinedFields is a table whose definition may change depending on theuser's needs, but for now assume it contains a varchar column calledfoobar.My problem is that if the user's needs change, and they remove the fieldfoobar, the trigger causes all subsequent inserts/updates to fail with anerror indicating the column foobar doesn't exist. (Which makes sense ofcourse!)CREATE TRIGGER tr_UDFTest ON UserDefinedFields FOR INSERT, UPDATEASDECLARE @foobar varchar(100)if not exists (select * from syscolumns sc inner join sysobjects so on sc.id = so.idwhere sc.name = 'foobar'and so.name = 'UserDefinedFields') BEGININSERT INTO LogTable (LogText) values ('Error : Foobar column does not exist!')RETURNENDSELECT @foobar= foobar FROM insertedIF ( @foobar = 'foobar') INSERT INTO LogTable (LogText) values ('Found foobar')ELSE INSERT INTO LogTable (LogText) values ('Did not find foobar')GOI'd be happy with the above 'flavor' of solution (bailing, or logging anerror and bailing, when we hit unexpected problems) as long as theinserts/updates don't fail otherwise. Perhaps I can nest a transaction, orsupress a RAISEERROR or something?The cleanest solution would probably be to change a bunch of clientsoftware such that it won't remove the foobar field if this field isneeded for a trigger (foreign key constraints based on the set of rulesI'm using are a nice and intuitive solution). Unfortunately, that doesn'twork well with my timeframe (done by thursday) as changing the clientsoftware is impossible by then. Any ideas or suggestions? Platform isWin2k, SQL 2000 Enterprise (I think enterprise, certainly 2000).thanks,Dave
Say you have an existing populated SQL 2005 database, with 700+ tables, and you want to just change the order of the columns inside every table. Short of manually building conversion scripts, anyone know an automated way to do this? I was thinking thru ways to do them all in one shot, and have tools like Erwin and DbGhost that could be used also. Basically moving some standard audit columns from the end of the tables to just after the PK columns.
field ([idConteudo] [int] NULL) to ([idConteudo] [BigInt] NULL)
Or really should drop and create again?
CREATE TYPE [dbo].[tbProjeto] AS TABLE( [dsConteudo] [varchar](64) NOT NULL, [idConteudo] [int] NULL, [dtConteudo] [datetime] NULL DEFAULT (getdate()) ) GO
Ok, I haven't been doing too much database work lately and my brain has gonesoft.I need some help with proper structure.My database is being used to track television shows.Any given show will be associated with at least one production company (callit ProdCo) but possibly up to 3 production companies (never more than that).I have:tblNetworkNetID (numeric, Identity)NetName (char, 50)tblShowDataBookingNum (numeric, Identity)BookingNumExt (numeric, Identity)Now, if I was just dealing with one ProdCo I would add it tblShowData as aforeign key from tblNetwork; no problem.How do I structure it so that I can combine 1 to 3 ProdCo's as one referencein tblShowData.I tried:tblNetworkComboID (numeric, Identity)Net1Net2Net3The problem is, obviously I can only join the FK from tblNetworks to one ofthe NetX fields in tblNetworkCombo.I don't quite know where to go from here. Any help would be appreciated.
Hi Can Any one help me out of this in my project I have to migrate only datastructure or schema and not the data inside the table using SSIS. How can I do that ?
I have a SQL 2005 database that I am using with a website. This basic website will be sold to other companies and ran on their servers with different URLs. Since, All of these databases will store different data, I'm not sure how I can make updates to original database and replicate those structure changes to the other DBs without changing the data also. Is there a way to automate the replication of structural DB changes without replicating the data along with it?
We have SQL Enterprise Manager (8.0). Is there any way to Copy a Table from one database to another with only the Structure (design) - not all the Data? I can't find any option in the Import Data Wizard that only copies the Table structure. Any help is appreciated. Thanks
I have written 5 content-management systems and am getting a little bored writing a lot of the same functionality and tweaking it for different datamodels. Is there a way within .NET to have visibility of a database's structure (ie, data type, column names, foreign keys etc)? I'd like to write up a dynamically generated form for any given table, displaying appropriate form controls based on data type and foreign keys.
new to SQL Server 2000. We have an obsolete database that we need to save off for x number of years. DB2 has utilities (DB2Look/Export) that allows for the export of the data along with a schema and script that enables the future recreation of the structure of the databases and tables to include RI etc. You can save off the architecture and relationships of the tables as well as the data.
Does SQL Server have anything similar?
Failing that, our plan is to backup the data and logs then image the entire disk.
Hi all!I have an application that needs to copy the database structure fromone database to another without using the "Generate SQL Script"function in Enterprise Manager. I'd like to do this from within astored procedure. Can someone recommend the best approach for this?I've seen references to using SQL-DMO from a stored procedure using thesp_OA* procs in other postings to this group but was wondering if therewas an easier way? Can I use bcp and then use xp_cmdshell from withinmy stored procedure? It's not clear to me from the documentationwhether bcp copies both structure and data or just data? Is there abetter way?Thanks in advance for any help!Karen
Hi SQL gurus,I have a table structure question. I will have a table 'Models' thathas one to many 'incomes' and one to many 'costs'. These 2 entitieshave exactly the same structure, which is 7 smallmoney and a name. Isit better to create a table 'Incomes' and a table 'Costs', with boththe same number of fields like this :Incomes-------------in_idmodelin_1in_2in_3in_4in_5in_6in_7in_nameCosts-------------c_idmodelc_1c_2c_3c_4c_5c_6c_7c_nameor is it better to create one single table that will contain bothentities like that :Incomes_Costs-------------ic_idmodelic_1ic_2ic_3ic_4ic_5ic_6ic_7ic_nameic_isIncomewhich only differs from the 2 above by the isIncome field to know whichrow is an income and which row is a cost.I'd like to know which method is the best in terms of performance andgeneral structure and would greatly appreciate if you explain a littlethe reasons that drove you to suggest me a method over the other.Thanks all for your time!ibiza
Basically we have have two larges databases, one of which is updated from the other monthly.
For exaplination purposes:
DB1 = Source DB
DB2 = Destination DB
The problem that I require a soltion to is, how do I insert rows from a table in DB1 to DB2 and recover and store the identity of the new row against the ID of the existing row. This is so that I can then matain constraints when it comes to inserting rows into the next table and the next and so on.
This process of storing the ID's as lookups will need to be done for almost every table of which there are 20.
The best Idea we have at the minute is to create a table with two colums for each table (drop it and recreate it after each table has exported) that contains the two ID's, new and old.
This will require using a cursor for each row in the existing table, inserting it in the new table and the using @@Scope_Identity to get the new ID and then insert the two values into the temp table.
This too me feels like it will be very slow, particuarly when I bear in mind how much data we have.
Does anyone have any better ideas? (Sorry if the explaination isn't great, its difficult to get accross)
I have been working on my windows app, using SQL Server 2005 (& C#) but I now have loads of junk data, so I want to copy the structure without any data to a new set of files that can become the production version.
I assume I can use backup and restore in future when I want a copy of my production data to use for future testing.
How would I take just one table from my test database and add it to my production database without retyping the design?
Apart from the size limitation is there any advantage in buying a version of SQL Server over using the Express edition?
I'm looking to create a new version of a database but with completely reloaded data - so I want to retain the database structure but none of the data.
Is there an easy way (read: without a DBA on staff) to copy an existing database structure - preferably with views and stored procedures - and not take the data along for the ride?
In order to export data to a 3rd party provider, I build five separate tables to store the data. Every table has a different layout, except for the first four columns. They are record type, SSN, employee id and another id number.
Basically, I have to sort that data by SSN then by record type. Each employee will have multiple records.
However, that data will need to be "merged" into one table to be exported.
I have created a table that defines the first four columns, but then has one large "filler" field that will contain the rest of the data. How can I copy data from five different tables with five different layouts into one table?
We have the following scenario: We receive CSV files every month for which SSIS packages were built to process the data. The following problems occur from time to time:
1. The structure of the CSV file changed (e.g. column added or removed) 2. There were no footers in the data, but now footers started to appear 3. Date format changed (e.g. used to be mm/dd/yyyy, but became mm.dd.yyyy) 4. Number format changed (e.g. from 2000 to 2,000)
Currently we have person who manually opens each file, and using our "validation document" validates to ensure none of these or similar problems occur. We would like to move away from this manual process if possible. I understand that items 3. and 4. could be caught by loading data into a staging table with VARCHAR data types, and performing validation before moving it any further.
Item 2 is a bit questionable (meaning depending on the footer size SSIS load could fail or not).
Item 1, however, is a sure fail of the SSIS package that directly loads the data into a table.
Thus I feel the two possible options are:
1. Create a custom script that will run through the file, row by row, apply all the necessary validations and report an error or continue if all checks out
2. Use some 3rd party tool to validate the files (semi-manually) before kicking off the SSIS processing.
I have an application with MSAccess as front end and SQLServer as backend. have quite a bit of tables. i wanted to write a stored procedure which exports a SQL Server table (both Structure & Data) to a new Access MDB file. i know with the use of DTS its possible but i need to code it down. i need to perform this at runtime. so can anybody help. Its urgent.
Is there a way to copy the structure from one database to anotherwithout affecting the actual data?For example, I added new fields and stored procedures to a db in mydevelopment environment. There are a lot of them.I now want to make the same changes to my production environment db butdon't want to affect the data. Only the fields, stored procs &constraints and stuff pertaining to the structure of the db should bechanged.Does anyone know of a program that can do this or can this be done inMS SQL Enterprise Manager.Any help would be appreciated.7078895
I have a large fact table spread across tens of partitions (appx. 1TB each). I found that the business does not need much of the columns in the table. So, as an optimization action, I decided to get rid of these un-needed columns.What is the efficient way to achieve this? Can I simply drop these columns from the table, or use a new table with the reduced structure?
Is it possible to purge all records in the database while retaining the the table structures. Even better yet, could I do it on a table by table basis? If I simply delete all the records the identities for the tables do not revert back to 1.
I have created two table with same data structure. I need realtime effects (i.e. data) on both tables - Table1 & Table2.
Following Points to Consider.
1. Both tables are in the same database.
2. Table1 is using for data entry & I wants the same data in the Table2.
3. If any row insert, update & delete occers on Table1, the same effect should be done on Table2.
4. I need real time data insert, update & delete on Table2.
I knew that using triggers it could be possible, I have successfully created a trigger for inserting new rows (using logical table "Inserted") in Table2 but not succeed for update & delete yet.
I want to understand how can I impletement this successfully without any ambiguity.
I have attached data structure for tables. Thanx...
The same as following Tree as you can see 1__ | 2__ | 4__ | 6 | __ 3__ 5
I need a query to return the following Result. I think it is possible only through Nested sub-Queries But i don't know how to do that Could any one help me.?
Hello. I want to ask about the possibility of copying both a tablestructure and it's contents from aSQL server table to a table within MS access. The problem cannot besolve with a permanent table structure at the target location.The names of the columns are essentially data with the application andso are subject to change. I am targeting a solution using SQL QueryManager.The approach I have tried (with failure) isSELECT *INTO <linkedserver table>FROM <local table>This should create and copy. However, I am not sure if this isachievable with this approach.Refer to the dialogue;-------------------------------------------------------USE MASTERGOEXEC sp_addlinkedserver@SERVER = 'Freddie',@PROVIDER = 'Microsoft.Jet.OLEDB.4.0',@SRVPRODUCT = 'OLE DB Provider for Jet',@DATASRC = 'C: empHMIS_Recipe.mdb'-- I am not sure if this is requiredEXEC sp_addlinkedsrvlogin 'Freddie', false, 'sa', 'Admin', NULLSELECT * FROM Freddie...FRED -- This is OKSELECT * INTO #Temp FROM Freddie...FRED -- This is OK-- This fails - Refer errorSELECT * INTO Freddie.FRED65from #tempServer: Msg 2760, Level 16, State 1, Line 1Specified owner name 'Freddie' either does not exist or you do not havepermission to use it.-- This also fails and I thought reflected the above select with naming- Refer errorSELECT * INTO Freddie...FRED65from #tempServer: Msg 117, Level 15, State 1, Line 2The object name 'Freddie...' contains more than the maximum number ofprefixes. The maximum is 2.EXEC sp_dropserver 'Freddie',@droplogins = 'droplogins'------------------------------------------------------------Thank you.Regards JC...
