Generating Create Statement

Jun 10, 2007

Hi,
I am new to SQL Server and need your help.
I have a table (old_table) with about 500 columns and each column of type varchar. I have to generate a table (new_table) which has all the columns of the old table but with different data types.

Is there a way I can use the syscolumns to generate a “create table� statement?

Thanks.

View 4 Replies


ADVERTISEMENT

Generating CREATE Scripts

May 20, 2008

Hello all,
I have a few questions relating to scripts and I'm looking for some help, or at least a point in the right direction.
1) I'm generating CREATE scripts to move a number of tables/procedures/functions from my testing database to my production database.  The issue I have is the script doesn't seem to be generated in a particular order, so I'm getting errors when a table/procedure/function is created that references something that hasn't been created yet.  Is there a setting available to create the script in a specific order to prevent these errors?
2) This is somewhat related to the first question.  I need to drop all the tables/procedures/functions before I re-create them.  Can I set all the DROPs at the beginning of the script, and then run all the CREATEs?
3) Eventually the database will be ready for production and in use.  And eventually I'll be asked to make changes, without erasing existing data.  Can I accomplish this using the generate scripts feature?  What topics should I be investigating when looking into conducting these types of updates?  "Updating database" is too general and I'm not sure the technical term.  I've done very simple DB-related tasks so I'm trying to figure out what I'm looking for so I can educate myself.  Basically, I don't know what I don't know.
Any help is appreciated.  Thanks.

View 1 Replies View Related

SQL Statement For Generating Expired Customer

Jul 20, 2005

Hi all,I need sone help on generating a SQL statementi had 3 tables nowCustomer[color=blue][color=green]>> Name>> Acct No>> Address>> Phone[/color][/color]Invoice[color=blue][color=green]>> Invoice no>> Customer no[/color][/color]Invoice Details[color=blue][color=green]>> Invoice No>> Commence Date>> Expiry Date>> Amount[/color][/color]Every month, the customer will come in to pay for the bills. 1seperate row is generated for each invoice.I need to generate a report stating customer who had came in to payfor the month of march (expiry date=31/3/2004) but still have not makepayment for the month of April (date commence >1/4/2004).Can anyone help?

View 4 Replies View Related

Dynamically Generating CREATE INDEX Statements

Feb 15, 2001

Does anyone have any SQL that will look at a DB and dynamically generate CREATE INDEX statements? I
know I can use EM but I want to make this a scripted process and you can only generate CREATE INDEX statement if
you script out the tables too.

Any Ideas??

Thanks

View 2 Replies View Related

SQL Server 2005 Express, Generating Create Scripts

Nov 7, 2005

Anyone know an easy way to generate the SQL Scripts to gererate the
tables/sp's/triggers/indexes/fk's, ect? I know Sql Server 2000 lets you
generate the db script, but I don't know how to do this with 2005

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

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".

Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.

View 4 Replies View Related

Help Me Create A SQL Statement

Oct 27, 2005

I have a database that is far from being rational! I need to query the tables to get a hierarchy resultset. Here is the structure:levelOne   levelOne_A levelTwo  levelThree   levelFourmy problem is that table levelOne_A and table levelTwo have data that are considered to fall underneath levelOne. However the data is far different from each other so I can not able merge both into one.so the it should look like this:levelOne levelOne_A AND levelTwo  levelThree   levelFourAny ideas how I can get my hierarchy structure?Thanks for your help!

View 1 Replies View Related

Create A Uniqueidentifier In SQL Statement

Jan 20, 2008

Hello, I am trying to autogenerate a new guid in my sql statement. Here is a sample of my code:string comments = "CREATE TABLE comments (domaininfoid UNIQUEIDENTIFIER PRIMARY KEY NOT NULL," +
"comment TEXT NOT NULL)";
 
How can I make sql auto generate a guid?

View 2 Replies View Related

How To Create A Custom SQL Statement.

Mar 25, 2008

