Createa Table Like A Existing Table?
Dec 27, 2005Hai all
I am new to SLQ server. Can anyone tell me how to create a table that
should look like a existing table fields....
thanx
ADVERTISEMENT
Transact SQL :: How To Alter Existing Table Column As Identity Without Dropping Table
Nov 20, 2013I have created a table as below mentioned. Then I want to alter the ID column as identity(1,1) without dropping the table as well as losing the data.
create table dbo.IdentityTest
(
id int not null,
descript varchar(255) null,
T_date datetime not null
)
Create An Extra Table (for Audit Purpose) For Every Existing Table In A Database
Mar 28, 2008Hi 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. ..
Copying All Rows From One Table Into Another Existing Table And Overwriting Data
Feb 15, 2005i have 2 tables (both containing the same column names/datatypes), say table1 and table2.. table1 is the most recent, but some rows were deleted on accident.. table2 was a backup that has all the data we need, but some of it is old, so what i want to do is overwrrite the rows in table 2 that also exist in table 1 with the table 1 rows, but the rows in table 2 that do not exist in table one, leave those as is.. both tables have a primary key, user_id.
any ideas on how i could do this easily?
thanks
Tempoary Table Question - How To Create One With The Same Schema As An Existing Table?
Jul 28, 2006Hello,
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
SQL 2012 :: Importing Excel Table Into Existing Table?
Aug 25, 2014I am using the DTS wizard and having problems importing excel into an existing table.
Problem is that various column in excel are defined as double in the wizard but in my db table it is defined as an integer.
How do I get around this issue so the data types in excel can match up accordingly to my defined data type in my db table?
The wizard does a bad job of guessing the correct data type.
I have heard of using a staging table to import from excel and using that as my source to import into my existing table.
Create New Table From Existing Table
Feb 16, 2007Hi
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
Creating New Table From Existing Table
Apr 20, 2006Hi,
I m new to this forum with a Query
Create table <table name1 > as select * from <table name2>
this works for oracle. do anybody knows its alternative in sqlserver 2000
thanx. :)
Design A New Table From Existing Table
Jun 3, 2008hi,
i have a table with data in the following format (dont ask why it was done this way, its not something i can change)
so my table looks like this:
point :timestamp :value
_________________________
var1 11:00 5
var2 11:00 2.3
var3 11:00 21
var1 12:00 5.2
var2 12:00 2.4
var3 12:00 18
and so on.
i would like to have my data rearranged to look like the following:
timestamp :var1 value :var2 value :var3 value
________________________________________________
11:00 x y z
12:00 a b c
....
i am working with my database in a c sharp application. so if anyone does come up with a solution for this maybe keep that in mind so that i can discuss how to impliment it in the code.
thanks guys.
How To Create A New Table From Existing Table
Aug 19, 2005Hi 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?
I Want To Move An Existing Table From Another
May 28, 2007I want to move an existing table from another.
Example
B.mdf dbo.xx to C.mdf dbo.xx
B.mdf dbo.xx has records - I created a script and install the script for C.mdf dbo.xx
C.mdf dbo.xx is empty -
All I need now is to populate the records from B.mdf dbo.xx to the empty C.mdf dbo.xx
I was telling I could do a cut and paste to copy the dbo.xx
Thank you in advance.
~ Peaches ~
How To Add A Table To An Existing Publication?
Feb 22, 2002Hi,
I would like to know the procedure to be followed to add a table to the existing publication. The subcriber doesn't have the object yet. I have data in the source table and I want to add this table in a existing publication for transactional replication. Any advise is appreciated.
Thanks,
Ravi
Add Data To Existing Table
Jun 6, 2008Hi,
How would I select from TABLE A and add this data to TABLE B which has existing data that I want to keep. I would like to keep adding onto the old data. When I do an "Insert Into", it keeps on deleting my old data.
Thanks,
How To Add PK As Foreign Key To Existing Table
Dec 27, 2013I have created two tables in phpMyAdmin 3.5.8.1. comments which stores comments users post on a website and registration which stores a users registration details (username, password, e-mail etc, etc).
I want to add the username field from the registration table as a foreign key to the comments table. How do I do this in phpMyAdmin?
How Can I Add A Column To An Existing Table
Nov 14, 2007Hi everyone!
I just started with SQL and SQL Server 2005. I cantfind in my books how to add a column to an existing table. It should be a primary key, auto_increment column.
Hope someone can help, many thanks in advance
Greetings from Vienna Austria
landau
How To Add Primary Key In Existing Table
Aug 17, 2006i have table fff .it has two fields one is fno int , another is fnamevarchar(20)ffffno fname-------- -----------100 suresh102 rameshhere there is no not null constraint and identity column theni am add primary key constraint fno column pls help me
View 1 Replies View RelatedAdd A Datestamp To An Existing Table
Dec 4, 2007Hi all,I have an existing table(Several actually) that I want to add a columnor two or three. I need to have, I think, at least two columns. Onefor Created date and believe this is a no brainer in that I just addthe column and set the default to getdate(). Two, for tracking amodified date of the row of data. I want to keep it simple. What do Ido to track the modify date of a row in SQL Server 2005? Anytime anydata changes on a row update the update column? Is a trigger the onlyway to accomplish this task?Another column could be used to track who changed it. Is there a neatway to determine username, machine name, MAC address or any othermethod? I pretty wide open on this one as I am not sure what I want touse here.TIA!!!
View 16 Replies View RelatedCapture Existing Table
Jul 20, 2005I trashed SQL Server2000 when I added the WINXP SP2, and had to reinstallit. When I did, my database(DEV_DATA) remained intact, but when I go inthru Enterprise Manager, it is not located in the system tree, and istherefore inaccessible. Can anyone suggest how I could get this databaseincluded.
View 4 Replies View RelatedExisting Table Gets A New Partition
Feb 13, 2008
How do you alter the table to use the new partition (I know ALTER TABLE is in there but BOL doesn't give a valid example with the move option)? I can create the partition but I want to apply it to an existing table with no partition?
Thanks
How To Generate DDL Of An Existing Table
Feb 25, 2007I need sql script that can generate 'Create Table' definition including primary and foreign constraints for the table already existing in SQL server 2000.
Please do not refer me to the utilities or commercial software, I need only the sql script.
thanks
RDA Pull With Existing Table
Jan 19, 2006Hi Forum,
I am currently implementing RDA Pulls and Pushes. Both worked fine for me, except when I try to pull a certain table twice. I read that in order to pull the table a second time I must drop it on the client.
My original approach was to use a select statement with a where criterium in the pull statement (e.g. SELECT * from tblPhonebook where Pulled = 0), then set Pulled to 1 and pull again later.
My understanding of RDA was that I use the where criterium to filter the data at the server side and simply append that data at the client side.
Can I simply append data in some way or do I really have to drop the table on the client side every time I pull?
Regards
Tobias
Add SQL Count Column To Existing Table
Apr 22, 2008I have a table for blog comments I want to add a column that counts the number of comments for each article.
existing table looks like this:
CommentID
ArticleID (FK)
commentAuthor
authorEmail
comment
commentDate (getDate())
I would like to add a column that counts the number of total comments for each article.This will give me what I want using the VS query tool:
"SELECT COUNT(comment) AS Expr1 FROM UserComments WHERE (articleid = @articleid)"
But can I add that to the table somehow so it does it automagically???
Select Date Only Existing In One Table From 3
Dec 13, 2004Hi the subject matter pretty much sums up what i need, a piece of sql code that queries 3 or more tables and selects only those records that have a date field that only occurs in one of the tables. So for example if one table has 20 records with a date of 12/12/04 and the other 2 tables do not have any records with a date of 12/12/04 then i only want to select those records with a date of 12/12/04.
Thanks in advance for the help
Adding A Field To Existing Table
Jan 13, 2005I want to add a computed field to an existing SQL table:
where Field1 is >0 and field2 is not null
set newfield = 'Y'
else set newfield = 'N'
I want to keep this existing table because I'm using it as the basis for an Access Report that is nearly complete.
BTW this table is the result of a DTS package that is comprised of several SQL executables so I need to be able to repeat this as part of a larger process.
Thanks!
sgreene
Changing Field Name Of An Existing Table
Jan 17, 2006Hi,
Is it possible to change any fieldname of an existing table?I mean to say
by TSQL statement.We know that we can alter the data type and width etc.
But I haven't got any info about filedname change.So if it is possible
Please help...
And Is there any TSQL command to alter multiple columns in a single statement?
Please help...
Thanks!!
Joydeep
How Do I Insert Into Existing Temp Table?
May 2, 2006Hi,
How do I insert data into an existing temporary table? Note: I’m primarily a .NET programmer who has to do T-SQL to grab data from time to time.
What I am trying to do is this:
1) Put the scores for all the people who have completed a questionnaire into a temporary table called #GroupConfidence.
2) Add on a row at the end that gives an average for each score (ie the last row is an average of the column above).
I need my SP to give me a DataSet that I can throw straight to my .NET reporting engine (I don’t want to do any number crunching inside .NET) - that's why I want to add on the 'average' row at the end.
If I do this (below) the temporary table (#GroupConfidence) gets created and the values inserted.
-- Insert the results into the #GroupConfidence table
SELECTRTRIM(UC.FirstName + ' ' + UC.LastName) AS 'FullName',
RP.SubmitID,
RP.GL_Score,
RP.GP_Score,
RP.GPH_Score,
RP.DL_Score,
RP.MP_Score,
RP.Role_MI_Score,
RP.Role_ASXRE_Score,
RP.Role_APRA_Score,
RP.Overall_Score AS 'AllCategories'
INTO#GroupConfidence
FROMRodResultPercentages RP
JOIN#UsersCompleted UC ON UC.SubmitID = RP.SubmitID
My problem is that #GroupConfidence already exists so in fact I have this code below:
CREATE TABLE #GroupConfidence
([FullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SubmitID] [int] NOT NULL,
[GL_Score] [decimal](19, 10) NOT NULL,
[GP_Score] [decimal](19, 10) NOT NULL,
[GPH_Score] [decimal](19, 10) NOT NULL,
[DL_Score] [decimal](19, 10) NOT NULL,
[MP_Score] [decimal](19, 10) NOT NULL,
[Role_MI_Score] [decimal](19, 10) NOT NULL,
[Role_ASXRE_Score] [decimal](19, 10) NOT NULL,
[Role_APRA_Score] [decimal](19, 10) NOT NULL,
[AllCategories] [decimal](19, 10) NOT NULL
)
-- Insert the results into the #GroupConfidence table
SELECTRTRIM(UC.FirstName + ' ' + UC.LastName) AS 'FullName',
RP.SubmitID,
RP.GL_Score,
RP.GP_Score,
RP.GPH_Score,
RP.DL_Score,
RP.MP_Score,
RP.Role_MI_Score,
RP.Role_ASXRE_Score,
RP.Role_APRA_Score,
RP.Overall_Score AS 'AllCategories'
INTO#GroupConfidence
FROMRodResultPercentages RP
JOIN#UsersCompleted UC ON UC.SubmitID = RP.SubmitID
So I get this error: Server: Msg 2714, Level 16, State 1, Line 109
There is already an object named '#GroupConfidence' in the database.
Thanks in advance,
Ian.
Replacing Existing Records In A Table
Feb 5, 2007I have a table that stores all the processed data from other tables. How can I replace the same data in this table when I do "reprocessing"? It's kinda like a combination of delete then insert kind of thing. I cannot simply insert as it will become duplicates. any idea?
View 3 Replies View RelatedCreate New Table From Several Existing Tables?
Apr 8, 2012I 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
How To Add Identity Property To Existing Table?
Mar 2, 2004Has anybody ever tried to do this. I can't figure it out. All I want to do is take an existing table that already has values in the column that I want to change and add the identity property to yes and set the identity seed and increment to a specific number. I know you can do it in the CREATE TABLE statement but is there a way to use the ALTER TABLE command?
View 4 Replies View RelatedInserting A Column In An Existing Table
Mar 5, 2004I have an existing table (see below).
-----------
[FormCode] [varchar] (4) NULL ,
[FiscalYear] [char] (4) NULL
-----------
I want to add the column below after the [FormCode] when my SPROC runs.
-----------
[FiscalMonth] [char] (2) NULL
-----------
Any ideas would be a big help?
TIF
Adding Identity For Existing Table
Apr 26, 2008Hi Friends,
I have a existing table named as activity, and have the column like ID,Description. I want to add the Identity for the ID column using script only.. Have any ideas how to do in sql query analyser?
Thanks in Advance
Inserting Rows Into Existing SQL Table
May 21, 2008Hi all,
I am trying to insert some new rows into an existing SQL table. The table name is Agt_table, and I want to add some data for some new agents into existing columns:
Agent name, agent code, phone number, fax number
Example -
I want to add the following record to my existing table Agt_table
Agent name: ABC Company
Agent code: 012345
Phone #: 555-555-5555
Fax#: 555-555-5555
How would I write the sql statement to do that?
Thanks so much!!!
Mass Import Into Existing Table
Sep 17, 2013I have an existing SQL table that I want to import 300,000 rows into. I have copied the table headers into Excel and added all the rows but constantly getting multiple errors no matter what way I try to import it.
So far tried Import Method from SQL Management Studio, BULK INSERT and SQL Script. What is the easiest way?
If I try this
select *
into Coupon FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:coupon.xls;HDR=YES',
'SELECT * FROM [coupon$]')
I get
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.