Create Table And Fields

Jan 13, 2007

I have maked this little code but dont know the way to insertt new table and fileds in database DATABASE01...


Private Sub Command1_Click()

Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
objConn.ConnectionString = "driver={SQL Server};server=DVD377-14D9E48CSQLEXPRESS;uid=;pwd=;database=master"
objConn.Open
objConn.Execute "CREATE DATABASE DATABASE01"

''''''''''''''''''
'"CREATE table Mytable"
''''''''''''''''''

''''''''''''''''''
'"CREATE filed1" in text format
'"CREATE filed2" in text format
'"CREATE filed3" in text format
'"CREATE filed4" in date format(DD/MM/YYYY)

''''''''''''''''''

objConn.Close

End Sub

View 1 Replies


ADVERTISEMENT

Create Table,fields Come From Csv File

Oct 24, 2006

I want to create a table automatically,and fields come from a csv file

any idea? TIA

View 1 Replies View Related

Create SoreProcedure That Search In Al Table And Fields

Jun 28, 2006

i am   Create  a StoreProcedure    That  Search  in All  Tables And Filds by  Keyword
and return  one result????????

View 1 Replies View Related

How To: Create A Table That Only Allows NULL Fields To Be Updated.

Feb 26, 2008

I need to create a table that only allows fields to be updated if the field value is NULL. Is there anyway to configure a table in this manner?

In the example below the record will be created with a serial number and verification ready flag set to true. Other processes down stream will update the remaining fields as needed but should not update fields that have already been initialized.

Table Definition