im creating a custom sql statement where my code starts like tt.. its a double query and how do i link the 2nd part to the first part (select * from PO where 1=1)?<script runat="server">                protected void CheckBox1_CheckedChanged(object sender, EventArgs e)    {        strquery += " and PO between " + textbox1.text + " and " + textbox2.text;    }    protected void CheckBox2_CheckedChanged(object sender, EventArgs e)    {        strquery += " and Dlvdate between " + textbox3.text + " and " + textbox4.text;    }</script>  im a serious newbie with C#

View 3 Replies View Related

Create A Path In Statement?

Oct 17, 2013

I'm using SQL Server 2012 for my databases.

Now I'm trying to write a search module. Therefore I have to create a view, with all datas to make it easy for searching. Unfortunately I have a table, which creates a tree (let's say, it's a navigation tree). For the view, I like to have the navigation as a path:

/Main/FirstSub/SecondSub

My navigation tbl looks like this:

Code:
IDParentIDText
10Main
20otherMain

[Code]....

View 4 Replies View Related

Name In CREATE USER Statement?

Mar 27, 2008



Hi.

I was wondering if I could have a query like this:
CREATE USER 'Firstname Lastname' FOR LOGIN 'DOMAINuser' WITH DEFAULT_SCHEMA=[dbo]
The combination of firstname and lastname does not seem to work.

I have been using:
sp_grantdbaccess 'DOMAINuser', 'Firstname Lastname'
but i cannot specify DEFAULT_SCHEMA with that sp.

Any suggestions?



View 1 Replies View Related

Create New Column In Select Statement

May 28, 2014

I am trying to create a new column 'COL_4' and, in the same step, create a case statement off of the new col. I know that the code below will not execute. I realize that I could get ride of COL_4 in my code below and concatonate but I need to keep Col_4 in the output.

SELECT
COL_1
,COL_2
,COL_3
,COL_4 = COL_1 + COL_2
,COL_5 = CASE
WHEN COL_1
THEN 'SOMETHING'
END
FROM TABLE_1
;

View 1 Replies View Related

Help SQL Statement Create Calculate Percent ?

Apr 13, 2006

I have a following table :
ID ------- Answear ------ Vote
1 ------- 1|2|3|4 ------- 3|5|3|2
2 ------- 1|2|3|4 ------- 2|5|3|1
3 ------- 1|2|3|4 ------- 2|5|7|2

So, I need to create : ,

ID ----- Answear ------ Vote ----- Total ----- Percent
1 ----- 1|2|3|4 ------ 3|5|3|2------ 13-----23.08|38.46|23.08|15.38
2 ----- 1|2|3|4 ------ 2|7|8|1------ 18-----.....
3 ----- 1|2|3|4 ------ 2|5|7|2------ 16-----.....

Any one help me ?
Thanh you very much.
PS :(3/13)*100|(5/13)*100|(3/13)*100|(2/13)*100 after calculate : 23.08 % |38.46 %|23.08 %|15.38 %

View 6 Replies View Related

Best Way To Create Dynamic Update Statement

Jul 23, 2005

In general, What is the best approach in creating a dynamic updatestored procedure, that can handle recieving varying input paramters andupdate the approporiate columns.

View 6 Replies View Related

Table Name In Variable - Create Statement

Jul 20, 2005

Is it possible to have part of a table name used in a CREATE statementcontained in a variable? Here's what I'd like to do, althoughobviously the syntax of this isn't quite right or I wouldn't be hereasking:DECLARE @TblPrefix char (3)SET @TblPrefix = 'tst'CREATE TABLE @TblPrefix + TestTable (col1 int)The point there is to have a table named tstTestTableThe reason I need to do this is that my ISP will only give me onedatabase to work with and I'd like to have two copies of theapplication I'm developing running at the same time. So I'd like torun the sql script that creates the tables with TblPrefix set to "dev"and then run it again with TblPrefix set to "liv"thankseric

View 3 Replies View Related

Is There A Way To Create Columns From A Row Value In A Select Statement?

Jan 30, 2008



Hi!

I have a select question that look like this:


DECLARE @ID uniqueidentifier;

SET @ID = '40bd3052-60f4-414a-99df-ca882c128493';


SELECT

rp.ID AS ReportPackId

,rp.SupplierPartyIdentifier AS SupplierPartyIdentifier

,rpap.AdditionalPartyIdentifier AS AdditionalPartyIdentifier

FROM

ReportPack rp

INNER JOIN ReportPackAdditionalParty rpap

ON rpap.fk_ReportPack = rp.ID

WHERE rp.ID = @ID

The result a get when running the select question is:








ReportPackId
SupplierPartyIdentifier
AdditionalPartyIdentifier

40BD3052-60F4-414A-99DF-CA882C128493
String
addPartyId1

40BD3052-60F4-414A-99DF-CA882C128493
String
addPartyId2

My problem is that the result I want is the following:









ReportPackId
SupplierPartyIdentifier
AdditionalPartyIdentifier
AdditionalPartyIdentifier

40BD3052-60F4-414A-99DF-CA882C128493
String
addPartyId1
addPartyId2

I always know that the ReportPackId and SupplierPartyIdentifier will be identical for all rows because of the Where condition, and therefore I want all AdditionalPartyIdentifiers to be in columns instead of a new row. Is this possible?

View 5 Replies View Related

Need To Create &&<Query&&> Statement Programmatically

Jun 2, 2006

I am creating a web application that uses a using a web service to get data for my reports. Since the webservice only accepts 1 parameter called "sql" (the sql select statement), I am using the report's query string to get the data.

Here is the data source and dataset info I am using:

DataSource
Name: WebService
Type: XML
Connection string: http://localhost/myWeb/myWebService.asmx
Credentials: No credentials

DataSet
Query tab:
Name: WebService
Data source: WebService
Command type: Text
Query String: <Query><SoapAction>......</SoapAction></Query>

Here is a sample of the <Query> string that I use when I first build the report:

------------------------------------------------------------------------------

<Query>
<SoapAction>http://tempuri.org/GetDataset</SoapAction>
<Method Namespace="http://tempuri.org/" Name="GetDataset">
<Parameters>
<Parameter Name="sql" Type="String">
<DefaultValue>Select * From Customers</DefaultValue>
</Parameter>

</Parameters>
</Method>

<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath>
</Query>
------------------------------------------------------------------------------
When the user selects a report in the web application, they are prompted for information about the sql statement, and then I can rebuild the <Query> xml fragment, substituting the new sql statemet for the default one. for example, the statement "Select * From Reports" would be replaced with "Select * From Customers where LN = 'Smith'".

Then I want to attach that new <Query> statement to the report and run it. How can I set this information in the report? I can't find anything that talks about it, but there must be some way!

Thanks in advance for your help!

View 13 Replies View Related

Create An Insert Statement For A Row Containing The Word With.

Nov 13, 2007

I know the word 'With' is a SQL Reserved; I created a SQL Script Generator that creates insert statements to copy data from one SQL Server database to another. If a row contains the word 'with' or 'With' the insert statement fails. Is there a way around this? The script is executed in the Query Analyzer window of SQL Server.

The record below is an example of a row that fails.


Insert Into [Contact] ([Active],[AddressID],[Birthday],[Birthmonth],[ContactType],[CreatedBy],[CreatedDate],[Description],[Employed],[FirstName],[HasResume],[LastName],[MiddleName],[ModifiedBy],[ModifiedDate],[Personality],[RowVersion])

Values ('False', 0, 0, 0, 1, 0, '', 'Wants to get back with Van Halen', 'False', 'David', 'False', 'Roth', 'Lee', '', '', '', '')


The error message shown is:



Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Thank you,

Corby Nichols
Flying Elephant Software

View 7 Replies View Related

How To Create A Select Statement With An Increasement Variable?

Dec 19, 2003

Example:

Select icount + 1 as icount from table

or

Select counter() as icount from table

The above is wrong ...just a sample to show what I am trying to accomplish.

