How Can I Create A Clean Copy Of My DB And Keep Lookup Table Data

Nov 27, 2007

I have a database that I have been creating and testing. I have added some junk data and some data into lookup tables. Is there a way to create a clean copy of the db and keep the lookup table data? Also will I be able to create the db under a new name?

View 1 Replies


ADVERTISEMENT

How Do I Create A Lookup Table In SQL?

May 12, 2008

Table 1
Skill_ID SkillName
1 xml
2 oracle
3 sql
4 vb

Table 2
EMP_NAME SKILL_ID_1 SKILL_ID_2 SKILL_ID_3
JOHN DOE 1 4 null
JANE COLE 3 4 null
SAM COOK 1 2 4


I want to return the following:

RETURN DATA
EMP SKILL_ID_1 SNAME1 SKILL_ID2 SNAME2
JOHN DOE 1 xml 4 vb
JANE COLE 3 sql 4 vb
SAM COOK 2 oracle 3 sql

View 1 Replies View Related

Create A Lookup Table In SQL

Mar 23, 2008

i have very very basic knowledge of SQL like the ability to add, delete, update, select records and the ability to drop/create tables but i want to know how to integrate a lookup table into a field (very much like MS Access users can do with ease).

here is my example;

i have two tables - Club and Player

Club:

Club_Id
ClubName
Abbr

Player:

Player_Id
Name
Club_Id

the idea is that a single club can have several players. in the players table, i want the Club_Id field to act as the drop down whereas if a player changes club at any time, i can just refer to that player and change his Club_Id by scrolling down a list of values in SQL Server Management Studio 2005.

how can this be achieved by any chance, help would be very much appreciated.

View 8 Replies View Related

Using Foreach Loop To Parse An SQL Table - Lookup And Create Derived Column

Oct 25, 2007

I am looking for the best way in SSIS to do the following. I have an SQL table that for each row in the table I want to take an element from the table do a lookup in a Teredata Table, return information from the teredata source. Use that returned data to do some calculations and create a derived column from my calculations and place the data into the same SQL table that I am parsing through.

Ideas?

View 1 Replies View Related

DB Design :: Create Views From 2 Tables One Of Which Is Lookup Table That Will Give Column Names

Aug 5, 2015

I am trying a create views that would join 2 tables:

Table 1: Has all the columns need by a view (
Name: Product
Structure: ID, Attribute 1, Attribute 2, Attribute 3, Attribute 4, Attribute 5 etc
Table 2: Is a lookup table that provides the names of columns
Name: lookupTable
Structure: tableName, ColumnName, columnValue
Values: Product, Attribute1, Color
Product, Attribute2, Size
Product, Attribute3, Flavor
Product, Attribute4, Shape

I want to create a view that looks like

ID, Color, Size, Flavor, Shape

View 4 Replies View Related

Create Table From Text File, Extract Data, Create New Table From Extracted Data....

May 3, 2004

Hello all,

Please help....

I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....

I need to extract data from DataFile table, here's my query:

select * from dbo.DataFile
where DF_SC_Case_Nbr not like '0000%';

Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.

Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?

Any help would be highly appreciated.

Thanks in advance.

View 3 Replies View Related

SQL Server Admin 2014 :: Can't Create Model After Clean MDS Install

Apr 17, 2015

I have made a clean install of SQL 2014 including MDS on a server with Win Server 2012 R2.

After the installation I cant create new models because on an error saying that "Must declare the scalar variable "@Version_Id". "

I can see that the tables gets created in the MDS database but the models dont show up in the web interface.

I get the same error message when I try to deploy an old model.

View 0 Replies View Related

Create A Copy Of A Table In Sql Server

Jul 20, 2005

I have a table I'd like to copy so I can edit it and play around withthe data. How do I create copy of a table in SQl Server?Thanks,Bill

View 5 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

A Way To Recursively Look Up Hierarchal Data Using A Lookup Table

Oct 18, 2005

I have found the Common Table Expressions described in SQL 2005 and Iam not sure if it applies to this situation.Here are the tables<PRE><B>ManagedServer Table</B>--IdManagedServer (PK, int, not Null)--Name (nvarchar(256), not null)<B>ManagedServerToManagedServer Table</B>--IdParentManagedServer (PK, int, not null)--IdChildManagedServer (PK, int, not null)</PRE>The following will give you the parent-- Get Managed Server Group NamesLEFT OUTER JOIN ManagedServerToManagedServer mstms ONms.IdManagedServer = mstms.IdChildManagedServerLEFT OUTER JOIN ManagedServer msg ON mstms.IdParentManagedServer =msg.IdManagedServerHow would you go about getting all of the "parents" in the tree?Can this be done with CTEs? Unfortuately all of the examples found arejoining on itself.

View 1 Replies View Related

Performance Expectations For Fuzzy Lookup Against 25mill Row Lookup Table

Oct 31, 2007

We did some "at scale" fuzzy lookup tests today and were rather disappointed with the performance. I'm wanting to know your experience so I can set my performance expectations appropriately.

We were doing a fuzzy lookup against a lookup table with 25 million rows. Each row has 11 columns used in the fuzzy lookup, each between 10-100 chars. We set CopyReferenceTable=0 and MatchIndexOptions=GenerateAndPersistNewIndex and WarmCaches=true. It took about 60 minutes to build that index table, during which, dtexec got up to 4.5GB memory usage. (Is there a way to tell what % of the index table got cached in memory? Memory kept rising as each "Finished building X% of fuzzy index" progress event scrolled by all the way up to 100% progress when it peaked at 4.5GB.) The MaxMemoryUsage setting we left blank so it would use as much as possible on this 64-bit box with 16GB of memory (but only about 4GB was available for SSIS).

After it got done building the index table, it started flowing data through the pipeline. We saw the first buffer of ~9,000 rows get passed from the source to the fuzzy lookup transform. Six hours later it had not finished doing the fuzzy lookup on that first buffer!!! Running profiler showed us it was firing off lots of singelton SQL queries doing lookups as expected. So it was making progress, just very, very slowly.

We had set MinSimilarity=0.45 and Exhaustive=False. Those seemed to be reasonable settings for smaller datasets.

Does that performance seem inline with expectations? Any thoughts to improve performance?

View 4 Replies View Related

Stored Procedure To Copy Table 1 To Table 2 Appending The Data To Table 2.

Jan 26, 2006

Just wondering if there is an easy transact statement to copy table 1 to table 2, appending the data in table 2.with SQL2000, thanks.

View 2 Replies View Related

Load Fact Table, Very Slow With Lookup - Data Flow Transformation

Apr 9, 2008



Hello,


I have developed some packages to load data into "Fact" tables in the data warehouse.
Some packages are OK, other ones not. What is the problem?: some packages load fact tables with lots of "Lookup - Data Flow Transformation" into the "data flow task" (lookup against dimension tables) but they are very very slow, too much slow to be choosen as a solution.

Do you have any other solutions to avoid using "Lookup - Data Flow Transformation"? Any other solution (SSIS, TSQL and so on....) is welcome to speed up the Fact table loading process.

Thank in advance

View 7 Replies View Related

Clean Up Data - When Process Runs Next Time

Jul 25, 2006



In a integration project I am moving data from A to B.

First time is fine - since table B is empty.

However next time I run the process, I would like to delete all records in B before I run the project again.

What is the best way to delete / clean up data when you re run the process ?

Cheers, T







View 1 Replies View Related

Copy Rows To The Same Table And Its Related Data In The Other Table

Nov 23, 2007

Hi All,
I have 2 tables People & PeopleCosts.

PeopleID in People Table is the primarykey and foreign Key in PeopleCosts Table. PeopleID is an autonumber

The major fields in People Table are PeopleID | MajorVersion | SubVersion. I want to create a new copy of data for existing subversion (say from sub version 1 to 2) in the same table. when the new data is copied my PeopleID is getting incremented and how to copy the related data in the other table (PeopleCosts Table) with the new set of PeopleIDs..

Kindly help. thanks in advance.
Myl

View 3 Replies View Related

Copy A Table With Its Data From A Db To Another

Dec 16, 2007

Hi: At the moment,  I know how to copy a db to another (create a .bak file), but I am not sure what is the best way to copy one the table (with it data) from a db to another.Would u please give some links or suggestions?Thanks.jt 

View 4 Replies View Related

Copy Data From One Table To Another From 2 Db's.

Jan 17, 2008

i have a table called t_CustomerAcct in 'Dev' db and want to copy the data in t_CustomerAcct table in 'Production' db.
but i have some records in the table in 'production' db which i dont want to be updated. my primary key in both tables is 'email'
i bit lost on how to do this and i dont want to loose any data from production db accidentally.
 
 

View 1 Replies View Related

Copy Data From One Table To Another...

Jan 10, 2007

hi. i'm trying to copy data from one table to another. the table has already been created but i just want to update some of the information in it.

here is what i have:

INSERT INTO DefendantCaseBAK

SELECT * FROM DefendantCase WHERE DefendantCase.StatusID=2
when i run this query, i get this error:
The column prefix 'DefendantCaseBAK' does not match with a table name or alias name used in the query.
what am i doing wrong? i looked up some examples on the internet and i copied them exactly. thanks for your help!

View 6 Replies View Related

Copy Data From One Table To Another

Jun 2, 2008

I need to move data from a large table (350GB/395,000 rows) to another table within the DB, but onto another disk. The table has an image data type so I have to create a new table onto the new disk. My original table looks like this:

USE [PD51_Data]
GO
/****** Object: Table [dbo].[SCANNEDDOCUMENTS] Script Date: 05/27/2008 11:26:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SCANNEDDOCUMENTS](
[DocID] [int] IDENTITY(1,1) NOT NULL,
[CaseID] [int] NOT NULL,
[DocName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Doc] [image] NOT NULL,
[DocLocation] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DocNotes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TopicID] [int] NULL,
[ScannedDocumentsCheckSum] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
PRIMARY KEY CLUSTERED
(
[DocID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[SCANNEDDOCUMENTS] WITH NOCHECK ADD CONSTRAINT [ISCANNEDDOCUMENTS2] FOREIGN KEY([TopicID])
REFERENCES [dbo].[TOPICS] ([TopicID])
GO
ALTER TABLE [dbo].[SCANNEDDOCUMENTS] CHECK CONSTRAINT [ISCANNEDDOCUMENTS]

My new table looks like this:
USE [PD51_Data]
GO
/****** Object: Table [dbo].[SCANNEDDOCUMENTS2] Script Date: 05/27/2008 11:27:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SCANNEDDOCUMENTS2](
[DocID] [int] IDENTITY(1,1) NOT NULL,
[CaseID] [int] NOT NULL,
[DocName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Doc] [image] NOT NULL,
[DocLocation] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DocNotes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TopicID] [int] NULL,
[ScannedDocumentsCheckSum] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [SECONDARY] TEXTIMAGE_ON [SECONDARY]

GO
SET ANSI_PADDING OFF

My plan was to use import/export wizard to move the data over to the new table, while set to Bulk Log recovery, drop the original table, then create the indexes/constraints on the new one.
With this much data, I'm wondering what else I should do.
Anyone have a better idea? What am I missing?

View 7 Replies View Related

How To Copy Some Data Into Same Table

Sep 11, 2013

There are some data in a table with TypeID=23 and I need copy all rows with TypeID=23 to the same table, I mean append at the end, and make the copied rows TypeId=24 for some tests purpose.

View 3 Replies View Related

How To Copy Data From One Table To Another

Mar 9, 2014

I have a table Product BasicInfo, which has following fields:

ProdID (PK)
ProdName
Description
Manufacturer
Store
ProdPrice

Now i have another table TempPrice, having fields:

TempID (PK)
ProdID (FK)
TempProdPrice

TempPrice table has the original values, which are needed to copy into ProductBasicInfo column "ProdPrice" (which is entire NULL column so far).

View 5 Replies View Related

Copy Data From One Table To Another

Oct 16, 2014

Write a query that will grab bin# from table A and if its missing on table B copy it. I tried and update query but it doesn't work and also a select Into and failed too.

Table_A

idx Bin
1 CSR010101A
2 CSR010101B
3 CSR010102A
4 CSR010102B
5 CSR010201A
6 CSR010201B
7 CSR010202A
8 CSR010202B

Table_B

idx Bin
1 CSR010101A
2 CSR010101B
3 Null
4 Null
5 CSR010201A
6 Null
7 CSR010202A
8 NUll

View 1 Replies View Related

Copy A Row Of Data From One Table To Another

Jun 8, 2006

Hello,

I want to move or copy a row of data from one table to another, but not sure how to go about it! I have SQL 2000, and using Enterprise Mangager with SQL query to run the script. This will be used in a procedure once I get the Query to work.

Thank you,

Ernie

View 7 Replies View Related

Copy Data From Old To New Table

Apr 3, 2007

Hi All
I have two tables one is old and another new table.
I need to copy the data from old to new table.
using stored procedure.
How can I do the same.
Please help me.

Thanks

View 3 Replies View Related

Copy Data From One Table To Another, What Is Best?

May 27, 2008

I have a table that I need to move all data to a secondary data file. I have to copy all data from the table to another table to make this work since I have an image data type. My initial table is set up like this:

USE [PD51_Data]
GO
/****** Object: Table [dbo].[SCANNEDDOCUMENTS] Script Date: 05/27/2008 11:26:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SCANNEDDOCUMENTS](
[DocID] [int] IDENTITY(1,1) NOT NULL,
[CaseID] [int] NOT NULL,
[DocName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Doc] [image] NOT NULL,
[DocLocation] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DocNotes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TopicID] [int] NULL,
[ScannedDocumentsCheckSum] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
PRIMARY KEY CLUSTERED
(
[DocID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[SCANNEDDOCUMENTS] WITH NOCHECK ADD CONSTRAINT [ISCANNEDDOCUMENTS2] FOREIGN KEY([TopicID])
REFERENCES [dbo].[TOPICS] ([TopicID])
GO
ALTER TABLE [dbo].[SCANNEDDOCUMENTS] CHECK CONSTRAINT [ISCANNEDDOCUMENTS2]

My new Table is set up like this:
USE [PD51_Data]
GO
/****** Object: Table [dbo].[SCANNEDDOCUMENTS2] Script Date: 05/27/2008 11:27:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SCANNEDDOCUMENTS2](
[DocID] [int] IDENTITY(1,1) NOT NULL,
[CaseID] [int] NOT NULL,
[DocName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Doc] [image] NOT NULL,
[DocLocation] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DocNotes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TopicID] [int] NULL,
[ScannedDocumentsCheckSum] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [SECONDARY] TEXTIMAGE_ON [SECONDARY]

GO
SET ANSI_PADDING OFF
My plan is, once I move or copy the data to the new table, I will then drop the original table, rename new table to the original, create the indexes and FKs. What do you think would be the best way to move the data to the new table?

View 6 Replies View Related

Copy Data From One Table To Another

Jun 5, 2006

On the OLE DB Source, I have as following:

select field1, field2, field3 from table_source

On the OLE DB Destination, I used fast load option with table lock checked, and check constraints to copy data to table_destination. Both table_source and table_destination have the same table definitions. After the dtsx package ran, the number of rows copy over to the destination is not same. I got different results from different runs. This only happens when the source table is over 1 million rows and when I'm copying from the transaction database where user's activities are heavy.

Any ideas how I can modify the package so it can copy correct data? Thanks!

View 23 Replies View Related

How To Copy One Column Data From One Table To Another Table

Nov 30, 2004

Hi, All,
I have agentID in product table.
Now I add agentID column in transaction table. Now I want to copy all agentID from product table to transaction table based on the order_id
in both table. Can you show me an example?
Thanks
Betty

View 3 Replies View Related

How To Create An Copy Of A Certain Record Except One Specific Column That Must Be Different &&amp; Insert The New Record In The Table

Sep 1, 2006

Hi
I have a table with a user column and other columns. User column id the primary key.

I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key

Thanks.

View 6 Replies View Related

Copy Table Structure Only - Not Data

Oct 18, 2007

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
 

View 1 Replies View Related

Copy Table Data Back And Forth

Mar 16, 2006

This is probably a dumb question.. but here goes.

I'd like to make a copy of several tables before changes are made to them so that I can 'roll back' if necessary.

What I 'thought' i could to was this:

select * into mytable_temp from mytable

and then to roll back..

truncate table mytable

Select * into mytable from mytable_temp

When I try and select back into my original table, it says I can't because the object already exists.. What is a better way to accomplish this????

View 14 Replies View Related

Copy Column Data From One Table?

Aug 7, 2012

Query to copy column data from one table and put it in another???

View 6 Replies View Related

Copy Field Data From Table To Another

Jul 23, 2005

Let's say, I have Table1 and Table2, both with the exactly samestructure and data in it, except that Table1.Field1 is empty in databut Table2.Field1 have data in it.How could I copy all the data from Table2.Field1 to Table1.Field1?Is there any simpler way except looping through all the records to doit?

View 5 Replies View Related

Copy Table And Data From One Database To Another

Oct 3, 2006

I am working on a migration project.

Doing the standard processing, taking source data in a staging database, where I then create the new target tables, transform the source data into the new target table structure, and load the data.

However, having created the new target tables in my staging database, I cannot accurately migrate these tables into the new database.

An example table.

The following is the script created by SQL Management Studio if I right click and script the table as CREATE.
CREATE TABLE [dbo].[tbPRO_Package]( [PRO_PackageID] [int] IDENTITY(1,1) NOT NULL, [CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_tbPRO_Package_CreatedDate] DEFAULT (getdate()), [CreatedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tbPRO_Package_CreatedBy] DEFAULT (suser_sname()), [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_tbPRO_Package_ModifiedDate] DEFAULT (getdate()), [ModifiedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tbPRO_Package_ModifiedBy] DEFAULT (suser_sname()), [GUID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tbPRO_Package_GUID] DEFAULT (newid()), CONSTRAINT [PK_tbPackage_ID] PRIMARY KEY CLUSTERED ( [PRO_PackageID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]


Note that the field PRO_PackageID has an identity, and is a primary key.

Also note the constraints on several fields and the default values.


If I try to move this table and data using the SQL Server Import and Export Wizard, telling it to drop and create the table, and to keep identity, it does not correctly generate the table defintion - it generates the following script

CREATE TABLE [Migration Staging].[dbo].[tbPRO_Package] ([PRO_PackageID] int NOT NULL,[PackageCode] varchar(8) NOT NULL,[LKU_VatRateCode] nchar(10),[CancellationCharge] decimal(18,2),[CancellationMultiplier] decimal(18,2),[CreatedDate] datetime NOT NULL,[CreatedBy] varchar(50) NOT NULL,[ModifiedDate] datetime NOT NULL,[ModifiedBy] varchar(50) NOT NULL,[GUID] uniqueidentifier NOT NULL)

We've lost the identity, the PK,the constraints and the defaults.


If I use the SSIS Transfer SQL Server Objects Task, then the primary key is kept, but the identity is not, nor are the constraints or defaults.

Am I missing something? Is it actually POSSIBLE to accurately move tables between databases?

View 7 Replies View Related







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