I 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
Hi, I have the lovely task of overhauling some of our SQL-based systems. I've found many tables that don't have unique identifying numbers that really should have them. I've searched around and people keep mentioning the Identity field as being similar to Autonumber in Access. The only examples I could find involved setting up a new table... but I need to add (and populate) an identity column to an existing database table. Does anyone know the command for this?
Example... my table is called PACountyTown. It currently has 3 columns: County, Town, and Area. I wish to call the identity-ish field RecordID.
Thanks in advance!
I have a table which is already populated with data (Microsoft SQL 2008). I have now created a new column (int) which i want to populate with sequential numbers so that the new column created will serve let me know how many records exist in the table at a glance.
what SQL statement I need to write that will automatically polulate the newly created column with 1,2,,3,4,5 etc so that I can sort of number the records within the table.
I have 50000 records which I need to number and I really dont want to number the column manually via hand editing.
I 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
)
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. ..
i 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
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
I 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.
HiI'm developing a website using SQL Server Express 2005 and VIsual Web Developer Express, and now want to start testing my website in a live environment. Until now I have been manually creating tables on my web database to match those on my local DB, but I have so many tables I don't want to manually recreate them all again. Is there a method in SQL Server Express 2005 that will allow me to generate insert scripts for all the tables? CheersAndy
View 3 Replies View Relatedhi i have a table with Id and otehr fields,ID is autogenerate with datatype int.
But now i want to generate an Id in AU001 format and then want to insert into table where it must be autogenerate.How to do this.
Please help me with the problem,its very urgent....
Hello everybody
I am doing update on Database using Erwin
Is any diffrence in this code and what is is right way to create table in my case ?
-- --- this code generated by Erwin 3.5.2
CREATE TABLE obooGrpAllotRmBooking (
BookingDate ASmallDateTime NOT NULL,
ID_RoomType ID NOT NULL,
ID_GrpAllot ID NOT NULL,
ContractQty hAvailQty DEFAULT 0
)
go
ALTER TABLE obooGrpAllotRmBooking
ADD PRIMARY KEY (ID_GrpAllot, ID_RoomType, BookingDate)
go
------------- This code generated by SQl server 7 generate script ---
CREATE TABLE [dbo].[obooGrpAllotRmBooking] (
[ID_GrpAllot] [ID] NOT NULL ,
[ID_RoomType] [ID] NOT NULL ,
[BookingDate] [ASmallDateTime] NOT NULL ,
[ContractQty] [hAvailQty] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[obooGrpAllotRmBooking] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[ID_GrpAllot],
[ID_RoomType],
[BookingDate],
[ContractQty]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[obooGrpAllotRmBooking] WITH NOCHECK ADD
CONSTRAINT [DF__obooGrpAl__Contr__78DED853_05] DEFAULT (0) FOR [ContractQty]
GO
Thanks
Hi,
How can I generate the user SID on ReportServer.dbo.Users table?
Tks
I am working on SQL SERVER schemas using a plug-in in SQL developer itself now my requirement is to generate DDL for a table which is part of SQL SERVER schema...
View 3 Replies View RelatedHi
i have one problem in database to creating SQL script
i want to copy table strucure with table data
Pls help
Shailesh Kavathiya
http://www.codegroups.com/blog/
Hi!!!
Can anybody help me with the generation of Table of Contents for a report using SQL Server 2005 Reporting Services. Let me ellaborate. The scenerio is...i m having a report of lets say 500 pages grouped on employees, showing the performance of each employee between specific date range. Now if the manager prints the report of 500 pages he will be more intersted to jump directly to a perticular employee's page which means my printed report had to have a Table of Contents on my grouped criteria (which in this case is employee number). I would really appriciate if someone can suggest me a solution for this.
I'd like to know more about data warehouse. I've known some for SSIS working with SSAS. But it's existing data warehouse. I just transfer data to data warehouse using bussiness rules. I have no idea about data warehouse, how to design data warehouse from transactional database, how to generate the table structure. Any tips or books for that? Thank you very much.
View 5 Replies View RelatedI 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 ~
Hi,
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
Hi,
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,
I 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?
Hi 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
i 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 RelatedHi 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 RelatedI 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 Related
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
Hi 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
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
Hi,
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. :)
hi,
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.
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?
Hai allI am new to SLQ server. Can anyone tell me how to create a table thatshould look like a existing table fields....thanx
View 2 Replies View Relatedhi
I have a field call: password, I want to generates some random chars as
default password for users (so user got something to login, and update
later).
Current Solution I have:
I made myself a trigger and it does generate password on insert, but
the problem with that is I have to allow NULL on that field. I
don't want NULL to be allowed, and if I don't allow null... sql server
won't allow me to insert a record (when leaving password field empty).
Reason i don't wanna allow NULL on that field, is because there may be
more applicaions using the same field from the same database.
Just in case if the other developer gets sloopy, I don't want other
application(s) allow user put to enter a null or leave it blank on my
password field.
My question is:
1) can I put a function or some sort at the "Default" field under "Design Table", if so...how?
2) any suggestions for a better implementation?
3) if (1) doesn't work, what other options would you suggest other then the current trigger i'm using?
TIA
I'd I have a problem I'd like to post CREATE TABLE and INSERT statementsthat will create my table and insert data into the table.I can use the scripting feature in Enterprise Manager to generate CREATETABLE scripts.Is there a script I can run that will generate INSERT statements so I caninclude sample data.Thanks
View 1 Replies View Related