Is there a function in SQL statement? Thanks.

View 2 Replies View Related

FILENAME Option In CREATE DATABASE Statement

Nov 9, 2004

Hi,

I have a problem with CREATE DATABASE statement, since it needs to specify the absolut path for a file in the FILENAME= option. I would need instead a relative path, i.e. only the name of the file because the script that launch this SQL command is dependant on the installation path decided by the user, during the installation.
Is there a way to pass to the FILENAME only the name of the file?
Hope to have been clear :-)

Thanks in advance

View 4 Replies View Related

Dynamic CREATE TABLE Or SELECT INTO Statement

Jul 27, 2004

In SQL Server you can do a SELECT INTO to create a new table, much like CREAT TABLE AS in Oracle. I'm putting together a dynamic script that will create a table with the number of columns being the dynamic part of my script. Got any suggestions that come to mind?

Example:

I need to count the number of weeks between two dates, my columns in the table need to be at least one for every week returned in my query.

I'm thinking of getting a count of the number of weeks then building my column string comma separated then do my CREATE TABLE statement rather then the SELECT INTO... But I'm not sure I'll be able to do that using a variable that holds the string of column names. I'm guess the only way I can do this is via either VBScript or VB rather then from within the database.

BTW - this would be a stored procedure...

Any suggestions would be greatly appreciated.

View 1 Replies View Related

[SQL Server 2000] How Can I Create Cursor For A SQL Statement?

Jul 26, 2006

I have a SQL statement stored in a SQL varriable (after a lot of conditions)


Code:

declare @sql char(100)
set @sql = 'select ma_kh, ten from _khang'



Now, I want to create a cursor to recalculate some values
I've tried:



Code:

declare cur_T cursor for exec(@sql) open cur_T




but it doesn't work.
Can I have another way to do that???

View 2 Replies View Related

Help With Create View Statement And Eorror Message

Feb 25, 2005

Hi all,
I am trying to create a view with approx. 3000 columns... and got the following error message:

"CREATE VIEW failed because column 'HSEPRIN' in view 'MyTestView' exceeds the maximum of 1024 columns.

Is it mean the max number of columns for each table is 1024? I thought in SQL server the table can contain as much information as possible.
Anyone can help to answer my question?

Thank you in advance.

View 14 Replies View Related

Create Temporary Table Through Select Statement

Jul 20, 2005

Hi,I want to create a temporary table and store the logdetails froma.logdetail column.select a.logdetail , b.shmacnocase when b.shmacno is null thenselectcast(substring(a.logdetail,1,charindex('·',a.logde tail)-1) aschar(2)) as ShmCoy,cast(substring(a.logdetail,charindex('·',a.logdeta il)+1,charindex('·',a.logdetail,charindex('·',a.lo gdetail)+1)-(charindex('·',a.logdetail)+1))as char(10)) as ShmAcnointo ##tblabcendfrom shractivitylog aleft outer joinshrsharemaster bon a.logkey = b.shmrecidThis statement giving me syntax error. Please help me..Server: Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'case'.Server: Msg 156, Level 15, State 1, Line 7Incorrect syntax near the keyword 'end'.sample data in a.logdetailBR··Light Blue Duck··Toon Town Central·Silly Street···02 Sep2003·1·SGL·SGL··01 Jan 1900·0·0·0·0·0.00······0234578······· ····· ··········UB··Aqua Duck··Toon Town Central·Punchline Place···02 Sep2003·1·SGL·SGL··01 Jan 1900·0·0·0·0·0.00·····Regards.

View 2 Replies View Related

Can I Create A 'Top N' Statement Within A Stored Procedure Using A Parameter?

Jul 20, 2005

In a 'Top n' type statement I wish to be able to insert the n valuefrom a parameter, within a stored precedure egHaving declared @pageSize as a parameter I want to run the followingtype of query :SELECT DISTINCT TOP @pageSize routeID, routeName FROMtblRoute_HeaderWhen I attempt to do so I get an error mesage indicating incorrectsyntax. I do not get an error message if I specify 'n' directly as inTOP 10Am I missing something or is this not possible within a storedprocedure?Best wishes, John Morgan