USE [DB_AUTOMATED_PACKAGING_SYSTEM]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TBL_LABEL_VERIFICATION](
[SerialNumber] [varchar](50) NOT NULL,
[VerificationReady] [int] NOT NULL,
[VoidLabelImage] [image] NULL,
[VerifiedBy] [varchar](50) NULL,
[VerifiedDate] [datetime] NULL,
[Verified] [int] NULL,
CONSTRAINT [PK_TBL_LABEL_VERIFICATION] PRIMARY KEY CLUSTERED
(
[SerialNumber] 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

View 1 Replies View Related

Is There A Way To Simply Programticly Create A SQL Server Table And All The Fields?

Mar 26, 2008

I am developing a asp.net app and I have a long, very long list of data fields.
The values of the data fields have to be saved to a SQL database. The list is so long that it a pain in the ass to have to write all the code even to declare an object and all its properties by hand. So...
I made a list of all the field names and types and load it all up in an array. Then I wrote a bunch of macros to write all the properties one by one with the corredt type for the object I needed to create instead of doing it all one by one by hand.
I want to do the same thing with the Database. I dont want to have to hand define all the fiedls 1 by 1 by hand in the IDE. I want to write a simple macro, a loop theat loops thru my array and creates a field with the field name and type that it says in the array.
Simple enough.. Now I have the table defined already since that was simple enough. It is the 200 ++ fields that I dont wanna do.
So, please what is the code I need (in VB) to create 1 simple field in an existing table. Say the table is called "Table1" in the database "Database1" and the field I want is to be called "Field1" and to be 250 chr string..... ???
 Thank you Marc

View 3 Replies View Related

Create A Formula In A Column For Return 2 Fields From A Other Table

Oct 17, 2007

Hello,
I would like create in a table (A) a column with a formula's data.
In this formula I would like implement 2 fields from a Table (B)

So the formula can be :
[TABLE_B].[FIELD1] + [TABLE_B].[FIELD2]

Is it possible?
We can call 2 fields from a other table?

Thank you

View 1 Replies View Related

Very Simple Question: Cannot Create Table With Identity Fields Using SQL Sentences

Jan 26, 2007

Hi, I'm trying to build a several tables using sql ce 3.1
I refer to the manual on Create Table:
ms-help://MS.SSCE.v31.EN/ssmprog3/html/143cad25-5c1d-4c96-bd8b-6a95dbfb1b00.htm

The sample:
CREATE TABLE MyCustomers (CustID int IDENTITY (100,1) PRIMARY KEY,
CompanyName nvarchar (50))

just simply doesn't work displaying this error:

SQL Execution Error
Executed statement: CREATE TABLE MyCustomers (CustID int IDENTITY (100,1) PRIMARY KEY,
CompanyName nvarchar (50))

Error Source: SQL Server Compact Edition ADO.Net Data Provider
Error Message: There was an error parsing the query. [Token line number =1,
Token line offset 40, Token in error=IDENTITY]


I tested against a SQL Desktop, everything is OK.
What's wrong with the sentence?
Please some advise or workaround will be appreciated
Thanks
MSCD Fernando Zorrilla de San Martín

View 4 Replies View Related

Create TRIGGER Remove White Spaces From A Fields In Table-scan And Fix

Apr 21, 2008

hi
i have table i use it for update insert
and the users use this table from a grid on the web
and i need to prevent from white space in the fields in table
so how to
create TRIGGER remove white space from a fields in table scan and fix it ?





Code Snippet
SELECT TRIM(fieldname)
, LTRIM(fieldname)
, RTRIM(fieldname)
, LTRIM(RTRIM(fieldname))
FROM tablename





Code Snippet
WHERE (LTRIM(RTRIM(fieldname)) = 'Approve')




Code Snippet
replace(@text,' ','')







create TRIGGER on update insert and not to damage the text in the all fields
TNX

View 21 Replies View Related

SQL Server 2008 :: Bulk Import And Create New Table Based On Header Fields Of Imported File (XLXS)

Sep 11, 2015

I have a record in an Excel format (Excel 2010) and I would like to bulk import that into SQL Server 2008 and also while importing, SQL Server will automatically create a new table based on the header fields or row of the source file.

I am not sure if SQL Server 2008 has this capabilities.

View 0 Replies View Related

Creating A Table In SQL Server With Fields From Other Tables And Some Fields User Defined

Feb 20, 2008

How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.

View 8 Replies View Related

Does RDA Create New Fields?

Nov 20, 2007

Hi!
I need to sync an application on windows mobile 5 that uses sql server mobile with sql server 2005. I've tried Merge and it populated the tables on the server with new fields and triggers. Does RDA do the same thing?

Jesus saves. But Gretzky slaps in the rebound.

View 2 Replies View Related

Using LIKE To Find Any Of Fields In One Table In Fields Of Another

Jul 31, 2013

I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).

Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.

View 5 Replies View Related

Does RDA Create Extra Fields?

Nov 20, 2007

Hi!
I need to sync an application on windows mobile 5 that uses sql server mobile with sql server 2005. I've tried Merge and it populated the tables on the server with new fields and triggers. Does RDA do the same thing?

Jesus saves. But Gretzky slaps in the rebound.

View 4 Replies View Related

Can't Create A Foreign Key Using Non_primary Key Fields

Aug 9, 2004

Howdy all !
I'm just a bit on the frustrated side cause I want to create a foreign key but SQL Server won't let me. :(

I have table A with a primary key of main_id
I have table B with a primary key of another_id

Table A has a corresponding field called another_id.

I'm trying to create a foreign key between Table A & Table B on another_id but since it's not the Primary Key in Table A I get the following error:

There are no primary or candidate keys in the referenced table 'A' that match the referencing column list in the foreign key 'fk_classB_classA'.

Am I missing something totally obvious here? Why should I have to create a foreign key on a primary key?

What I find interesting is that I can create the relationship from enterprise manager but when I script it out is when I run into problems.

Here's the script I'm using:

Begin Code

alter table B add
constraint fk_classB_classA foreign key
(classB) references A (classB)
on delete no action
on update no action

End Code

Any help is greatly appreciated.

tam

View 6 Replies View Related

Create A Link Between Fields Of Two Tables

Jan 24, 2007

Amir writes "Dear Team Members;

Im writing a program in ASP language which it is using SQL Server for database.

I want to know how to create a link between fields of two tables. For example:
I have a table named: Projects containing:
p_name
status
date
member1
member2

and another table named results containing:
p_name
s_status
status1
status2

I want to create a link between field s_status of STATUS table and field status of table Projects

Users are sending data to table projects, when a user send some data to status of projects, I want this link to send data from status in project to s_status of results.

Could you please help me how to do it?

Regards.
Amir Zandi"

View 1 Replies View Related

How To Create Two Separate Fields From The Data?

Aug 2, 2007

Can anyone please help me with the following:


I have a table which has fields called defid, datavalue, pid etc.
I have to create a new table which should have fields:
CustID, datavalue[SocSec]{note:this is the value in d.datavalue when defid is 1004), datavalue[FirstName]{note: this is the value in d.datavalue when defid is 1006}, datavalue[LastName]{note: this is the value in d.datavalue when defid is 1007}, [FullName] {note: this is the value I should get by joining the datavalues (lastname and firstname) when defifis 1006 and 1007) ie. ex: FirstNameLastName.

COULD ANYONE PLEASE HELP ME ON THIS. DATA QUERY is as follows:
Thank you in advance.

SELECT DISTINCT c.CustID, d.datavalue, f.name
FROM dbo.Customers c
INNER JOIN dbo.Date7 d ON d.pid = c.root
INNER JOIN dbo.fielddisc f ON f.defid = d.defid
WHERE c.separatedate BETWEEN @StartDate AND @EndDate
AND c.DateTab = 7 AND d.defid IN (1004, 1006, 1007, 1009) AND c.CustID = 10 AND c.root = 8472
ORDER BY c.root

View 3 Replies View Related

Join Several Fields To Create New Field

Oct 4, 2006

What would be the recommendation/approach in creating a seperate field in which joins several differate fields together.



I have a table with field name a, b, and c. I want the information in those fields to be populated in a seperate field, d.



So instead of:



a

122

b

joe

c

st



I would have:

d

122 joe st



Thanks!

View 12 Replies View Related

Create Fields In Tables If They Do Not Exist In Database

Mar 31, 2008

Hello.
I need some help constructing a query i need to run on my database. I need to add 2 fields to every table in my databse. However, some of the tables already have1 or both these fields so i need to somehow do a check if the dield already exists. If it does not create the fields. Im using a MS SQL express 2005 server.
Could anyone help me construct this. Im pretty novice at SQL.
Thanks.

View 7 Replies View Related

How To Create DB For Single Attribute Having More Than 2 Text Fields

Dec 14, 2014

How can we create a DB for a single attribute such as ORDER DETAILS, CASH RECEIPT, TAX INVOICE having more than 2 text fields.

Also, in every form attribute such as order id is not present - in order identify the same as a primay key. So, which other attributes or fields can be considered as a primary key.

View 5 Replies View Related

Large Databases - Create Indexes For Fields That Are Used In Where Statements?

Aug 29, 2013

For large databases is it a good idea to create indexes for fields that are used in Where statements? Does that improve performance and reduce overhead?

View 4 Replies View Related

Combine And Convert Int Fields To Create One Field, Should Be Simple

Oct 19, 2007

I want to combine two of my fields somewhat like when you combine to char fields and concatenate them. But these two fields are int. How do i do that? Here's my query right now:




Code Block
SELECT AutoID,
CONVERT(Varchar(Mars_Calender_Year )+ CONVERT(Varchar(Mars_Calender_Period_Code) as MarsId
FROM NavisionReplication.dbo.Tbl_Mars_Calender
ORDER BY Mars_Calender_Year DESC








what am i doing wrong?

View 5 Replies View Related

Transact SQL :: Create Unique ID Based On Existing Fields

Sep 1, 2015

I have the following table (Table does not have unique key id )

Last Name     First Name         DATE        Total-Chrg

Jaime               KRiSH           5/1/2015         -4150.66
Jaime               KRiSH           5/1/2015          1043.66
Jaime               KRiSH           5/1/2015          1043.66
Jaime               KRiSH           5/1/2015           4150.66
Jaime               KRiSH           5/3/2015           4150.66
Peter                Jason            5/1/2015           321.02
Peter                Jason            5/1/2015           321.02
Peter                Jason            5/23/2015         123.02

I want the results to be in following way

Uniq ID    Last Name    First Name          DATE         Total-Chrg

1                Jaime            KRiSH                 5/1/2015     -4150.66
2                Jaime            KRiSH                 5/1/2015     1043.66
2                Jaime            KRiSH                 5/1/2015     1043.66
3                Jaime            KRiSH                 5/1/2015      4150.66
4               Jaime             KRiSH                 5/3/2015      4150.66
5               Peter              Jason                5/1/2015       321.02
6               Peter              Jason                5/1/2015       321.02
7               Peter              Jason               5/23/2015     123.02

May be we may do by dense_rank or Row_Number, but I couldn't get the exact query to produce based on the above table values.  There are some duplicates in the table(which are not duplicates as per the Business). For those duplicated Unique ID should be same(Marked in Orange Color which are duplicates). 

View 4 Replies View Related

How Do I Create A Composite Primary Key, Using Fields From Multiple Tables?

May 22, 2007

Hi All



I hope someone smart can help me, it would be highly appriciated.



I am developing an SQL Serverdatabase and in on of the tables I need the primary key to consist of two pieces of data.



TblOrders: OrderNum, Orderdate, ....

TblDispatchers: DispatcerID, Dispatcher, DispatcherAddress



The OrderNum field in TblOrders need to be a composite of an AutoNum-field (incrementet by 1) and the DispatcerID from the tblDispatchers.



Can this be done, and how.



Many thanks



Kind regards

Tina

View 3 Replies View Related

Create Output File In A Specific Layout - Extra Spaces Between Fields

Mar 17, 2014

I'm trying to create an output file in a specific layout. For some reason my output file is adding an extra 10 spaces between the Account Number and the Check Number in the statement below. The rest of the output file looks fine. Where the extra 10 spaces are coming from? I need 1 Filler Space between these fields.

SELECT DISTINCT
CASE p.PaymentMethodID WHEN 10 THEN 'I' WHEN 60 THEN 'V' WHEN 50 THEN 'S' ELSE 'I' END
+ CONVERT(CHAR(1), '')
+ (REPLICATE('0', 20 - LEN(ba.AccountNumber))+ CONVERT(CHAR(20), ba.AccountNumber))
+ CONVERT(CHAR(1), '')
+ (REPLICATE('0', 18 - LEN(p.CheckNumber)) + CONVERT(VARCHAR(18), p.CheckNumber))

[Code] .....

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

Problems On Create Proc Includes Granting Create Table Or View Perissinin SP

Aug 4, 2004

Hi All,

I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:

Create PROC dbo.GrantPermission
@user1 varchar(50)

as

Grant create table to @user1
go

Grant create view to @user1
go

Grant create Procedure to @user1
Go



Thanks Guys.

View 14 Replies View Related

Boolean: {[If [table With This Name] Already Exists In [this Sql Database] Then [ Don't Create Another One] Else [create It And Populate It With These Values]}

May 20, 2008

the subject pretty much says it all, I want to be able to do the following in in VB.net code):
 
{[If [table with this name] already exists [in this sql database] then [ don't create another one] else [create it and populate it with these values]}
 
How would I do this?

View 3 Replies View Related

Dynamic Create Table, Create Index Based Upon A Given Database

Jul 20, 2005

Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.

View 1 Replies View Related

Finding Rows In One Table But Not In Another Table Based On 2 Fields

Apr 23, 2008



I have 2 tables with 2 fields in common between them. How do I get a count of rows in table 1 but not in table 2 matching those 2 fields? Thanks.

View 7 Replies View Related

Can CREATE DATABASE Or CREATE TABLE Be Wrapped In Transactions?

Jul 20, 2005

I have some code that dynamically creates a database (name is @FullName) andthen creates a table within that database. Is it possible to wrap thesethings into a transaction such that if any one of the following fails, thedatabase "creation" is rolledback. Otherwise, I would try deleting on errordetection, but it could get messy.IF @Error = 0BEGINSET @ExecString = 'CREATE DATABASE ' + @FullNameEXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'CREATE TABLE ' + @FullName + '.[dbo].[Image] ( [ID][int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'ALTER TABLE ' + @FullName + '.[dbo].[Image] WITHNOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON[PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorEND

View 2 Replies View Related

Creating Table Fields Dynamically From Another Table Row

Aug 31, 2006

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

View 1 Replies View Related

I Want To Update My Table But It Has 30 Fields So How Can I Modify My Table I Have To

May 29, 2008

I want to update my Table but it has 30 fields so how can i modify my table i have to add some fields in it


I need that query for alter table

in it


reply me soon

View 14 Replies View Related

HT Insert Table A Record Fields Into Table B

Jun 13, 2008

All- Supposing I have table_a and table_b. Table_a has fields FIRST_A and LAST_A for people's first and last names. Similarly, table_b has FIRST_B and LAST_B for people's first and last names.

How would I create a record in table_b for each record in table_a, such that for each added record, the values of FIRST_A is copied to FIRST_B and LAST_A is copied to LAST_B. (Table_b will have other fields that are left unfilled.)

Thanks!

-Kurt

View 1 Replies View Related







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