Transact SQL :: Create Table Script
Jul 21, 2015
When I right click on a table and click Script Table as --> Create to --> it scripts out:
CREATE TABLE [dbo].[Test](
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Address] [varchar](50() NULL
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[Test] ADD [City] [varchar](50) NULL
Why is there an Alter table statement? I’m assuming this table was altered at some point but why is SQL not just doing a create table and how is it keeping track of these changes?
View 4 Replies
ADVERTISEMENT
May 21, 2015
convert my table(like picture) to hierarchical structure in SQL. actually i want to make a table from my data in SQL for a TreeList control datasource in VB.net application directly.
ProjectID is 1st Parent
Type_1 is 2nd Parent
Type_2 is 3rd Parent
Type_3 is 4ed Parent
View 13 Replies
View Related
Jul 21, 2015
I create a script like below:
GO
SET ANSI_PADDING ON
GO
DECLARE
@ProjectID AS NVARCHAR(128),
@TableName AS NVARCHAR(128);
SET @ProjectID = N'EPA';
[code]....
The table created, but T-SQL created a table called @TableName.not like "EPA_SweetChargeCodeAssignees'
how to debug?
View 2 Replies
View Related
Jul 2, 2015
Im trying to insert the values from this query into a table, so I can later check the history of memory usage:
SELECT
[total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB]
,[total_page_file_kb] / 1024 AS [Total_Page_File_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Page_File_MB]
,[kernel_paged_pool_kb] / 1024 AS [Kernel_Paged_Pool_MB]
,[kernel_nonpaged_pool_kb] / 1024 AS [Kernel_Nonpaged_Pool_MB]
,[system_memory_state_desc] AS [System_Memory_State_Desc]
FROM [master].[sys].[dm_os_sys_memory]
What I'm missing is a way to insert the current timestamp every time I insert to the table.My plan is to use the insert into command.
View 3 Replies
View Related
May 26, 2015
I use Indexes Fundamentals of Microsoft SQL Server - Lesson 30: Indexes in the website of URL... to learn the basic things of Indexes. In my SQL Server 2012 Management Studio (SSMS2012), I executed the following code..
-- scFTX_CreateTableEmployees.sql
-- saved in C:/Documents/SQLServerIndexes_downloadCode
-- 26 May 2015 10:52 AM
USE ScottChangDB;
GO
CREATE TABLE Employees
[code]....
Where the SCHEMA and the index 'IX_Employees are located in the Object Explorer of the database "ScottChangDB" of my SSMS2012.
View 3 Replies
View Related
Sep 1, 2015
Need creating a stored procedure which creates a table.
View 3 Replies
View Related
Jun 17, 2015
I pulled some examples of using a subquery pivot to build a temp table, but cannot get it to work.
IF OBJECT_ID('tempdb..#Pyr') IS NOT NULL
DROP TABLE #Pyr
GO
SELECT
vst_int_id,
[4981] AS Primary_Ins,
[4978] AS Secondary_Ins,
[code]....
The problems I am having are with the integer data being used to create temp table fields. The bracketed numbers on line 7-10 give me an invalid column name error each. In the 'FOR', I get another error "Incorrect syntax near 'FOR'. Expecting '(', or '.'.". The first integer in the "IN" gives me an "Incorrect syntax near '[4981]'. Expecting '(' or SELECT". I will post the definitions from another effort below.
CREATE TABLE #Pyr
(
vst_int_idINTEGERNOT NULL,
--ivo_int_idINTEGERNOT NULL,
--cur_pln_int_idINTEGERNULL,
--pyr_seq_noINTEGERNULL,
[code]....
SQL Server 2008 R2.
View 3 Replies
View Related
Sep 30, 2015
I have an Access app. that I am migrating the DB portion (queries, tables) to SQL server. I need to create a temp table that lasts as long as the user has the Access FE app. open. Idea is that the temp table stores the user's parameters (used for filtering data entry forms and report). The parameters allow the app. to only show the user his data (cannot view other users data). The SP shown below works OK, it creates a ##Temp table and updates it with the parameters sent by Access FE app. The issue I am having is that as soon as the SP finishes the ##Temp table is removed. I thought of using a regular table, but, I am currently testing this migration in my local SQL server instance, as soon as I move the database to production environment, then users will not be able to create tables as permissions are only read/write.
USE [Work_Allocation]
GO
/****** Object: StoredProcedure [dbo].[spUser_Parameters_update] Script Date: 9/30/2015 12:27:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spUser_Parameters_update]
[code]...
View 10 Replies
View Related
Jun 25, 2015
what would be the TSQL in trying to create a new table with date-time format ending via a select into like:
select
*
into tblResults_
+
SELECT
CONVERT(VARCHAR(10),GETDATE(),112)
+
'_'
+
REPLACE(CONVERT(VARCHAR(10),GETDATE(),108),':','_')
from qryResult
View 3 Replies
View Related
Apr 29, 2015
I want to create index for hash table (#TEMPJOIN2) to reduce the update query run time. But I am getting "Warning!
The maximum key length is 900 bytes. The index 'R5IDX_TMP' has maximum length of 1013 bytes. For some combination of large values, the insert/update operation will fail". What is the right way to create index on temporary table.
Update query is running(without index) for 6 hours 30 minutes. My aim to reduce the run time by creating index.
And also I am not sure, whether creating index in more columns will create issue or not.
Attached the update query and index query.
CREATE NONCLUSTERED INDEX [R5IDX_TMP] ON #TEMPJOIN2
(
[PART] ASC,
[ORG] ASC,
[SPLRNAME] ASC,
[REPITEM] ASC,
[RFQ] ASC,
[Code] ....
View 7 Replies
View Related
Jul 24, 2015
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary',
@profile_name =
'SQL SMTP',
[code]....
View 3 Replies
View Related
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
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
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
Jun 3, 2007
hello all i am new here,
my question is how i can activate http in sql server 2005
i want to run a helpdesk application but i cant make a new database.
it has something to do with create endpoint, i have no knowlege off sql server 2005 its not my field
can someone help me please?
View 3 Replies
View Related
Jul 29, 2015
I have got a table as follow:
CREATE TABLE [dbo].[NozzleAllTbl] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[nBDId] INT NULL,
[nShellId] INT NULL,
[nTDId] INT NULL,
CONSTRAINT [PK_dbo.NozzleAllTbl] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_dbo.NozzleAllTbl_dbo.NozzleTbl_nBDId] FOREIGN KEY ([nBDId]) REFERENCES [dbo].[NozzleTbl] ([Id]),
CONSTRAINT [FK_dbo.NozzleAllTbl_dbo.NozzleTbl_nShellId] FOREIGN KEY ([nShellId]) REFERENCES [dbo].[NozzleTbl] ([Id]),
CONSTRAINT [FK_dbo.NozzleAllTbl_dbo.NozzleTbl_nTDId] FOREIGN KEY ([nTDId]) REFERENCES [dbo].[NozzleTbl] ([Id])
);
NozzleTbl is another Table.At the moment I am referencing only one NozzleTbl thrice. But how can I reference a collection of NozzleTbl thrice?So that in the c# code I could access the collections as:
NozzleTbl_1 = NozzleAllTbl.NozzleTbls[0]
NozzleTbl_2 = NozzleAllTbl.NozzleTbls[1]
...
NozzleTbl1_1 = NozzleAllTbl.NozzleTbls1[0]
NozzleTbl2_1 = NozzleAllTbl.NozzleTbls2[0]
....
View 2 Replies
View Related
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
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
Aug 22, 2007
Can someone point me to any whitepaper on how to use a Transact SQL script to create an SSIS package. I will need to be able to run the script at various customer sites.
View 11 Replies
View Related
Oct 19, 2015
create the column "Region" like this Picture?.
View 26 Replies
View Related
Jun 25, 2015
Log-in access web-page.When page is accessed a stored procedure is executed that will provide a list of all the RA(Problem -- when a new building is added, this stored procedure must be opened and syntax added)..Results are stored in a drop down list on the page.Create a table holds list of all buildings something similar to this
Create Table [dbo].[BuildingInformation] (
ID INT IDENTITY NOT NULL,
BuildingName varchar(50),
RAName varchar(50)
[code]....
Alter the stored procedure so that when it is run it will iterate the BuildingInformation table and compare the value in BuildingInformation to the values in the field BuildingName of the view [RA]. If all buildings contained in the table [BuildingInformation] exist in the view [RA] nothing will change. If their are values in the [BuildingInformation] table that do not exist in the view [RA], the view [RA] will be dropped and recreate with a 'Union ALL' for each building in the table
View 6 Replies
View Related
Sep 2, 2015
I have a data with mutliple esn but different auditdate and opid. I will pull this data filtering by date and opid. My requirements is not to include the opid = 51 but need to get the desired opdesc for this esn that contains opid=51.
See below sample ddl and desired result. I dont want do include the opid = 51 because it will create a duplicate in transaction instead retain the opid 5
example: esn T9000000000019829505 has multiple rows with different auditdate and opid. retain the records for opid is equal to 5 but get the opdesc for opid is equal 51.
reate table #test
(esn nvarchar(35), dateaudit datetime, opid int)
insert into #test(esn,dateaudit, opid)values('352452060499834','2015-05-12 20:32:39.490',5)
insert into #test(esn,dateaudit, opid)values('352452060499834','2015-07-06 17:35:14.210',5)
insert into #test(esn,dateaudit, opid)values('T9000000000019829505','2015-01-14 15:18:45.620',5)
[Code] ....
Desired Result:
esn-------------------dateaudit----------------opid--opdesc--rn
352452060499834------2015-05-12 20:32:39.490---5---Shipping--1
352452060499834------2015-07-06 17:35:14.210--5---Shipping--1
T9000000000019829505--2015-01-14 15:18:45.620--5---Scrap-----1
OR
esn-------------------dateaudit----------------opid--opdesc--rn--remarks
352452060499834------2015-05-12 20:32:39.490---5---Shipping--1---shipping
352452060499834------2015-07-06 17:35:14.210--5---Shipping--1---shipping
T9000000000019829505--2015-01-14 15:18:45.620--5---Shipping--1---Scrap
View 5 Replies
View Related
Jul 17, 2015
I have a Java application (APP) that use SQL Server 2008 - 20014. In the SQL server there can be more than my database. During setup of the database the first user (ex:ADM) is created using MS SQL Server Management Studio (SMS). ADM can then launch the APP with proper credentials and connect to the database.ADM can then, in the APP, create new users for the APP and database. After some testing for proper CREATE and GRANTS I use the following code to create a new Create, Read, Update, Delete (CRUD) user for the APP and database: CREATE LOGIN testuser WITH PASSWORD='password', DEFAULT_DATABASE = testdb
USE testdb
CREATE USER testuser FOR LOGIN testuser
GRANT AUTHENTICATE TO testuser WITH GRANT OPTION
GRANT CONNECT TO testuser WITH GRANT OPTION
GRANT ALTER ANY USER TO testuser WITH GRANT OPTION
GRANT CONNECT SQL TO testuser WITH GRANT OPTION
GRANT INSERT,SELECT,UPDATE, EXECUTE, DELETE TO testuser WITH GRANT OPTION
USE master
GRANT CONTROL SERVER TO testuser
USE testdb..When I the use the SMS and look at security the "testuser" is only added to testdb. To test I create a second database "testdb2" --> launch the APP to connect to testdb2 and can login with "testuser".I don't know if I give to much grants or missing one...proper TSQL so new users only have CRUD access to "testdb" and no other database on the SQL server.
View 5 Replies
View Related
Oct 14, 2015
How to create column "Region" like this Picture.
View 16 Replies
View Related
Jun 1, 2015
I did lot of google but did not get script for candidate and super key. I do not have syntax to create candidate and super key in sql.
View 4 Replies
View Related
May 25, 2015
I have two tables, Inspections and InspectionDetails. The InspectionDetails only contains information if an inspection found violations. Some inspections don't look for violations so inspectiondetails would have no entry.How can I create a 0-Many relationship between these two tables?Inspection Table has : InspectionID, InspectionDate, TypeofInspection, Inspector..InspectionDetail Table has : DetailID (an auto-incrementing identity column), InspectionID, SectionViolated, ViolationDescription, InspectorNotes.
View 6 Replies
View Related
Jun 21, 2015
How can I turn this query into an Email alert if one of the counts are GT 50. The Source are machines that I capture counts(Unprocessed_Cntr) from every 5 minutes and load to the Load_Time_Capture table. I also add a datetime to each capture(Unprocessed_Capture ).
If any individual source has a count > 50 or if the combined(ALL) GT 50 send an email to support person.
SELECT
CASE GROUPING([Source])
WHEN 1 THEN 'ALL'
ELSE [Source] END AS 'Input Source',
avg([Unprocessed_Cntr]) as 'Average Queue Past 15 Min'
FROM Load_Time_Capture
WHERE Unprocessed_Capture >= DateADD(mi, -15, Current_TimeStamp)
GROUP BY [source] WITH ROLLUP
View 4 Replies
View Related
Aug 17, 2015
I need to create a sequential aggregate of a column using sum in tsql.
I want a column that will sum up all hours work by transaction date.
for example
Monday my hours work will be 8 and therefore my total transaction hours will be 8.
Tuesday my hours work will be 8 but I want the total hours of my total transaction hours to 16.
Wed my total hours work will be 5 and I want the total hours of my total transaction hours to 21. etc.
Select myname, weekday, hours worked, Totalhours_ToDate =Sum(hours worked)from table
View 3 Replies
View Related
May 8, 2015
I have 3rd party application implementation I am doing, if I have stored procedure in one database, can it be copied to other database (newly created) on the same server? I will have the name of old and new database when this will be copied. I do need to automate this copy.
View 10 Replies
View Related
May 13, 2015
table1
id Rate start end
A01 2 01/04/2015 31/03/2016
A02 2 01/04/2015 31/03/2016
A03 2 01/04/2015 31/03/2016
A01 2.5 01/04/2016 31/03/2017
A02 2.5 01/04/2016 31/03/2017
A03 2.5 01/04/2016 31/03/2017
table2
id Received
A02 13/05/2015
A02 23/07/2016
View 3 Replies
View Related
Nov 17, 2015
I am wanting to create a custom workday calendar to show Monday - Friday as a workday, then go back and update a few holidays as non-workdays. This is the syntax I have to start with, but it is presenting with an error:
Msg 241, Level 16, State 1, Line 21 Conversion failed when converting date and/or time from character string.
What should I alter?
CREATE TABLE dbo.CreateCustomCalendar
(
wk_Date INT IDENTITY NOT NULL,
FullDate DATETIME NOT NULL,
WeekDayName VARCHAR(9) NOT NULL,
IsWorkday varchar(20) NOT NULL
)
[Code]....
View 7 Replies
View Related
Sep 29, 2015
I need to create a T-sql script to create logins in bulk so users can access views on the database. Usernames as used in the local domain are all stored in a table.
Users already exist in the domain (let’s say: MYDOMAIN)ADSI.dbo.Ad.username is the column wich contains the usernames as used in the Active Directory domain (+/- 350)Script needs to check if login already exists to make sure login is not added twice.If possible give all these users db_datareader role, in let’s say: MYDATABASE
View 7 Replies
View Related
Jun 20, 2015
This is my syntax, but it keeps presenting an error of 'Invalid Column name Last Resort
Select fb.foo, fb.man, cz.choo, 'Hi' As [Last Result]
From foobar As fb
Inner Join chezter As cz
On fb.username = cz.username
and [fb].[Last Result] = [cz].[Last Result]
View 3 Replies
View Related
