I am having problems creating a table in my first database.
When I script:
CREATE TABLE ship (mmsi INT, time INT, longitude INT, latitude INT, cog INT, sog INT, heading INT, navsat INT, imo INT, name CHAR, callsign VARCHAR, type INT, a INT, b INT, c INT, d INT, draught INT, dest CHAR, eta INT); I am returned with an error 1064 with regads to the attributes from "Typ" onwards.
From what I can see this should work.
hi, i'm trying to create a table and populate it with data from another database residing on the same server. i've done this on oracle using tables within the same database and am just making a first effort with the added twist of a different database. this is what i've been doing so far...
CREATE TABLE facility_dimension
(
fac_id INT IDENTITY(1,1),
tri_fac_id CHAR(17),
fac_nameVARCHAR(100),
street VARCHAR(100),
city VARCHAR(100),
county VARCHAR(50),
state VARCHAR(4),
longitude REAL,
latitude REAL,
PRIMARY KEY(fac_id)
)
SELECT tri_facility_id, facility_name, street_address,
city_name, county_name, state_abbr, fac_latitude,
fac_longitude
FROM TRI_2004.form_1;
....where TRI_2004 is the other database and form_1 is the table. the result is creation of the new table and then the output of the secondary query. i'm assuming this can even be done but if it can't that would be helpful to know as well. thanks in advance!
Hi
I created database using SQL server and runs under Cassini. The creation of database is ok but I have a problem when I am creating the table in database. Whenever I execute the code to create the tables, it shows the error message like 'Server not found' or just hang there. Does anybody know why it hang while I am creating the table? Is it because of the code or it's the time out error. Pls help as I am very new to this area.
MZ
Hi All,I want to use MS sql server edition 2000. I have installed the server. Now iwant to create the database on this server with tables and triggers. Canplease someone suggest me how to do this ?Thanks a lot,Dinesh
View 1 Replies View RelatedI need to create a database and then create tables in it..
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'DB1')
DROP DATABASE [DB1]
-- create a new database
CREATE DATABASE [DB1] ON PRIMARY
( NAME = N'DB1', FILENAME = N'D:DB1.mdf' ,
SIZE = 51200KB , MAXSIZE = UNLIMITED, FILEGROWTH = 30720KB )
LOG ON
( NAME = N'DB1_log', FILENAME = N'D:DB1_log.ldf' , SIZE = 2048KB , MAXSIZE = UNLIMITED , FILEGROWTH = 30720KB )
COLLATE Latin1_General_CI_AS
CREATE TABLE [DB1].[dbo].[SALES](
[PERIOD] [int] NOT NULL,
[LOC] [nchar](3) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
If i run the above statement one after one, they work; however, if I run all of them together (or in sp), the following error raised:
Msg 2702, Level 16, State 2, Line 43
Database 'DB1' does not exist.
May I know how can i create a database and then immediately the tables in sp!
Thanks
I have a question regarding the dynamic sql. I have a script which which deletes all the foreign keys and re- creates it. So, I have created a table instead of a temp table. Now I need to create that foreign keys table in such a way that in which ever database I run the script, this  foreign keys table should be created in that particular database.
For example:
IF OBJECT_ID('tempdb..keys','U') IS NOT NULL
DROP TABLE keys
CREATE TABLE keys.[@sourceserver],[@database]. (RowId INT PRIMARY KEY IDENTITY(1, 1),
ForeignKeyConstraintName NVARCHAR(200),
ForeignKeyConstraintTableSchema NVARCHAR(200),
ForeignKeyConstraintTableName NVARCHAR(200),
ForeignKeyConstraintColumnName NVARCHAR(200)
I am trying to use the following Query to create the table Agents and add rows to it.
USE REMAXCLASSIC;
IF OBJECT_ID ('dbo.Agents', 'U') IS NOT NULL
DROP TABLE Agents;
GO
CREATE TABLE Agents
[Code] ....
I get the following error messages:

Msg 102, Level 15, State 1, Line 34 Incorrect syntax near '0.25'
Msg 105, Level 15Â mark after the character string ');
I'm wondering if there is some sql I can run to check properties on a table. This would be used to verify things like data types, allow nulls and default values have been set to avoid mistakes. This could be done manually one table and one column at a time, but it would be a lot easier to look at it in the results window.
View 5 Replies View Related
Hi,
If I want to automatically insert a record which has default value in a table,
how can I create the trigger?
Banti writes "IF i create temporary table by using #table and ##table then what is the difference. i found no difference.
pls reply.
first:
create table ##temp
(
name varchar(25),
roll int
)
insert into ##temp values('banti',1)
select * from ##temp
second:
create table #temp
(
name varchar(25),
roll int
)
insert into #temp values('banti',1)
select * from #temp
both works fine , then what is the difference
waiting for ur reply
Banti"
Using SQL against a DB2 table the 'with' key word is used todynamically create a temporary table with an SQL statement that isretained for the duration of that SQL statement.What is the equivalent to the SQL 'with' using TSQL? If there is notone, what is the TSQL solution to creating a temporary table that isassociated with an SQL statement? Examples would be appreciated.Thank you!!
View 11 Replies View RelatedI am trying to create a table that holds info about a user; with the usual columns for firstName, lastName, etc.... no problem creating the table or it's columns, but how can I "restrict" the values of my State column in the 'users' table so that it only accepts values from the 'states' table?
View 2 Replies View RelatedHere is my code
ALTER PROCEDURE Test
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @From varchar(10)
DECLARE @To varchar(10)
DECLARE @DBName varchar
SELECT TOP 1 @From = CONVERT(char,CreateDate,101) FROM CustomerInfo
WHERE TicketNum =
(SELECT TOP 1 TicketNum FROM CustomerInfo
WHERE CreateDate <= DATEADD(mm, -30, CURRENT_TIMESTAMP)
ORDER BY CreateDate DESC)
SELECT @To = CONVERT(char,GETDATE(),101)
SET @DBName = 'Archive_SafeHelp'
CREATE DATABASE @DBName + ' ' + @From + ' ' + @To
END
I am trying to create a database based on the name contained in the variables. I get the error 'Incorrect syntax near '@DBName'. How do i accomplish this?
Thanks
Ganesh
Hi guys, can I know the steps on creating a database snapshot on a mirror database? Thx for the assistance. :)
Best Regards,
Hans
Hello all:
Is it possible to creates fields of the table dynamically?. I have this situation in my project. This is just a small sample. I have row of length 140. I don't wan't to declare all this fields manually using the create table command.
The description of table is as, in this table all the field are of type varchar only, there are like 140 columns.
create dummy emp (
field1 VARCHAR(100), field2 varchar(200), field3 VARCHAR(100).... )
Table: Dummy
================================================== ==
field1 field2 field3..........
Empid Empname empaage1 sam 23...........
2 rai 22............
.
.
.
n raj 45.............
================================================== ==
Now I want to create another table as "EMP" , with proper data type
fields too..
create table emp (
empid int, empname varchar(100), empage int....)
The table should look like as:
Table: EMP
================================================== ==
Empid Empname empaage............
1 sam 23...............
2 rai 22................
.
.
.
n raj 45.................
================================================== ==
I want to do this dynamically.....
Some how I need to extract those field from table[dummy]; the first row acts as a column header for the table[Emp] and the subsequent row acts as a record for the table[Emp]
A small rough snippet of the code will be appreciated....
Waiting for replies........
saby
I am using a Stored procedure in which I am creating a table and dropping that table at the end of execution. This SP is calling every 10 second (but no concurrent access) from my application. Is there any issue using the drop table command in the SP? will it create any memory fragmentation issue in SQL server?
View 7 Replies View RelatedI am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
Any help would be greatly appreciated.
Current Table
Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40
Proposed Table
Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008
Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008
Thanks,
Mike Misera
Hi All,
I have a situation with a table that was created for a transactional
system with a 3 columns key. The table is similar to the following:
countrystatecitydescription
11221City A from country 1 and state 12
11321City A from country 1 and state 13
21422City B from country 2 and state 14
21522City B from country 2 and state 15
Now I'm trying to create a dts package that would allow me to build a
city dimension table with unique codes (keys) for each city. What kind of
transformation should I use to translate the old codes (based on the
country-state-city key) into the new ones and preserving the data
integrity?
Thanks,
Ignacio
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. :)
I'm trying to create programmatically a table in my db on sql server 2005.1. I created a string "CREATE TABLE ....." and then tried to execute it using my ADO. That didn't work, so I copied the string into a query in sql server and got the message "CREATE TABLE sql is not supported". Why's that? 2. I created a stored procedure in my db that will get the "CREATE TABLE...." string and execute it. I thought it would look something like this: parameter @createString varchar(MAX) BEGIN BEGIN EXEC @createString or @createString END END Both these options didn't work.How can I make it work properly?Thanks.
View 3 Replies View RelatedI need a module to help me create a SQLServer database table. Is there a module that allows users to specify parameters in the creation of a SQLserver table?
View 1 Replies View RelatedHello, folks.
I wonder whether I can create a table based on the script in SQL Analyzer.
If this is possible, can you folks tell me how to do it.
Thanks in advance.
Hyunhyo Jun
GIS Research Group
University of Colorado
Does anyone know if it is possible to create a tmp table in RAM without pinning it and how to do it? I already knew that SQL has a stored procedure allows pinning a table in RAM.
Thank you for any input.
Hi
I am trying to create a table in sql server 2000 and seem to be getting an error
the code that i am using is as folows:
CREATE TABLE Invoice
(
InvoiceID INT IDENTITY PRIMARY KEY NOT NULL Auto_Increment,
Invoice_no VARCHAR(50),
RefText VARCHAR(50),
CustomerDetails text,
)
I want to be able to have the invoiceid as the primary key that auto increments...
After creating the table id like to import a csv file with only the following data
Invoice_no, RefText, CustomerDetails
is it possible to do this as i want the id to automatically be generated to be unique
thanks
vijay writes "how can i creat a new table as i have in my database"
View 2 Replies View RelatedHow do you create a new table from a SELECT statement of another tableusing MS SQL Server. This is part of a distributed database topic foruniversity. Unfortunately I can only seem to get the new table createdin Oracle and not MS.
View 4 Replies View RelatedI need to create tables from a C# code. I made a stored procedure hoping that I will pass a table name as a parameter: @tabName. The procedure executed in SqlServer All Right but when I called it from C# code it created not the table I put in as a parameter but table "tabName." Otherwise everything else was perfect: coumns, etc.
Here is my stored procedure.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CreateTableTick]
@tabName varchar(24) = 0
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE tabName
(
bid float NULL,
ask float NULL,
last float NULL,
volume float NULL,
dateTimed datetime NULL
)
END
What went wrong?
Thank you.
How do you create a SQL database (Sql Express 2005) in real time using VB.Net 2005 ASP.net 2.0?
View 2 Replies View RelatedI am working on site in asp.net with c# and using database sql server 2000 and managing all the data on server i have created one database on server and in that i have created 15 table. should i continue with the same or create another database for table.
tell me how many tables should i maintain in one database. and what precausion should i take while mainting database to increse the speed of site.and what precausion should i take to avoid serverload
please guide me i dont have an idea.
thanks for spending ur valuable time for me.
Sorry, I'm a newbie out there when it comes to creating database. I currently have an ASP.Net project which requires a database. I spent the past few days thinking about how to create a proper database for the application but to no avail. I think tis thread might be out of point for tis forum, but i am really in need of help.
Here goes the storyline of my database:
The project is an online project management system, meant for students to submit their deliverables (reports, eg. Minutes, agenda, project monitor chart) Lecturers will access the web application to grade the student’s submission. The application must allow administrators to add in new subjects. When a new subject is added, the subject leader will set a dateline for a particular module to be submitted. Eg, minutes dateline. Once the dateline is due, the system will inform the lecturers in charge of who/which group did not submit the module. Lecturers and subject leaders can then generate warning letters to be sent via email to the students involved. Lecturers and subject leaders will be able to make announcements through the system as well.
A student can either submit his/her work as an individual or as a group.
A student will be studying more than 1 subject.
A subject will consist of more than 1 module.
Every module will have a dateline for its submission.
Students can make more than 1 submission for each module.
Students belong to different groups/classes for different subjects.
For different modules, there will be different fields to be inserted into the database.
Example, in the “Minutes module�, the student will have to be able to key in the objectives, the date of the meeting, the venue, its members, absent members, actual minutes, action by and meeting end time.
In the “Project monitor chart module�, the student will have to key in the week/date, the name of the member, task involved, as well as the status.
When a new subject is created, the subject leader will have to specify the percentage of each different module. (Eg, Exams – 40%, Term test 15%, and so on.)
A subject will also be taught by more than 1 lecturer. So subject leader will specify the lecturers teaching that particular subject.
I hope someone out there can help me out on the construction of a database for the above storyline. Thanks a lot.
Hi All,
Can anybody help me/does anybody have the code to create a database on ms sql server 2000 using asp.net?
This needs to be done without being asked for the sql server login credentials (i.e the sa username/password can be inserted in the code)
Is there any tools to create a database with tables, views, and all that jazz with a GUI. I went through the exammle on beta.asp.net but the way they created their db was with a script. I want to create everything with a GUI app. If this gui app doesn't exist can someone point me to where I can find more info about creating db, tables etc with sql server 2005 express? Thanks in advance.-Daticus
View 2 Replies View RelatedI have an excel document that Imported to access database. That database should count the number of tickets created by each tech rep; list the area, sub area and description of call created by a the tech rep. Can access perform this task and if yes, any idea I to approach that project or should i use SQL, And if access won't be able to do it. please make a suggestion how to approach this project. it will be nice if i can get the steps.
EXAMPLE
Request Id SRS Started Call Description Area Sub Area Request Status Closed Date Assign To Created By