Creating A Table From Data In A SQL Server Db
Jun 29, 2005
I have a form with a drop down box so the user can select a quote.. When a quote is selected i need to populate a table of all the records associated with the quote id. I need the table to be created in such a way that the user can add new rows, delete rows and edit the data. Then all of the changes need to be written back to the database. Whats the most efficient/best way of doing this and if you have any ideas can you explain them as thoroughly as possible! I'm currently upgrading an access database to a sql server back end with an asp.net client and it's taking me a while to get to grips with all the changes!Thanks in advance,Chris
View 1 Replies
ADVERTISEMENT
Sep 20, 2007
I 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
View 6 Replies
View Related
May 20, 2006
I 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 Related
Aug 27, 2005
I use Visual Studio and VB.
If I want to create an SQL data table on the server, I go to Server
Explorer and make a small table with the corerct columns. Then I add
rows programatically as needed from the Application I'm working with.
It would suit me, for a current job, to be able to create the table itself programatically.
I don't know how to do this. Can it be done ? If so, could someone give
me a starter, please ? Four cols with a key in the first.
David Morley
View 1 Replies
View Related
Jan 25, 2007
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!
View 2 Replies
View Related
Aug 4, 2005
Hi, I'm having some difficulty creating the SQL Statement for getting some data from a table:
I have the following table of data
__User___Votes___Month
__A_______14______2__A_______12______3__A_______17______4__A_______11______5
__B_______19______2__B_______12______3__B_______15______4
I want to beable to pull out the total number of votes a user has had over a period of months.
eg Total up each users users votes for months 4 and 5
that would give:
__User____TotalVotes
___A________28___B________15
An added complecation is that user B does not have any data for month 5
Any help or pointers would be fanstatic
Many thanks
View 3 Replies
View Related
Jun 11, 2008
Hi All,
I am extracting source data which is in txt fille to OLE DB destination. But data of each day I want to save in different staging table. For Eg; tblProduct20081206, tblProduct20081207. How can it be done. I have seen lots of posting and script when destination is Txt. I want to use same table for staging but want to create different table for each day with adding date extension.
Please Help
View 2 Replies
View Related
Aug 13, 2001
Hi all,
I'm trying to create a temporary table with one of the columns with a datetime datatype. Sql server 7.0 is giving me an error:
Error 195 date is not a recognized function name.
Example of my create table looks something like this:
create table #temp_table(column_one int, column_two(15,2), Column_three datetime)
View 2 Replies
View Related
Sep 26, 2015
I am trying to create a sample table in the Azure SQL Data warehouse but its giving me a syntax error Incorrect syntax near the keyword 'CLUSTERED'.
CREATE TABLE [dbo].[FactInternetSales]
( [ProductKey] int NOT NULL
, [OrderDateKey] int NOT NULL
, [CustomerKey] int NOT NULL
, [PromotionKey] int NOT NULL
[Code] ....
what's the correct syntax
View 2 Replies
View Related
Oct 8, 2015
I have table having around 100 million rows.Everyday we have an ETL process in which table will be trucnated and relaoded. Will creating a partition on the table increase the inserting speed?
View 4 Replies
View Related
Jan 9, 2005
I have 1 table with a huge amount of data that I recive from someone else in a flat file format. I want to be able to filter through that data and scrub it and find out the good data and bad data from it.
I'm scrubbing the data using different stored procs that i've created and through a web interface that the user can pick which records they wish to create.
If I were to create a new table for clean records, what is the syntax to keep Appending to that table through the data that i'm obtainig via the stored procs that i've created.
Any thoughts or suggestions are greatly appriciated in advance
Thanks again in advance
RB
View 1 Replies
View Related
Apr 21, 2007
I am trying to create a clustered index on a View of a table that has an xml datatype. This indexing ran for two days and still did not complete. I tried to leave it running while continuing to use the database, but the SELECT statements where executing too slowly and the DML statements where Timing out. I there a way to control the server/cpu resources used by an indexing process. How can I determine the completion percentage or the indexing process. How can I make indexing the view with the xml data type take less time?
The table definition is displayed below.
CREATE TABLE [dbo].[AuditLogDetails](
[ID] [int] IDENTITY(1,1) NOT NULL,
[RecordID] [int] NOT NULL,
[TableName] [varchar](64) NOT NULL,
[Modifications] [xml] NOT NULL,
CONSTRAINT [PK_AuditLogDetails] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The view definition is displayed below.
ALTER VIEW [dbo].[vwAuditLogDetails] WITH SCHEMABINDING
AS
SELECT P.ID,D.RecordID, dbo.f_GetModification(D.Modifications,P.ID) AS Modifications
FROM dbo.AuditLogParent P
INNER JOIN dbo.AuditLogDetails AS D ON dbo.f_GetIfModificationExist(D.Modifications,P.ID)=1
The definition for UDF f_GetModification
ALTER function [dbo].[f_GetModification]( @Modifications xml,@PID uniqueidentifier )
returns xml
with schemabinding
as
begin
declare @pidstr varchar(100)
SET @pidstr = LOWER(CONVERT(varchar(100), @PID))
return @Modifications.query('/Modifications/modification[@ID eq sql:variable("@pidstr")]')
end
The definition for UDF f_GetIfModificationExist
ALTER function [dbo].[f_GetIfModificationExist]( @Modifications xml,@PID uniqueidentifier )
returns Bit
with schemabinding
as
begin
declare @pidstr varchar(100)
SET @pidstr = LOWER(CONVERT(varchar(100), @PID))
return @Modifications.exist('/Modifications/modification[@ID eq sql:variable("@pidstr")]')
end
The Statement to create the index is below.
CREATE UNIQUE CLUSTERED INDEX [IX_ID_RecordID] ON [dbo].[vwAuditLogDetails]
(
[ID] ASC,
[RecordID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
View 1 Replies
View Related
Jul 31, 2006
I am trying to design a SSIS DTS package that imports an Excel named range (acts as a table) into a SQL Server 2005 database table. The direct import (using the import/export wizard) works fine, but I need to use a DTS package with a data transformation step. The DTS fails to create/replace/or update the SQL Server table. Suggestions?
View 3 Replies
View Related
Oct 18, 2006
Hello,
I am working on a webapp using VB.net
Right now I am writing to a sql table during a process where the end user
starts entering the contents for a file that is going to be generated once he
finishes entering the data, but the problem is that if more than one user is
doing the same process the data would get mixed up. To avoid this I
thought in creating a temporary table (its name will consist of a string
and the current date time).
I would like to see any tutorial
about creating and working with temp tables. Or if you have any
suggestions, I will appreciate them. Thanks
View 1 Replies
View Related
Apr 25, 2000
I have used to following Microsoft supplied code to create a link to
SQL server from access: (Is there a way to use a DSN-less connection at comment below? If not is there a way to create a DSN using code?
-------------------------------------------------------------------------
Sub ClientServerX3()
Dim dbsCurrent as Database
Dim tdRoy as TableDef
Set dbsCurrent = CurrentDb
Set tdfRoy = dbsCurrent.CreateTableDef("Roy")
tdfRoy.Connect = "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"
***************IS THERE ANYWAY TO USE AN OLEDB (OR OTHER DSN-LESS)
***************CONNECTION HERE? How??
tdfRoy.SourceTableName = "Royalties"
dbsCurrent.TableDefs.Append tdfRoy
End Sub
Thanks, I really appreciate the help
Sam
View 1 Replies
View Related
May 6, 2007
Using Microsoft SQL 2000
When creating a table I want to be able to specify not only the db to create it on but also which server to create it on. I have two servers that are linked together, I can view all data without issue.
Doing further research it looks like with the create table command you can tell it the new table name and the database but you can't tell it which server to use. Is there a way of doing this?
Example :
CREATE TABLE LAPTOP.database.dbo.tableName (a INT) gives the following error:
The object name 'LAPTOP.database.dbo.' contains more than the maximum number of prefixes. The maximum is 2.
I am new to linked servers so basically my question is, how do you point to a server within sql before I execute the create table command?
Tx in advance
Mark
View 2 Replies
View Related
Jun 11, 2006
Hi,
Is there a way to dynamically create a new table in sql server express using the code behind with vb on a Page_Load event?
Thanks Matt
View 2 Replies
View Related
Dec 8, 2006
Can we do that and how pls?
The purpose is to avoid creating sql server table structure manually when we already have xsd file.
View 6 Replies
View Related
Aug 10, 2006
AA!I am having problems on creating table through Enterprise Manager. Itgives me Error 1038 i.e. is as followsUnexpected ErrorODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot useempty objector column names. Use a single space if necessary.
View 3 Replies
View Related
Jul 20, 2005
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 Related
Nov 26, 2013
I need to ensure some naming standards to users while creating tables.
All table name should be in same pattern
(ie., ) TeamName_EmpId_tablename
I need a way to restrict user to follow the naming standard. If any user tries to create table without the above naming standard system should not allow.
View 5 Replies
View Related
Jul 2, 2014
I am having trouble figuring out why the following code throws an error:
declare
@cols nvarchar(50),
@stmt nvarchar(max)
select @cols = ('[' + W.FKStoreID + ']') from (select distinct FKStoreID from VW_PC_T) as W
select @stmt = '
select *
[Code] ...
The issue that I am having is:
Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value '[' to data type int.
I know that I have to use the [ ] in order to run the dynamic sql. I am not sure what is failing and why as the syntax seems to be clean to me (obviously it is not).
View 6 Replies
View Related
Apr 30, 2015
Environment: Microsoft SQL Server Standard Edition (64-bit), 10.0.5520.0
I was doing a code review for another developer and came across this code:
CREATE TABLE dbo.#ABC
(
ReportRunTime DATETIME
,SourceID VARCHAR(3)
,VisitID VARCHAR(30)
,BaseID VARCHAR(25)
[Code] ....
This EXECUTES with no error or warning message.However, if I change this to CREATE the PK in an ALTER TABLE statement, I get the (expected by me) error:
CREATE TABLE dbo.#ABC
(
ReportRunTime DATETIME
,SourceID VARCHAR(3)
,VisitID VARCHAR(30)
,BaseID VARCHAR(25)
,OccurrenceSeqID INT
[code]...
==> Msg 8111, Level 16, State 1, Line 17 Cannot define PRIMARY KEY constraint on nullable column in table '#ABC'.
==> Msg 1750, Level 16, State 0, Line 17 Could not create constraint. See previous errors.
(note: As the #ABC table is an actual copy of a few of the columns in a "permanent" table, I will likely change the definition as follows such that the columns are defined to match the names / datatypes / NULLability:
SELECT TOP 0
CAST('01-01-1980' AS DATETIME) AS [ReportRunTime]
,SourceID
,VisitID
,BaseID
,OccurrenceSeqID
[Code] .....
View 9 Replies
View Related
May 20, 2015
I have 2 tables (#raw1 & #raw2). Each has a year (yr) and month (mnth) and a count (cnt1 / cnt2) field. I need a table created as follows:
2013 2014 2015
cnt1 cnt2 cnt1 cnt2 cnt1 cnt2
jan 5 77 77 8 88
etc....
Normally, I would ask about a pivot but since it is months down one side and year and columns on the top, I don't know if a pivot will work.
create table #raw1 ([yr] int, [mnth] int, [cnt1] int)
insert into #raw1 values
(2013, 1, 5)
, (2013, 2, 5)
, (2013, 3, 5)
, (2013, 4, 5)
, (2013, 5, 5)
, (2013, 6, 5)
[code]....
View 1 Replies
View Related
Dec 15, 2006
My question is what is the proper way to do this? I have been try the ways that it is instructing me to do.
By creating the name, then I go to a list of potential sources, one being: the local, then another being: the actual domain on the network, last one being: the name of the created data source. I have already tried the local & domain, I will try the last one.
But, my question is if this selection is not going to connect to SQL SERVER. Then, "What should I do within the server to make this connection possible to a client computer look for the access?"
View 1 Replies
View Related
Dec 2, 2014
I have the following query that will serve as a basis for SSRS report
SELECT TOP (1000) d.Project_Name, d.Status, d.Country, d.Region, p.Period, p.Quarter, p.Year, d.Brand, d.Store_Opens_Actual, d.DA, d.DPN, d.StoreNumber,
CONVERT(VARCHAR(10), CASE WHEN ISDATE(d .Store_Opens_Actual) = 1 THEN d .Store_Opens_Actual WHEN ISDATE(d .Store_Opens_Forecast) = 1 AND
ISDATE(d .Store_Opens_Actual) = 0 THEN d .Store_Opens_Forecast WHEN ISDATE(d
[Code] ....
This returns a dataset, that I need to convert into a PIVOT table that should look like the attached spreadsheet.
Having trouble writing the PIVOT table query. I feel like I am missing something conceptually as I am not doing any summing or aggregation. I don't know if dynamics SQL is the solution here or which route to take. I don't know if there is such things as PIVOTING without aggregation. CROSS TAB came to my mind as well.
View 1 Replies
View Related
Apr 3, 2015
I want to create and drop the global temporary table in same statement.
I am using below command but I am getting below error
Msg 2714, Level 16, State 6, Line 11
There is already an object named '##Staging_Temp' in the database.
if object_id('Datastaging..##Staging_Temp') is not null
begin
drop table ##Staging_Temp
end
CREATE TABLE ##Staging_Temp(
[COL001] [varchar](4000) NULL,
[Code] ....
View 1 Replies
View Related
Apr 19, 2015
I am getting error when I am trying to create table on runtime
Declare @FileName varchar(100)
Declare @File varchar(100)
set @FileName='brkrte_121227102828'
SET @File = SUBSTRING(@FileName,1,CHARINDEX('_',@FileName)-1)
--=select @File
[Code] ....
Error massage:-
Msg 203, Level 16, State 2, Line 16
The name 'CREATE TABLE DataStaging.dbo.Staging_brkrte ( [COL001] VARCHAR (4000) NOT NULL, [Id] Int Identity(1,1), [LoadDate] datetime default getdate() )' is not a valid identifier.
How to resolve above error....
View 4 Replies
View Related
Jul 11, 2015
Recently I've been stumbling with a way to properly produce a result that provides me a count of sales orders that fall in a range of sales order totals.
I want to take the Sales Order Total and then count how many times it falls into a specific bucket.
I've included the script where I've left with sample data so you can run in into a tempdb.
I hope to have the end result look like the below summary if you use my sample data.
Count Less Than 5K: 5
Count Greater Than 5K Less Than 10K: 0
Count Greater Than 10K Less Than 25K: 3
Count Greater Than 25K: 2
Here is the script to set up the tempdb
use tempdb
go
/*
drop table #salesinvoice;
drop table #salesinvoicedetail;
[Code] ....
Below is my query, I change it to perform a count on the summarized totals.
select
si.OrderID as OrderNum
,sum(case when sid.LineAmount < 5000 then 1 else 0 end) as LT5K
,sum(case when sid.LineAmount >=5000 and sid.LineAmount < 10000 then 1 else 0 end) as GT5kLT10k
,sum(case when sid.LineAmount >=10000 and sid.LineAmount < 25000 then 1 else 0 end) as GT10kLT25k
[Code] ....
Here are the order totals.
SI201 = 41450
SI202 = 11600
SI203 = 1210
SI204 = 16001
SI205 = 2750
SI206 = 12500
SI207 = 25450
SI208 = 4500
SI209 = 3850
SI210 = 2575
And the desired result would tell me the below summary. The final query can be column based. I just write this in rows for clarity.
Count Less Than 5K: 5
Count Greater Than 5K Less Than 10K: 0
Count Greater Than 10K Less Than 25K: 3
Count Greater Than 25K: 2
******/
View 3 Replies
View Related
Nov 13, 2014
I could deploy across my environment, which is a mix of 2008R2/2012 servers, to give some information on log files. Running into a silly issue right off the bat. The table that DBCC LogInfo() conjures out of magic is different between the two. In 2012 it gained the RecoveryUnitID column. So I'm trying to write some logic to create a temp table based on which version is running. I would like to avoid a global temp table if possible. Here's what I've tried:
sp_executesql creates a table outside of the scope of my session:
DECLARE @PrVers NVARCHAR(128)
, @PrVersNum DECIMAL(10,2)
, @StageTable NVARCHAR(1024) = N''
[code]....
View 9 Replies
View Related
May 29, 2015
I have written a script to pivot a table into multiple columns.
The script works when run on its own but gives an error when i try to create a view or aprocedure from the same script. The temporary table #.... does not work so i have converted it to a cte.
Here is a copy of the script below
-- Dynamic PIVOT
IF OBJECT_ID('#External_Referrals') IS NULL
DROP TABLE #External_Referrals;
GO
DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);
[Code] ....
View 7 Replies
View Related
May 26, 2007
Hello,
Quick question, I hope, I am trying to create a table that has a column that is a nested table in SQL Server 2005 Express Edition. Any ideas how I could go about doing this?
Sincerely,
James Simpson
Straightway Technologies Inc.
View 4 Replies
View Related
Jul 26, 2007
Hi everyone,
we have some reference tables in in a specific database. that other applications need to have access to them. Is it possible to create a view in the application's database to retrive data from ref database while users just have access to the application Database not the view's underlying tables?
Thanks
View 1 Replies
View Related
