Hi
I have a problem out here. I have two databases with the same table structure
and I want to merge data from both the tables . Please can anyone let me know.
Iam using Sql Server 7.0.
Thanks
Bye
I'm pretty new to SQL altogether. I kinda gave myself a crash-course when the software I was using, Papertrack, was having problems. It all started when I tried to move the database. Now there are two separate databases that I can see on SQL Enterprise manager. I picked up the book and figured some things out, but still don't know how to merge the databases. I called the support for the software and got to know them on a middle-name basis. Is there a way I can merge the two databases so that the software can pull queries from both and access the files?
To anyone that likes a challenge,
Thanks
Hello, As is known, when developing an application in VWDE that features membership as well the application's own database, you have a total of 2 databases. Seeing as a lot of hosters charge per database instance, to make the membership utility viable, it would be prudent to merge each database into one. Quite a few blogs/tutorials, including ScottGu's, recommend merging, but I'm as yet to find one that actually explains the merging process step by step. Advice such as, "run each script against the same database," simply isn't enough.If anyone could shed any light on this topic, it would be much appreciated. Of note, I have SQL Server Management Studio as the recommended deployment tool. If one thing's becoming increasing evident, it's that it would be well worthwhile in finding a host that supports SQL Server 2005 as opposed to 2000. Kind regards.
View 2 Replies View RelatedHow do I merge three SQL Server databases into one. There will be some duplicated information in some of the tables. Any help much appreciated. Thanks.
P.S. Running SQL v7 on NT!!
i have two databases one has
firstname, lastname
and the othere
firstname, lastname, emailaddress
how do i get to merge the data from emailaddress to the 1st datbase?
Melvin Felicien
IT Manager
DCG Properties Limited
How would you solve this problem?
Would you use replication? SSIS? Schedule stored procs? A third party data tool?
We have about 50 databases located at offices around the world. There are several key tables which we need a central copy of at our Head Office.
We need only the new INSERTS or UPDATES for the day to be copied to head office.
The copying process should happen after business hours for that office.
Some cleaning of the data needs to happen before updating head office.
Our database is being installed at new offices every other month, so it's important to us that this be easy to add a new database into the schedule, ideally without needing a SQL DBA or Developer.
We want something robust, performant and easy to troubleshoot.
Kev
Hi,
I have two databases lets say DB1 and DB2.
Schemas for both databases is same.
In both database schemas there are tables which has identity columns as primary key.
Now i want to merge these two databases in a single database say DB3.
It may also possible that some master records in both databases are common so they should not repeat in DB3
Is there any way so that i can do it quickly and as soon as possible.
Thanks in advance
Rohit
I have a SQL 7 database with about 30 tables in. For documentation purposes I need to print out the structures of each table. I have tried using the diagram tools but no joy.
I have access to Access, Crystal Reports, if these are any use?
Please help!!
Tony Kavanagh
I will soon be writting a script which will search threw all tables in my database and try to find how they may be linked to one another, and also which tables are called by which stored procedures, any suggestions or tips would be great.
View 1 Replies View RelatedHi all,
Could anyone let me know if there are any Scripts available for comparing the schema differences between two SQL server databases?
Thanks
DBAnalyst
Hello !! :O)
I have a SQL 7 database with some tables in. For documentation purposes I need to print out the structures of each table and all the rows in every table.
Please help!!
Thanks
CREATE TABLE table_name AS SELECT STATEMENT WHERE rownum = 0; this query will copy the table structure of one table without copying the data. I need to copy the table structures of all the tables in a particular database without copying the data.suggest me on this.
View 6 Replies View RelatedI need to get a print out of column names, types, and properties for each of the tables. We just started using SQL Express from Access. I cannot find any reference to printing the structures in Microsoft SQL Server Management Studio Express.
Thanks
Hi all expert,
Question as stated title above, how do I duplicate the table structure to another database whether it copies all columns, primary and foreign keys, default values, descriptions, constraints, indexes
Many thanks for the help.
regards,
We have databases with large numbers of tables. We have a separate database for each year. For various reasons, we need to export about 100 of the tables (Structure only, not their data) from last years database into this year's database. What is the best method for doing this? The import/export wizard creates the tables but does not bring in important things like keys.
Regards Shirley A
Hi.
Is there anyway to export the table structures : data type,length,NULLABLE,Description into an Excel file using MS SQL Server?
Or I need to do it manually?
Thank you in advanced.
Sincerely
Agustina
Hi!
I have 7 source databases and one target database, all using the same structure. The structure is made of 10 tables, with foreign key constraints.
I need to merge the source databases into the target (which won't have any data before that process, but will already have the correct schema), and to keep the relationships between the records.
I know how to iterate over the source databases (with SMO foreach), but I'd like to know if someone can advise the best copy method for that context in SSIS ? (I don't want to keep the primary keys, but I need to keep the relationships...)
Any pointer most welcome!
best regards and thanks
Thibaut
I have to merge the data from two databases, one is in SQL Server 2005 format, one is in 2000. The merged data will then reside on a SQL Server 2000 platform. Is there an easy way to do this through Management Studio or Enterprise Manager? Or will we have to export the data from the 2005 database to a flat file and import it into a new 2000 database. And then do the merge?
TIA
Hi,
Is this anyway to finding updated/ deleted recored using anyother data flow transformation tasks without using sql task.
Can find the new records using merge join task.
Is there better way to merge master table using staging table?
Thanks in advance.
DF.
I have two tables each with a date field. I have to combine these two tables and sort on the date. I want the date from each table to be on the same column though, not in seperate columns.
Ex:
Table A
ID
Log_Time
ETI
Table B
ID
Log_Time
Action
Description
Thanks for the help
Rock *
thanks for reading.
kinda like like a UNION for columns maybe. i think that's what i'm looking for. take a look if you have a minute....
3 queries, each pretty clear (count the occurrences of a value in a column --> where someFlag='Y')...
SELECT Count(flag_A) as 'flag_A_confirmed', Year
FROM cases
WHERE flag_A = 'Y'
GROUP BY Year
SELECT Count(flag_B) as 'flag_B_confirmed', Year
FROM cases
WHERE flag_B = 'Y'
GROUP BY Year
SELECT Count(flag_C) as 'flag_C_confirmed', Year
FROM cases
WHERE flag_C = 'Y'
GROUP BY Year
but my desired output merges the columns together in one table ....
total confirmed flags for that year by type...
Yearflag_Aflag_Bflag_C
1998121533
1999161325
2000191421
2001... etc
i'm pretty sure i can do this using temp tables. any other ideas?
thanks for your input in advance. again.
I need to populate a table from several sources of raw data. For agiven security (stock) it is possible to only receive PARTS ofinformation from each of the different sources. It is also possibleto have conflicting data.I am looking to make a composite picture of a given security using thefollowing rules:1) The goal is to replace all NULL and Blank values with data2) Order of precedence (from highest to lowest) is Non-NULL Non-Blank--> Blank --> NULL3) In the case of Non-NULL Non-Blank values that conflict (aredifferent) leave existing value (even if NULL or Blank)For example:Given the following rows:Symbol Identity IdSource Exchange Type SubType Name-------- ------------ --------- --------- ------- ---------------------------TZA 901145102 CUSIP XNYS Stock NULL TV AZTECATZA 901145102 NULL NULL NULL NULLWSM 969904101 CUSIP XNYS Stock NULL WILLIAMSSONOMAWSM 969904101 NULL XNYS Stock NULLWILLIAMS-SONOMAWSM CUSIP XNYS Stock Common NULLWSM NULL CUSIP XASE Stock NULL WILLIAMSSONOMATYC 902124106 CUSIP XNYS Stock NULL TYCOTYC 902124106 CUSIP XNYS Stock NULL TYCOINTERNATIONALI am looking for the following results ('*' indicates changed value)Symbol Identity IdSource Exchange Type SubType Name-------- ------------ --------- --------- ------- ---------------------------TZA 901145102 CUSIP XNYS Stock NULL TV AZTECATZA 901145102 *CUSIP *XNYS *Stock NULL *TV AZTECAWSM 969904101 CUSIP XNYS Stock *Common WILLIAMSSONOMAWSM 969904101 *CUSIP XNYS Stock *CommonWILLIAMS-SONOMAWSM *969904101 CUSIP NULL Stock Common NULLWSM *969904101 CUSIP XASE Stock *Common WILLIAMSSONOMATYC 902124106 CUSIP XNYS Stock NULL TYCOTYC 902124106 CUSIP XNYS Stock NULL TYCOINTERNATIONAL
View 6 Replies View RelatedI don't know the correct syntax to accomplish this. I've looked in the on-line help but am not coming up with anything.
Basically I have two identically structured tables in two separate databases.
I wanted to select anything from the dbo2.table that isn't in the dbo1.table and insert it into dbo1.table.
I thought I could do something like... quote:
Select INTO dbo1.table
and then add my select statement. But that throws an error:
quote:Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'bkupproceduresfeeschedule' in the database.
And the on-line documentation says an INTO is only for a new table.
How can I merge records from one table into another table?
I'm trying to avoid a large amount of manual data manipulation.
Here's the background: Legacy system that has (well let's call apples apples) pretty much no method of enforcing data integrity, which has caused a fairly decent amount of garbage data to be inserted in some tables. Pulling one of the [Individuals] table from within this Legacy system and inserting it into a production system, into the Table schema currently in place to track [Individuals] in this Production system.
Problem: Inserting the information is easy, how to deduplicate the records that exist within the staging table that the legacy [Individuals] table has been dumped into in production, prior to insertion. (Wanting to do this programmatically with SQL or SSIS preferably, so that I can alter it later to allow for updating existing/inserting new)
Staging Table Schema:
;
CREATE TABLE [dbo].[stage_Individuals](
[SysID] [int] NULL, --Unique, though it's not an index intended to identify the [Individuals]
[JJISID] [nvarchar](10) NULL,
[NameLast] [nvarchar](30) NULL,
[NameFirst] [nvarchar](30) NULL,
[NameMiddle] [nvarchar](30) NULL,
[code]....
Scenario: There are records that duplicate the JJISID, though this value is supposed to be unique for every individual. The SYSID is just a Clustered Index (I'm assuming) within the Legacy system and will be most likely dropped when inserted into the Production [Inviduals] table. There are records that are missing their JJISID, though this isn't supposed to happen either, but have valid information within SSN/DOB/Name/etc that can be merged into the correct record that has a JJISID assigned. There is really no data conformity, some records have NULLS for everything except JJISID, or some records will have all the [Individuals] information excluding the JJISID.
Currently I am running the following SQL just to get a list of the records that have a duplicate JJISID (I have other's that partition by Name/DOB/etc and will adapt whatever I come up with to be used for those as well):
;
select j.*
from (select ROW_NUMBER() OVER (PARTITION BY JJISID ORDER BY JJISID) as RowNum, stage_Individuals.*, COUNT(*) OVER (partition by jjisid) as cnt from stage_Individuals) as j
where cnt > 1 and j.JJISID is not nullNow, with SQL Server 2012 or later I could use LAG and LEAD w/ the RowNum value to do my data manipulation...but that won't work because we are on SQL Server 2008 in this environment.
[URL]
With, the following as a potential solution:
GSquared (3/16/2010)Here's a query that seems to do what you need. Try it, let me know if it works.
Performance on it will be a problem, but I can't fine tune that. You'll need to look at various method for getting this kind of data from the table and work out which variation will be best for your data. Without access to the actual table, I can't do that.
;
WITH CTE
AS (SELECT master_id,
MIN(ID) AS first_id,
MAX(Account_Expiry) AS latest_expiry
FROM #People
GROUP BY master_id)
SELECT P1.master_id,
[code].....
Unfortunately, I don't think that will accomplish what I'm looking for - I have some records that are duplicated 6 times, and I'm wanting to keep the values within these that aren't NULL.
Basically what I'm looking for, is to update any column with a NULL value to the corresponding Duplicate [Individuals] record value for that column.
**EDIT - Example, Record 1 has a JJISID with NULL NameFirst & NameLast BUT Record 2 has the same JJISID and values for NameFirst & NameLast. I'm wanting to propogate the NameFirst & NameLast from Record2 into Record1
I have 2 test database identical in size and table structure, only one resides on filegroups and one resides on a single file.
From everything I have heard filegroups are suppose to improve performance. I stop and start the server before each test to clean out
cache. When I run the filegroup test initially the first run averages 20 seconds slower then the database sitting on the single file. However
on consective runs there is a significant improvement in performance with filegroups verses single files. I have
the indexes sitting on a separate filegroup and 2 additional filegroups to spread the tables across. Does any one know what would be causing
the performance to degrade on the initial run of the test. (The test by the way is a stored procedure that runs a select statement against each table).
Any Info Will help Thanks
Barb
Hi,
i'm writing a app in c# and have to store Trees in a Database.
I'm working with Datasets for the exchange between the DB and the App.
The trees have the same options like the windows folders. If u delete a node, all subnodes should be deleted too.
But something a Foreign Key from ParentID references (Id) with the delete-Rule on cascade seems not to be possible, because of multiple cascade Paths or cycles. Do i have to add some xtra constarins:
Not Possible:
create Table tree (
Id varchar Not null,
ParentId varchar Not null,
Constraint pk1 Primary Key (Id),
Constraint fk1 Foreign Key (ParentId) references tree(Id)
On Update Cascade
On delete CAscade
)
Do i have to write triggers, which delete The subnodes too and set the Update-/deleterulr on NO Action
Greetz
Does anyone know any good links for SQL tree structures and example queries and stuff... I cant really find anything part from the standard example of emplyee, boss, salary which explains how to create the tree table...(dun dis bit) I did notice a book but I live in a little village so cant go get it till wekend?
I'm desperate, reli need to work out how too do this.....
(Wow; surfing this site has really illuminated what a lowly hack-programmer I am to this field of SQL and relational processing :S )I am creating a temp table, doing an Bill of Material explosion for a single Order Line Item.(note: SQL Server 2000)I used blindman's "accumulator method (click here) (http://www.dbforums.com/showpost.php?p=6239109&postcount=6)" to generate the entire potential BOM tree.So; step 1 works wonderfully! :beer: Cheers blindman!Now; I want to remove unwanted nodes (and all their children) from the temp-table. I have a bunch of functions (0 to 1 for each node) that return a Yes or No. If "No", then flag-to-eliminate immediately that branch and don't revisit anything on it (and therein lies my problem). This will leave me with a temp table of only valid nodes.Since this is recursive, and since it will involve Dynamic SQL (the function name varies), all I can come up with is using a Cursor in a WHILE loop. Even at that; since a CURSOR is point-in-time (ie: values don't change once selected), I'll have to re-check the current temp table values (or create of 2nd temp table of only deleted nodes and repeat a SELECT with NOT EXISTS in it, hmmm).Since blindman's method generates a table ordered by level, the sequence of processing is pre-determined - unless I can re-order it into a more traditional hierarchy (1 entire branch at a time) and number the levels, in which case the cursor could just skip to the next branch of equal or higher level.Note: The thought does occur to me I could have an intermediary function (static name) that in turn does the Dynamic SQL. These functions contain the Business Logic that looks at a myriad of column values and relationships in the database and there's no one-size-fits-all decision tree so Dynamic SQL is necessary.The max cursor size will be maybe 300, and on average 100. Number of levels will normally be 3 or 4, but conceivably could be up to 10. Given the average 100 potential components/sub-assemblies, the final assembly will be about 30. As a periodic background process; it will do 3,000 Order Line Items a day, so I'm figuring 1 second response time per build is adequate (ie: the user's not waiting on it so it doesn't have to be blinding fast) - however why waste?Anyhow; I thought this might be a fun problem for some Data Structure genius who wants to give a lesson in Relational Programming.Thanks for looking.Here's what I have so far:CREATE PROCEDURE dbo.sp_ExplodeTest1 (@recID int = 1) AS/* tbTestH is a table containing an assembly hierarchy. Assemblies with no parent are Builds.Assemblies with no children are Components.It's columns: MyID int, ParentID int, (other descriptive columns)*/declare @t table (TempNodeID int identity(1,1),MyID int)-- Seed the tree with the Build's ID.insert into @t (MyID) values (@recID)/* This populates the temp table with the entire Assembly for the given build. It is Ordered By the level in the assembly. Number of assembly levels is infinite.For example: Level 1 = the Build. It comes firstLevel 2 = all parents are level 1. Is next (no particular seq)Level 3 = all parents are level 2. Is next (no particular seq)etc.*/while @@Rowcount > 0insert into @t (MyID)select tbTestH.MyID from tbTestHinner join @t rList on tbTestH.ParentID = rList.MyIDwhere not exists ( select *from @t CurrentList where CurrentList.MyID = tbTestH.MyID)-- now to display the results so far.select t.*, tbTestH.* from tbTestH inner join @t t on tbTestH.MyID = t.MyID order by t.TempNodeIDGO
View 8 Replies View RelatedWhat is included under SQL Data structures? Could anyone refer me to some good tutorials on that material?
View 2 Replies View RelatedI am using the wizard to create a new mining structure and getting the error at the time of selecting the datamining technique like either microsoft decision tree or microsoft timeseries etc.,
"Unable to retrieve a list of supported data mining algorithms. Make sure you are connected to the correct Analysis Services server instance and the Analysis Services server named localhost is running and configured properly. You can continue with a default list of data mining algorithms."
What is that means?
Thanks in advance,
Hi,Does any of u folks used any tools for database structures comparision??
I have used DBDIFF(from DKGAS.com) ChangeManager from Embarcadaro and SQL COMPARE from Redgate.
I did have have some problem in each of these tools AND am looking for some good tools which are economical also.
Thx in advance
Ravi
A point for general discussion maybe.
I've just read Jeff's query regarding passing an array to a stored procedure, and it seems to be a question that hits the message board quite frequently. There often seems to be the case where you have some block of data in one location, and want to process it somewhere else, and there just isn't an easy way to do it (or is there?). Passing a cursor is possible, but getting data back out of a cursor is painful.
The ways I have used in the past are:
Using a cursor
Using a global temporary table
Using a "permanent" table, but declared with a unique name (using a timestamp) and passing the tablename as a parameter.
Has anybody any suggestions for a future SQL release which we could suggest to Microsoft?
Suggestions from myself:
-Having a single TSQL command to copy a cursor back into a table, to make cursor parameters less cumbersome
-Being able to pass a pointer to a temporary table as an SP parameter
-Allowing variables to be declared with more complex structures (such as arrays, or classes)
Regards,
ChrisH
Hi, I have a table of folders that I would like to be able to displayin a depth first manner, similar to what you would see in WindowsExplorer. the table is defined similar toCREATE TABLE Folders (folderID int,parentFolderID int,name varchar(32)).... I've left some things out of the definition for the simplicity ofexample.My desire is to quickly generate a record set containing the all childfolders of a specific folder along with the how many levels deep eachfolder is, in a quick and memory efficient manner. I have done someresearch through this group and found a few examples similar to what Ihave hypothesized, however I was hoping for some feedback on what Ihave decided to use.Since this database table is already created and I cannot modify theexisting schema, it would not be very feasible to use a nested setsmodel (unfortunately -- or if anyone has a suggestion?).I plan on using a stored procedure that recusively calls itself.something similar to the following:/* This is assuming the table #TempFolderTable has* already been declared globally*/sp_FolderDisplayRecurive @folder int, @level int ASIF @@NESTLEVEL > 31 RETURN;DECLARE @childFolder INTDECLARE folders_cursor AS CURSOR LOCAL FORSELECT folderID FROM FoldersWHERE parentFolderID = @folderIDOPEN folders_cursorFETCH NEXT FROM folders_cursorINTO @childFolderwhile @@Fetch_status = 0BEGIN-- Insert current folder into-- folder table (under parent @folder)INSERT INTO #TempFolderTable(FolderID, DepthLevel, ParentFolderID) VALUES(@childFolder, @level, @folder)-- Iterate over all of its childrenEXEC sp_FolderDisplayRecursive@childFolder, @level + 1FETCH NEXT FROM folders_cursor INTO @childFolderEND...Will this incure a large overhead for a deep directory structure? Arethere any other problematic issues? Any suggestions and/or commentsare very welcome. Thanks!
View 3 Replies View Related