View 5 Replies View Related

Create Multiple Store Procedures In 1 SQL Statement

Nov 17, 2006

Hi guys , may I know is that possible to create multiple store procedures in 1 time using 1 SQL statement? Thx for the assistance.

Best Regards,

Hans

View 5 Replies View Related

Can We Specify Datarow Locking In Create Table Statement

Sep 24, 2007

Hi guys,

I have a question regarding a locking scheme in MSSQL I hope you guys can help. In Sybase, I am able to specify datarow locking in DDL (ex. create table, alter table). Can I do the same in MSSQL or is there an equivalent option in CREATE TABLE statement in MSSQL? I came across a few articles in MSDN about datarow locking and it seems to me that MSSQL only allows locking through DML... Is that true? Thanks.

View 2 Replies View Related

CREATE INDEX Statement For SQL Mobile Not Documented?

Nov 16, 2005

Hello,

View 5 Replies View Related

Alter Statement To Create Foreign Key Relationships

Jul 18, 2006

Here is the alter statement that I am trying to use to create a relationship between 2 tables. This does not seem to work on mobile. What am I doing wrong?


ALTER TABLE [SubCategory] CONSTRAINT [FK_SubCategory_Category] FOREIGN KEY([CategoryID])
REFERENCES [Category] ([CategoryID])
ON UPDATE CASCADE
ON DELETE CASCADE

View 3 Replies View Related

Create Batch File That Will Run Update Statement And Schedule It To Run?

Oct 7, 2013

Is there a way to create a Batch file that will run an Update Statement and schedule it to run?I've used bcp to extract data to a txt file before, but i'm not sure if an Update can be performed.I'm using SQL Server 2008 R2 Express Edition so i don't have Server Agent available.

View 7 Replies View Related

SQL Server 2012 :: Using Function In Create Index Statement

Jun 10, 2015

Can we use a sql function() in create index as below is giving error , what would be work around if cannt use the function in below scenario

CREATE NONCLUSTERED INDEX [X_ADDRESS_ADDR1_UPPER] ON [dbo].[ADDRESS]
(
UPPER([ADDR_LINE_1]) ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO

View 3 Replies View Related

Unable To Create Variable Select Statement In For Each Loop

Apr 24, 2007

What I'm trying to do is this;

I have a table with Year , Account and Amount as fields. I want to



SELECT Year, Account, sum(Amount) AS Amt

FROM GLTable

WHERE Year <= varYear



varYear being a variable which is each year from a query



SELECT Distinct Year FROM GLTable



My thought was that I would need to pass a variable into a select statement which then would be used as the source in my Data Flow Task.



What I have done is to defined two variables as follows

Name: varYear (this will hold the year)

Scope: Package

Data type: String



Name:vSQL (This will hold a SQL statement using the varYear)

Scope: Package

Data type: String

Value: "SELECT Year, Account, sum(Amount) AS Amount FROM GLTable WHERE Year <=" + @[User::varYear]



I've created a SQL Task as follows

Result set: Full Result Set

Connection Type: OLE DB

SQL Statement: SELECT DISTINCT Year FROM GLTable

Result Name: 0

Variable Name: User::varYear



Next I created a For Each Loop container with the following parameters

Enumerator: Foreach ADO Enumerator

ADO Object source Variable: User::varYear

Enumeration Mode: Rows in First Table



I then created a Data Flow Task in the Foreach Loop Container and as the source used OLE DB Source as follows

Data Access Mode: SQL Command from Variable

Variable Name: User::varYear



However this returns a couple of errors "Statement(s) could not be prepared."

and "Incorrect syntax near '='.".



I'm not sure what is wrong or if this is the right way to accomplish what I am trying to do. I got this from another thread "Passing Variables" started 15 Nov 2005.



Any help would be most appreciated.

Regards,

Bill

View 5 Replies View Related







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