Create New Table From Several Existing Tables?

Apr 8, 2012

I have three tables :

England_Summer_2001
England_Summer_2002
England_Summer_2003

The tables have the following columns :

Player, Position, [From], [To], Fee, Type, ID, League, Window

I want to create a new table, EnglandFinal with all the data from the three tables although I'm guessing it would not be a good idea to copy the primary keys (ID column) as they would clash.

I have played around with CREATE and INSERT into and UNION but I get various errors. I'm sure I've done this before!

This creates a table from a single table :

select * into Final
from England_Summer_2001

View 14 Replies


ADVERTISEMENT

Create An Extra Table (for Audit Purpose) For Every Existing Table In A Database

Mar 28, 2008

Hi all, please help. I m trying to create an "empty" table from existing table for the audit trigger purpose.
For now, i am trying to create an empty audit table for every table in a database named "pubs", and it's seem won't work.
Please advise.. Thanks in advance.

Here is my code:





Code Snippet

USE pubs

DECLARE @TABLE_NAME sysname
DECLARE @AUDIT_TABLE VARCHAR(50)

SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME NOT LIKE 'audit%'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
AND TABLE_NAME = 'sales'

WHILE @TABLE_NAME IS NOT NULL
BEGIN

SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_NAME = 'sales'

SELECT @AUDIT_TABLE = 'Audit'+''@TABLE_NAME''


SELECT * INTO @AUDIT_TABLE
FROM @TABLE_NAME

TRUNCATE TABLE @AUDIT_TABLE
ALTER TABLE @AUDIT_TABLE ADD UserAction Char(10),AuditStartTime Char(50),AuditUser Char(50)


SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
AND TABLE_NAME NOT LIKE 'audit%'

END
Thanks. ..

View 6 Replies View Related

Tempoary Table Question - How To Create One With The Same Schema As An Existing Table?

Jul 28, 2006

Hello,

I'd like to create a temporary table with the same schema as an exiting table. How can I do this without hard coding the column definitions into the temporary table definition?

I'd like to do something like:

CREATE TABLE #tempTable LIKE anotherTable

..instead of...

CREATE TABLE #tempTable (id INT PRIMARY KEY, created DATETIME NULL etc...

I'm sure there must be a simple way to do this!

Many thanks,

Ben S

View 3 Replies View Related

Create A Copt Of Existing Table

Feb 25, 2006

How can I create a new table with a new name which is an exact copy of an existing table with:

1) All rows

2) no rows, only the structure of the table.

View 2 Replies View Related

Create Insert Scripts For Existing Table?

Jan 6, 2007

What is the easiset way to create TSQL Insert scripts for each record in a table. Can this be accomplished with one of the tools in SQL Server 2005?

Thanks in advance

View 5 Replies View Related

Not Able To Create An Identity Column For An Existing Database Table

Feb 1, 2008

I am working with a table in SQL server. I have a column that I want to designateas an identity column. I am not able to do this, because the field for "Identity Specification" is not editiable.
What I did was I went to sql server, right clicked and selected "Modify".The column properties dialog box/edit grid is then displayed with attributesthat I can modify.
There are two major nodes in this dialog box. One is named "General" and the otheris named "Table Designer". I expand the "Table Designer" node and then go to the node labeled "Identity Specification" It is here where I would like to edit thevalues.
The values that are listed for edit are listed below. BUT, the problem is thatI can place my cursor in those fields, but I am not able to change/edit them.Can anyone tell me what the problem is here? and how I can fix it?
 +Identity Specification   (Is Identity)   Identity Increment   Identity Seed

View 3 Replies View Related

SQL Server 2008 :: Create Partition On Existing Table?

Mar 4, 2015

Can we create the Partition on Existing Table?e.g Create table t ( col1 number(10,0), Col2 Varchar(10)) ;After the table Creation can we alter the table to partition the table.

View 2 Replies View Related

Table Partitions On Existing Tables.

Jun 4, 2007

Hi guys , assuming right now I already create partition function (PF_Date) and partition scheme (PS_Date). Let say I would like to implement the partition on the existing tables ( eg: transaction table which is in PRIMARY filegroup), how am I switch it from PRIMARY to PS_Date ? Is it I have to re-create the particular table then only able to put in the partition scheme? Hope can get any assistance here. Thanks alot.



Best Regards,

Hans

View 8 Replies View Related

Need Help In Copying A Temp Tables Contents To A Existing Table

Nov 8, 2006

I have a real table with an identity column and a trigger to populate this column.

I need to import / massage data for data loads from a different format, so I have a temp table defined that contains only the columns that are represented in the data file so I can bulk insert.

I then alter this table to add all the other columns so that it reflects all the columns in the real table. I then populate all the values so that this contains the data I need.

I then want to insert into the real table pushing the data from the temp table, however this gives me errors stating that the query returned multiple rows.

I specified all the columns in the insert grouping as well as on the select from the temp table.

ANY thoughts / comments are appreciated. This is beginning to drive me nuts.



Rob

View 5 Replies View Related

Create New Table From Existing Table

Feb 16, 2007

Hi
I'm trying to Create a new Table from existing table in Q/Analyzer. I figured it would be something like this:
CREATE TABLE newTable AS(SELECT * FROM OldTable);
but i keep getting
Server: Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'AS'.
also.. is there another method of doing this, something like
INSERT INTO newTable(SELECT * FROM OldTable);
and it creates the table ( newTable ) for u if it doenst already exist??
Cheers!!!
im using sql2000

View 4 Replies View Related

How To Create A New Table From Existing Table

Aug 19, 2005

Hi All I want to create new table which is going to be a copy of one existing table.
I need to copy the existing data into the new table too. How do I do that?

View 2 Replies View Related

DB Design :: How To Copy Data From Existing Table To Normalized Tables

May 20, 2015

I normalized the below tables but I am finding it difficult to copy data to the new tables.  How do I copy data from existing table to the normalized tables? see the table structure below and other supporting information:

SKU_DATA(SKU,SKU_Description,Department,Buyer) Note: this table already has data in it.
CREATE TABLE SKU_DATA (
SKU   
   Integer NOT
NULL,

[code].....

The table structure above have two three determinants( SKU,SKU_Description and Buyer).  SKU and SKU_Description are candidate keys. Primary key is SKU.

Normalization : SKU_DATA(SKU,SKU_Description, Buyer)
 BUYER(Buyer,Department)

View 2 Replies View Related

Creating Nested Tables Inside Of A Existing SQL Server Express Table

May 26, 2007

Hello,

Quick question, I hope, I am trying to create a table that has a column that is a nested table in SQL Server 2005 Express Edition. Any ideas how I could go about doing this?

Sincerely,



James Simpson

Straightway Technologies Inc.

View 4 Replies View Related

Create Table From Two Other Tables??

Oct 8, 2007



Im trying to bring together two tables into one, but they're records dont always match, so im getting akward returned records. So now i want to combine the fields i need from these two tables to create a new one. How would that be coded.

I want to

SELECT

[Customer_Bill_Customer_No],

[Customer_Bill_Customer_No],

[Sales_Header_Order_DateTime],

[Sales_Header_Your_Reference]


from

[NavisionReplication].[dbo].[Tbl_Sales_Header]



and
SELECT
[Sales_Invoice_Header_No]


FROM [NavisionReplication].[dbo].[Tbl_Sales_Invoice_Header]



now , how would i do that?

View 2 Replies View Related

Can I Export Tables So That Existing Tables In Destination Database Will Be Modified?

Jul 20, 2005

I'm working on an ASP.Net project where I want to test code on a localmachine using a local database as a back-end, and then export it tothe production machine where it uses the hosting provider's SQL Serverdatabase on the back-end. Is there a way to export tables from oneSQL Server database to another in such a way that if a table alreadyexists in the destination database, it will be updated to reflect thechanges to the local table, without existing data in the destinationtable being lost? e.g. suppose I change some tables in my localdatabase by adding new fields. Can I "export" these changes to thedestination database so that the new fields will be added to thedestination tables (and filled in with default values), without losingdata in the destination tables?If I run the DTS Import/Export Wizard that comes with SQL Server andchoose "Copy table(s) and view(s) from the source database" and choosethe tables I want to copy, there is apparently no option *not* to copythe data, and since I don't want to copy the data, that choice doesn'twork. If instead of "Copy table(s) and view(s) from the sourcedatabase", I choose "Copy objects and data between SQL Serverdatabases", then on the following options I can uncheck the "CopyData" box to prevent data being copied. But for the "CreateDestination Objects" choices, I have to uncheck "Drop destinationobjects first" since I don't want to lose the existing data. But whenI uncheck that and try to do the copy, I get collisions between theproperties of the local table and the existing destination table,e.g.:"Table 'wbuser' already has a primary key defined on it."Is there no way to do what I want using the DTS Import/Export Wizard?Can it be done some other way?-Bennett

View 3 Replies View Related

Need Help To Create Tables Based On Word Doc Table

Nov 23, 2007

Hello,

I'm looking for the best solution for creating database tables based on
a Word Document with a table. I've attached part of the table from the word doc. This document is base on a template for audit reporting.

What I assume is I would need first to start of by creating two tables.
One table would consist of the fields of the horizontal upper most data e.g.
conformance, noncomformance etc. The other table would consist of the vertical fields of the word doc table, e.g. editorial, Auditee Name etc. If my assumption is correct, what primary key and foreign key would I need to have?

Any help would be much appreciated.

Thanks

Karl

View 3 Replies View Related

Create A View Or Temp Table From 2 Tables

Jan 4, 2008



I have 2 tables:


Customer Table: ID, OrderID (composite key)

100, 1
100, 2
200, 3
200, 1
Order Table: OrderID, Detail

1, Orange
2, Apple
3, Pineaple


Assuming each customer always orders 2 items. I need to create a SQL query that shows as following (a view or a temp table is OK). How do I do that?


CustomerID, Order Detail1, Order Detail2

100, Orange, Apple
200, Pineaple, Orange



View 10 Replies View Related

Using CREATE TABLE DDL On Excel Creates Two Tables

Mar 11, 2008

Hello. I am using OLEDB 4.0 to create an excel file and store DataTable rows into it. I am using

CREATE TABLE [SheetName] ([Column1] longtext, [Column2] longtext....

DDL and then write data using INSERT INTO ... DDL.

There are three strange things that i've encountered and will be glad to get help from anyone:



If I use as a table name expression with spaces, the name is changed to expression with underscore '_' symbol instead of spaces.
I can not set trailing '$' when creating a table, but when inserting data I have to use the name with traling '$' otherwise the exception is thrown. Moreover, if i get the scheme of the Excel file later I am getting TWO tables instead of one: a first with the name without '$' and another the same with trailing '$'. Nedless to say that visually Excel shows only a name without '$'
If I am trying to do same operations using OLEDB 12.0 (Office2007) I get invalid file.
If anyone knows how I can overcome above issues, please write me a code. I am coding in C# but VB examples are as good as any other.

View 5 Replies View Related

SQL 2012 :: Generating CREATE TABLE Scripts For Large Number Of Tables

Feb 11, 2014

Other than right-clicking on each individual table in SSMS and generating a CREATE script, is there a simple way to generate CREATE TABLE scripts for tables within a given database?

Background: I have a bunch of tables in one database, and I would like to add tables to a second database that have the same names and basic structures of some of the tables from the first database.

I do not need to transfer any data from the tables, this is a seperate project that will use a similar data structure. I just want to generate the CREATE TABLE scripts for 30ish tables within the first database, and then I'll tweak the scripts as appropriate and run them against the new database.

[URL] ....

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

Using Existing Database Or Should Create New One

May 7, 2008

I've plan to writing Stored Procedure ("SP"). This SP contains program that produce a result and this result will insert into already define summary table. This SP will running every 30 minutes.

What is the best solution? I should put this already define summary table in existing Database or i should create a new database and create the summary table into this new database. Can i using ReportServer Database to create this summary table?

I'm using SQL Server 2005.

View 4 Replies View Related

Create A Empty DB From A Existing DB

Dec 26, 2005

how to: create a empty DB from a existing DB keeping all fields and keys intact

View 3 Replies View Related

Create A Testing Database Out Of Existing DB

May 24, 2007

Hello All,
 I was wondering if anybody can help me with the following question:
I'm working on the application where the Database, it's table (2) and several stored procedures are involved. The database is SQL Server 2000. It's also very old and involves a lot of operations, stored proc and so on. I just need to re-write a piece of the app which is using existing stored proc. Most of them are DELETE, INSERT and so on. I don't want to work with real stage DB and need to make a copy of the Database to my Dev box. So I tried:
* Right click, All Tasks, Export Data into the newly created database on my dev box.
That doesn't work, every time I try doing it, it fails somewhere in the middle of the process. I'm thinking it happens because of complexity of the database. I tried several options there already. Still nothing. I need the whole databse to be copied because I'm not sure which stored proc the app is using so I need them all, and tables too. Is there another way of doing this?
Thank you,
Tatyana

View 2 Replies View Related

Create Index With Drop Existing

Nov 7, 2000

If I rebuild the clustered index using this option (Drop Existing) without specifying the Fill Factor, would it it retain the original Fill Factor?

Thank you

View 2 Replies View Related

Can't Create Or Edit Existing Objects From SQL SRV 7.0

May 4, 1999

Hello,

I just upgraded our existing 6.5 installation with the new SQL server 7.0. I can't get any of the existing stored procedures which I imported from 6.5 into 7.0 to allow me to edit them. I do all of my design from Visual InterDev and the SQL Server 6.5 version would allow me to create and edit stored procedures. It would also allow me to create/design new tables.

With SQL Server 7.0 I do not have an option to edit or create any of these items. I have created a new login, assigned it a password, given it admin rights/roles and I am still unable to remotely create these items. What am I doing wrong?

Thanks for any assistance
Doug

View 1 Replies View Related

Create .sdf From Existing SQL Server 2005 DB

Aug 22, 2007

Hello ,



1.Is there a way to generate an .sdf (SQL CE DB) from an existing SQL Server 2005 DB?
So that the sdf file has the same tables and data as SQL server 2005 DB.

2.Is there a way to copy data in Excel file to an .sdf file (SQL CE DB)?

Thanks,
Rookie

View 6 Replies View Related

How Do I Create A Copy Of An Existing Database? Please Help

Aug 10, 2007

Hi,

How do I create a copy of an existing database using vb.net? I have not been able to find solutions to the problem using vb.net. Can someone please help me. Thanks

Thanks

View 3 Replies View Related

Cannot Create New Or Change Existing Subscriptions

Jun 2, 2006

We are having problems when creating new subscriptions or when trying to edit existing subscriptions. When editing an existing subscription, the report manager displays "An Internal error occured" message and when I look in the log the error says
Only members of sysadmin role are allowed to update or delete jobs owned by a different login

When trying to create a new subscription the report manager displays "An internal error occured" and the log says
System.Data.SqlClient.SqlException: The schedule was not attached to the specified job. The schedule owner and the job owner must be the same or the operation must be performed by a sysadmin.

We are running sql reporting services 2005 sp1. The report server database server is on a different machine to the reporting server. The existing subscriptions were set up previously on another report server which was originally our dev server. These subscriptions are working just can't change them, or create new ones.
Any help is much appreciated.

View 23 Replies View Related

Create Full Sql Script From Existing Mdf

Dec 6, 2006

Hi,

In most books on ADO.NET programming, a sample database is given as a series of sql instructions (create database, create table, insert into table values (..), etc ), thereby creating the complete mdf/database file. The question arises: how does one create such a SQL script file from an existing .mdf using SSMSEE/SQL Server 2005 Express?

Cheers,

Daniel

View 3 Replies View Related

Import Csv Data To Dbo.Tables Via CREATE TABLE && BUKL INSERT:How To Designate The Primary-Foreign Keys && Set Up Relationship?

Jan 28, 2008

Hi all,

I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations:

-- ImportCSVprojects.sql --

USE ChemDatabase

GO

CREATE TABLE Projects

(

ProjectID int,

ProjectName nvarchar(25),

LabName nvarchar(25)

);

BULK INSERT dbo.Projects

FROM 'c:myfileProjects.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO
=======================================
-- ImportCSVsamples.sql --

USE ChemDatabase

GO

CREATE TABLE Samples

(

SampleID int,

SampleName nvarchar(25),

Matrix nvarchar(25),

SampleType nvarchar(25),

ChemGroup nvarchar(25),

ProjectID int

);

BULK INSERT dbo.Samples

FROM 'c:myfileSamples.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO
=========================================
-- ImportCSVtestResult.sql --

USE ChemDatabase

GO

CREATE TABLE TestResults

(

AnalyteID int,

AnalyteName nvarchar(25),

Result decimal(9,3),

UnitForConc nvarchar(25),

SampleID int

);

BULK INSERT dbo.TestResults

FROM 'c:myfileLabTests.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO

========================================
The 3 csv files were successfully imported into the ChemDatabase of my SSMSE.

2 questions to ask:
(1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables?
Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period?
(2) How can I set up the relationships among these 3 dbo Tables?

Please help and advise.

Thanks in advance,
Scott Chang

View 6 Replies View Related

How Can I Create An Install Script Using An Existing Database?

May 19, 2005

I have a database full of tables but no install script. Is there a way
I can use EM or QA to create a create database with all the tables
script?

What I am trying to avoid is going through by hand to all these tables and writing out the sql to create the tables...

I know you can do it one table at a time in QA by clicking on the table
and choosing 'Script object as new', but I was wondering if there was a
way to do this for an entire database at once, instead of one table at
a time...


Thanks!

View 2 Replies View Related

DTS To Create New Excel Sheet In Existing Workbook

Jan 12, 2004

Has anyone been able to create a DTS that will create a new sheet within an existing Excel file. I want one Excel file with multiple tabs referencing the beginning of each week and growing. The name needs to be dynamic so as to not overwrite previous sheets (tabs) in the file.

I have a current ActiveX script that will move the data to a dynamic name (date), I just can't create the table (sheet/tab) to move the data to.

Any help would be appreciated.

-Mark

View 6 Replies View Related

Does SSRS Have To Create Its Own Database? Or Can It Use An Existing Empty One?

Sep 10, 2007

Hello everyone,


Greetings! I just found this group after wasting hours trying to make
SSRS work with databases that are already created (that is, the DBA
created two empty databases for me, X and XTempDB).



I keep getting "this connection string is invalid can't use this to
connect to SQL Server" in the Database Setup tab.



The problem is that to create its own database, SSRS needs dbcreator
and securityadmin roles (please correct me if I'm wrong here). And I'm
having a helluva problem trying to convince the DBA to give me
temporary dbcreator and securityadmin roles!

Is there any document or
something that I can use to convince him that SSRS will not do
anything like recreating the login (because he said that the login has
already existed and has its role set up in many other databases)?


Any help, pointers, suggestions, etc. is very much appreciated.



Thanks,
Ray

View 7 Replies View Related







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