Hi,
I am new to SQL and i am trying to create a table from a table. below is my query but im getting an error
CREATE TABLE Production.TransactionHistoryArchive1 AS
(SELECT ProductID,SUM(Quantity) QuantitySum,SUM(LineItemTotalCost) TotalCostByID FROM Production.TransactionHistoryArchive
GROUP BY ProductID)
error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.
Hi all,
I have SQL Server Management Studio Express (SSMS Express) and SQL Server 2005 Express (SS Express) installed in my Windows XP Pro PC that is on Microsoft Windows NT 4 LAN System. My Computer Administrator grants me the Administror Privilege to use my PC. I tried to use SQLQuery.sql (see the code below) to create a table "LabResults" and insert 20 data (values) into the table. I got Error Messages 102 and 156 when I did "Parse" or "Execute". This is my first time to apply the data type 'decimal' and the "VALUES" into the table. I do not know what is wrong with the 'decimal' and how to add the "VALUES": (1) Do I put the precision and scale of the decimal wrong? (2) Do I have to use "GO" after each "VALUES"? Please help and advise.
Thanks in advance,
Scott Chang
///////////--SQLQueryCroomLabData.sql--///////////////////////////
USE MyDatabase
GO
CREATE TABLE dbo.LabResults
(SampleID int PRIMARY KEY NOT NULL,
SampleName varchar(25) NOT NULL,
AnalyteName varchar(25) NOT NULL,
Concentration decimal(6.2) NULL)
GO
--Inserting data into a table
INSERT dbo.LabResults (SampleID, SampleName, AnalyteName, Concentration)
VALUES (1, 'MW2', 'Acetone', 1.00)
VALUES (2, 'MW2', 'Dichloroethene', 1.00)
VALUES (3, 'MW2', 'Trichloroethene', 20.00)
VALUES (4, 'MW2', 'Chloroform', 1.00)
VALUES (5, 'MW2', 'Methylene Chloride', 1.00)
VALUES (6, 'MW6S', 'Acetone', 1.00)
VALUES (7, 'MW6S', 'Dichloroethene', 1.00)
VALUES (8, 'MW6S', 'Trichloroethene', 1.00)
VALUES (9, 'MW6S', 'Chloroform', 1.00)
VALUES (10, 'MW6S', 'Methylene Chloride', 1.00
VALUES (11, 'MW7', 'Acetone', 1.00)
VALUES (12, 'MW7', 'Dichloroethene', 1.00)
VALUES (13, 'MW7', 'Trichloroethene', 1.00)
VALUES (14, 'MW7', 'Chloroform', 1.00)
VALUES (15, 'MW7', 'Methylene Chloride', 1.00
VALUES (16, 'TripBlank', 'Acetone', 1.00)
VALUES (17, 'TripBlank', 'Dichloroethene', 1.00)
VALUES (18, 'TripBlank', 'Trichloroethene', 1.00)
VALUES (19, 'TripBlank', 'Chloroform', 0.76)
VALUES (20, 'TripBlank', 'Methylene Chloride', 0.51)
GO
//////////Parse///////////
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '6.2'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'VALUES'.
////////////////Execute////////////////////
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '6.2'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'VALUES'.
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
View 6 Replies View Relatedwhich is more efficient...which takes less memory...how is the memory allocation done for both the types.
View 1 Replies View RelatedDear All,
I was trying to create a table with many columns. However, I got the following error. Have anyone seen this error before?
>>>>>
Warning: The table 'PDMS_USER' has been created but its maximum row size (9118) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
<<<<<
Thanks.
Hi
I've been looking at scripting some create tables, but want to know if the table created successfully. I've been doing the following:
DECLARE @ERRCODE INT
CREATE TABLE x
SET @ERRCODE = @@ERROR
This works ok, if there is no erroor. However if I run this again, then obviously I get the error "This object already exists" and the script stops executing.
Is there a way that I can capture the error using @@ERROR and still let the script run ?
Thanks in advance
Mickster
Hi
Working with SQL Server 2005
I am trying to create a table with the following code
create table department
(dept_id smallint unsigned not null auto_increment,
name varchar(20) not null,
constraint pk_department primary key (dept_id)
)
but keep coming up with an error. I suspect that I am trying to use mysql (which I don't want to use!) instead of sql, but can't find the changes I need to make.
Thank you in advance
CREATE PROCEDURE dbo.spCreateObBabyEpisodeArchive AS
DECLARE @TableName as varchar(400)
DECLARE @SQL as varchar(8000)
SET @TableName = 'ObBabyEpisode' + CONVERT(varchar,REPLACE (CONVERT(VARCHAR, GETDATE(), 106) , ' ', ''))
SET @SQL = 'CREATE TABLE ' + @TableName + '
(
AdmitSource INT NOT NULL DEFAULT 0,
Anaesthesia3rdINT NOT NULL DEFAULT 0,
Anaesthetist3rdINT NOT NULL DEFAULT 0,
Apgar1INT NOT NULL DEFAULT 0,
Apgar10INT NOT NULL DEFAULT 99,
Apgar5INT NOT NULL DEFAULT 0,
ApgarOtherVARCHAR(20) NOT NULL DEFAULT '' '',
AttAccoucherVARCHAR(30) NOT NULL DEFAULT '' '',
AttAccoucherRankVARCHAR(30) NOT NULL DEFAULT '' '',
AttMidwifeVARCHAR(30) NOT NULL DEFAULT '' '',
AttMidwifeRankVARCHAR(30) NOT NULL DEFAULT '' '',
AttOthersVARCHAR(30) NOT NULL DEFAULT '' '',
AttOthersRankVARCHAR(30) NOT NULL DEFAULT '' '',
AugmentationVARCHAR(30) NOT NULL DEFAULT '' '',
BabyAdmitDrINT NOT NULL DEFAULT 0,
BabyDisDateVARCHAR(12) NOT NULL DEFAULT '' '',
BabyDisDestINT NOT NULL DEFAULT 0,
BabyDisFeedINT NOT NULL DEFAULT 0,
BabyDisHospINT NOT NULL DEFAULT 0,
BabyDisStaffINT NOT NULL DEFAULT 0,
BabyDisTimeVARCHAR(7) NOT NULL DEFAULT '' '',
BabyEddVARCHAR(12) NOT NULL DEFAULT '' '',
BabyGenderVARCHAR(20) NOT NULL DEFAULT '' '',
BabyLosINT NOT NULL DEFAULT 0,
BabyNumINT NOT NULL DEFAULT 0,
BabyPostMedsVARCHAR(30) NOT NULL DEFAULT '' '',
BabyRecStatusVARCHAR(50) NOT NULL DEFAULT '' '',
BabyRegStaffINT NOT NULL DEFAULT 0,
BabyReqMandatoryFieldsVARCHAR(250) NOT NULL DEFAULT '' '',
BabyScnNicuVARCHAR(6) NOT NULL DEFAULT '' '',
BabyTreatmentVARCHAR(80) NOT NULL DEFAULT '' '',
BabyUrnoVARCHAR(20) NOT NULL DEFAULT '' '',
BabyWardINT NOT NULL DEFAULT 0,
BirthAnaesVARCHAR(30) NOT NULL DEFAULT '' '',
BirthDateVARCHAR(12) NOT NULL DEFAULT '' '',
BirthDefectVARCHAR(80) NOT NULL DEFAULT '' '',
BirthLengthDECIMAL NOT NULL DEFAULT 0,
BirthModeINT NOT NULL DEFAULT 0,
BirthPlaceINT NOT NULL DEFAULT 0,
BirthStatusVARCHAR(55) NOT NULL DEFAULT '' '',
BirthTimeVARCHAR(7) NOT NULL DEFAULT '' '',
BirthTraumaVARCHAR(30) NOT NULL DEFAULT '' '',
BirthTypeINT NOT NULL DEFAULT 0,
BirthWeightVARCHAR(10) NOT NULL DEFAULT '' '',
CaesarTypeVARCHAR(20) NOT NULL DEFAULT '' '',
CmpAccPaidDECIMAL NOT NULL DEFAULT 0,
CmpAccRcvdDECIMAL NOT NULL DEFAULT 0,
CmpCategoryINT NOT NULL DEFAULT 0,
CmpDisDateVARCHAR(12) NOT NULL DEFAULT '' '',
CmpDisFeedINT NOT NULL DEFAULT 0,
CmpFeedChangeVARCHAR(6) NOT NULL DEFAULT '' '',
CmpFeedReasonINT NOT NULL DEFAULT 0,
CmphomeConsultINT NOT NULL DEFAULT 0,
CmpHospConsultINT NOT NULL DEFAULT 0,
CmpNotHomeINT NOT NULL DEFAULT 0,
CmpOutPhoneINT NOT NULL DEFAULT 0,
CmpOutreachTotINT NOT NULL DEFAULT 0,
CmpPhoneConsultINT NOT NULL DEFAULT 0,
CmpProblemINT NOT NULL DEFAULT 0,
CmpProviderINT NOT NULL DEFAULT 0,
CmpReadmitBabyINT NOT NULL DEFAULT 0,
CmpReadmitMotherINT NOT NULL DEFAULT 0,
CmpReferBabyINT NOT NULL DEFAULT 0,
CmpReferMotherINT NOT NULL DEFAULT 0,
CmpVisitTotINT NOT NULL DEFAULT 0,
CordAnalysisTimevarchar(25) NOT NULL DEFAULT '' '',
CordBaseExcessvarchar(6) NOT NULL DEFAULT '' '',
CordBloodVARCHAR(6) NOT NULL DEFAULT '' '',
CordCompsVARCHAR(55) NOT NULL DEFAULT '' '',
CordInsertionVARCHAR(55) NOT NULL DEFAULT '' '',
CordLactateVARCHAR(6) NOT NULL DEFAULT '' '',
CordPhVARCHAR(6) NOT NULL DEFAULT '' '',
CordStemBloodVARCHAR(6) NOT NULL DEFAULT '' '',
CordVesselsINT NOT NULL DEFAULT 0,
DischWeightvarchar(10) NOT NULL DEFAULT '' '',
DisFeedReasonINT NOT NULL DEFAULT 0,
EndDate3VARCHAR(12) NOT NULL DEFAULT '' '',
EndTime3VARCHAR(6) NOT NULL DEFAULT '' '',
EpisodeIDINT NOT NULL ,
EstGestDECIMAL NOT NULL DEFAULT 0,
EstRespsINT NOT NULL DEFAULT 0,
ExclusiveBFVARCHAR(6) NOT NULL DEFAULT '' '',
FirstFeedDateVARCHAR(12) NOT NULL DEFAULT '' '',
FirstFeedModeINT,
FirstFeedTimeVARCHAR(6) NOT NULL DEFAULT '' '',
FoetMonVARCHAR(30) NOT NULL DEFAULT '' '',
ForcepTypeVARCHAR(100) NOT NULL DEFAULT '' '',
HeadCircmDECIMAL,
HearTestDateVARCHAR(12) NOT NULL DEFAULT '' '',
HearTestResultVARCHAR(20) NOT NULL DEFAULT '' '',
HepBvDateVARCHAR(12) NOT NULL DEFAULT '' '',
HepBvTimeVARCHAR(6) NOT NULL DEFAULT '' '',
HindleakDateVARCHAR(55) NOT NULL DEFAULT '' '',
HindleakTimeVARCHAR(6) NOT NULL DEFAULT '' '',
ID INT NOT NULL ,
ImmGnDateVARCHAR(12) NOT NULL DEFAULT '' '',
ImmGnTimeVARCHAR(6) NOT NULL DEFAULT '' '',
InductionDateVARCHAR(12) NOT NULL DEFAULT '' '',
InductionTimeVARCHAR(6) NOT NULL DEFAULT '' '',
InductMainINT,
InductModeVARCHAR(30) NOT NULL DEFAULT '' '',
InductOtherVARCHAR(250) NOT NULL DEFAULT '' '',
IntendedChangedVARCHAR(55) NOT NULL DEFAULT '' '',
IntendedPlaceVARCHAR(30) NOT NULL DEFAULT '' '',
IntendedSpecifyVARCHAR(55) NOT NULL DEFAULT '' '',
IntendedYnVARCHAR(6) NOT NULL DEFAULT '' '',
KonakDateVARCHAR(12) NOT NULL DEFAULT '' '',
KonakRouteVARCHAR(10) NOT NULL DEFAULT '' '',
LabAnalgesiaVARCHAR(30) NOT NULL DEFAULT '' '',
LabCompsVARCHAR(60) NOT NULL DEFAULT '' '',
LabDrugsVARCHAR(30) NOT NULL DEFAULT '' '',
LabourTime1VARCHAR(6) NOT NULL DEFAULT '' '',
LabourTime2VARCHAR(6) NOT NULL DEFAULT '' '',
LabourTime3VARCHAR(6) NOT NULL DEFAULT '' '',
LastUpdateVARCHAR(55) NOT NULL DEFAULT getdate(),
LiquorINT NOT NULL DEFAULT 0,
MembDateVARCHAR(30) NOT NULL DEFAULT '' '',
MembRuptureVARCHAR(55) NOT NULL DEFAULT '' '',
MembTimeVARCHAR(6) NOT NULL DEFAULT '' '',
NeoMorbVARCHAR(80) NOT NULL DEFAULT '' '',
NewBornScrVARCHAR(12) NOT NULL DEFAULT '' '',
ObsAccClassVARCHAR(10) NOT NULL DEFAULT '' '',
ObsAdmitDateVARCHAR(12) NOT NULL DEFAULT '' '',
ObsAdmitTimeVARCHAR(6) NOT NULL DEFAULT '' '',
OnsetDate1VARCHAR(12) NOT NULL DEFAULT '' '',
OnsetDate2VARCHAR(12) NOT NULL DEFAULT '' '',
OnsetLabVARCHAR(30) NOT NULL DEFAULT '' '',
OnsetTime1VARCHAR(6) NOT NULL DEFAULT '' '',
OnsetTime2VARCHAR(6) NOT NULL DEFAULT '' '',
OpdelAnaesthetist INT NOT NULL DEFAULT 0,
OpdelDilationDECIMAL NOT NULL DEFAULT 0,
OpdelMainINT NOT NULL DEFAULT 0,
OpdelNatureVARCHAR(40) NOT NULL DEFAULT '' '',
OpdelOtherVARCHAR(30) NOT NULL DEFAULT '' '',
Oxytocic3VARCHAR(30) NOT NULL DEFAULT '' '',
PassedMecVARCHAR(6) NOT NULL DEFAULT '' '',
PassedUrineVARCHAR(6) NOT NULL DEFAULT '' '',
PdcuBatchErrorVARCHAR(250) NOT NULL DEFAULT '' '',
PdcuBatchIDINT NOT NULL DEFAULT '' '',
PeriAnaesINT NOT NULL DEFAULT '' '',
PeriStatusINT NOT NULL DEFAULT '' '',
PeriSuturedByINT NOT NULL DEFAULT '' '',
PlacentaAbnormVARCHAR(100) NOT NULL DEFAULT '' '',
PlacentaDelModeVARCHAR(55) NOT NULL DEFAULT '' '',
PlacentaMemVARCHAR(55) NOT NULL DEFAULT '' '',
PositionVARCHAR(40) NOT NULL DEFAULT '' '',
PresentationINT NOT NULL DEFAULT '' '',
RefHospitalINT NOT NULL DEFAULT '' '',
RegistrarFlagINT NOT NULL DEFAULT '' '',
ResusVARCHAR(60) NOT NULL DEFAULT '' '',
ScalpLactateDateVARCHAR(50) NOT NULL DEFAULT '' '',
ScalpLactateTime VARCHAR(30) NOT NULL DEFAULT '' '',
ScalpLactateValue VARCHAR(30) NOT NULL DEFAULT '' '',
SkinContactVARCHAR(6) NOT NULL DEFAULT '' '',
SkinContactDateVARCHAR(12) NOT NULL DEFAULT '' '',
SkinContactReasonGT30INT NOT NULL DEFAULT '' '',
SkinContactTimeVARCHAR(6) NOT NULL DEFAULT '' '',
TotLabourTimeVARCHAR(6) NOT NULL DEFAULT '' '',
UrNoVARCHAR(20) NOT NULL,
VCordBaseExcessVARCHAR(6) NOT NULL DEFAULT '' '',
VCordLactate VARCHAR(6) NOT NULL DEFAULT '' '',
VCordPhVARCHAR(6) NOT NULL DEFAULT '' ''
)'
EXEC @SQL
GO
When I run the query, I get this error
Server: Msg 203, Level 16, State 2, Line 172
The name 'CREATE TABLE ObBabyEpisode18Jun2007
(
AdmitSource INT NOT NULL DEFAULT 0,
Anaesthesia3rdINT NOT NULL DEFAULT 0,
Anaesthetist3rdINT NOT NULL DEFAULT 0,
Apgar1INT NOT NULL DEFAULT 0,
Apgar10INT NOT NULL DEFAULT 99,
Apgar5INT NOT NULL DEFAULT 0,
ApgarOtherVARCHAR(20) NOT NULL DEFAULT ' ',
AttAccoucherVARCHAR(30) NOT NULL DEFAULT ' ',
AttAccoucherRankVARCHAR...
Line 172 Points to EXEC @SQL
I cannot understand what is the error. Any clues?
CREATE TABLE Agents(ID COUNTER NOT NULL CONSTRAINT constraintName_pk PRIMARY KEY,Name VARCHAR(255),Supervisor INTEGER,MasterCalendarVisible BOOLEAN default false)I'm using this against an Access DB using JET driver... I keep gettinga syntax error which goes away when I remove the last line... Anyideas whats wrong with it?Thanks!
View 1 Replies View RelatedHi,
I have connected sdf database through SQL server management studio.
I want to execute a simple query as,
CREATE TABLE [dbo].[user]( [user_id] [int] NOT NULL)
While executing the same through Query pane, it gives error as,
<>
Major Error 0x80040E14, Minor Error 26100
> CREATE TABLE [dbo].[user]( [user_id] [int] NOT NULL)
The table name is not valid. [ Token line number (if known) = 1,Token line offset (if known) = 22,Table name = user ]
<>
Any pointers?
Thanks,
Shailesh.
CREATE TABLE IF NOT EXISTS TempA (id int);
CREATE TABLE IF NOT EXISTS TempB (id int);
For some reason the above statements are giving me syntax errors?
I want to create table only if it does not already exist.
Also, can the same "if not exists" clause be applied to "DROP TABLE" and "TRUNCATE" ?
thx in advance .
I have both MSDE and SQL Server 2000 on my development PC.
Using Server Explorer - select MSDE server - right click on Tables icon - and I'm offered Create New Table.
Using Server Explorer - select SQL Server 2000 server - right click on Tables icon - and I'm NOT offered Create New Table.
Both servers are configured identically (except for whatever is keeping me from Create Table ...) Any suggestions?
I'm trying to create a table in Microsoft Server Management Studio 2012. The table has two fields which are both foreign keys.
I created the following:
create table tblRoomEquipment(
RoomID nvarchar(8),
EquipmentType nvarchar(1),
foreign key (RoomID) references tblRoom(ID),
foreign key (EquipmentType) references tblEquipment(Type)
)
Both tblRoom and tblEquipment have the red line error which when I highlight say the they both reference an invalid table!
Both tables are there and have primary keys defined as ID & Type. I have searched around and all I could find was that there maybe a permission problem.
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.
'****************************************************************************
Cmd.CommandText = "Drop Table Raj"
Cmd.ExecuteNonQuery()
Cmd.CommandText = "Select * Into Raj From XXX"
Cmd.ExecuteNonQuery()
'**************************************************************************
This generates error that Table already exist.
If Wait 1 sec then execute statement then it works fine.
Thanks in Advance
Piyush Verma
i have created a fact table which has unique cluster index as below,
CREATE UNIQUE CLUSTERED INDEX [FactSales_SalesID] ON [dbo].[FactSales] (salesid ASC)
WITH (DATA_COMPRESSION = PAGE)
GO
however later when i add CLUSTERED COLUMNSTORE INDEXES :
CREATE CLUSTERED COLUMNSTORE INDEX CSI_FactSales
ON dbo.FactSales WITH (DATA_COMPRESSION = COLUMNSTORE)
GO
it prompts error.
Msg 35372, Level 16, State 3, Line 167 You cannot create more than one clustered index on table 'dbo.FactSales'. Consider creating a new clustered index using 'with (drop_existing = on)' option.
i am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?
--create the procedure and insert into the temp table without creating it.
--no compilation error.
CREATE PROC testTemp
AS
BEGIN
INSERT INTO #tmp(dt)
SELECT GETDATE()
END
only on calling the proc does this give an execution error
I’ve got a situation where the columns in a table we’re grabbing from a source database keep changing as we need more information from that database. As new columns are added to the source table, I would like to dynamically look for those new columns and add them to our local database’s schema if new ones exist. We’re dropping and creating our target db table each time right now based on a pre-defined known schema, but what we really want is to drop and recreate it based on a dynamic schema, and then import all of the records from the source table to ours.It looks like a starting point might be EXEC sp_columns_rowset 'tablename' and then creating some kind of dynamic SQL statement based on that. However, I'm hoping someone might have a resource that already handles this that they might be able to steer me towards.Sincerely,
Bryan Ax
I would like to create a table valued function that fetch through the table below using a cursor and return the records that are unique
EmpidChDateSiteuseridinitsal finsalNote
-------------------------------------------- ----------
236102015-4-21 22:02:10.8072570 0.696176161 change inisal value
236112015-4-21 22:02:11.0502570 0.696176161change inisal value
236122015-4-21 22:02:11.1202570 0.696176161 change inisal value
236132015-4-21 22:02:11.2452570 0.696176161change inisal value
I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).
However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.
Below code.
ALTER Procedure [dbo].[sp_ViewCreate]
/* Input Parameters */
@TableName Varchar(20),
@Dist Varchar(20)
AS
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)
[code]....
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
Hi all, please help. I m trying to create an "empty" table from existing table for the audit trigger purpose.
For now, i am trying to create an empty audit table for every table in a database named "pubs", and it's seem won't work.
Please advise.. Thanks in advance.
Here is my code:
Code Snippet
USE pubs
DECLARE @TABLE_NAME sysname
DECLARE @AUDIT_TABLE VARCHAR(50)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME NOT LIKE 'audit%'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
AND TABLE_NAME = 'sales'
WHILE @TABLE_NAME IS NOT NULL
BEGIN
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_NAME = 'sales'
SELECT @AUDIT_TABLE = 'Audit'+''@TABLE_NAME''
SELECT * INTO @AUDIT_TABLE
FROM @TABLE_NAME
TRUNCATE TABLE @AUDIT_TABLE
ALTER TABLE @AUDIT_TABLE ADD UserAction Char(10),AuditStartTime Char(50),AuditUser Char(50)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
AND TABLE_NAME NOT LIKE 'audit%'
END
Thanks. ..
Is there any Posibility to change a User Table to System Table.
How to create one system table.
I am in Big mess that One of the Table I am using is in System Type.
I cant Index the same. Is there any Mistake we can change a user table to system table.....
Hi, all experts here,
Thank you very much for your kind attention.
I am trying to create a new mining structure with case table and nested table, the case table (fact table) has alread defined the relationships with the nested table(dimension table), and I can see their relationship from the data source view. But why the wizard for creating the new mining structure showed that message? Why is that? And what could I try to fix it?
Hope it is clear for your help.
Thanks a lot for your kind advices and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
Hello,
I'd like to create a temporary table with the same schema as an exiting table. How can I do this without hard coding the column definitions into the temporary table definition?
I'd like to do something like:
CREATE TABLE #tempTable LIKE anotherTable
..instead of...
CREATE TABLE #tempTable (id INT PRIMARY KEY, created DATETIME NULL etc...
I'm sure there must be a simple way to do this!
Many thanks,
Ben S
I need to create a table from these 2 tables and want all the data. Table 1 has 15000 records and table has around 1000 records.
I have 2 tables.
SELECT [UniqueID]
,[Company]
,[CustID]
,[CustomerName]
,[FiscYr]
[Code] ....
I am using SQL SERVER 2012..I have the following table:
tbtab1
| A | B | C |
| 1 | Pluto | NULL |
| 2 | Pippo | NULL |
| 3 | Rossi | NULL |
I want to creare a new empy table with the columns name contained into the column B of the first table; the following should my results:
tbtab2
| Pluto | Pippo | Rossi |
Hi all,
my stored procedure have one table variable (@t_Replenishment_Rpt).I want to create an Index on this table variable.please advise any of them in this loop...
below is my table variable and I need to create 3 indexes on this...
DECLARE @t_Replenishment_Rpt TABLE
(
Item_Nbr varchar(25) NULL,
Item_Desc varchar(255) NULL,
Trx_Date datetime NULL,
Balance int NULL,
Trx_Type char(10) NULL,
Issue_Type char(10) NULL,
Location char(25) NULL,
Min_Stock int NULL,
Order_Qty int NULL,
Unit char(10) NULL,
Issue_Qty int NULL,
Vendor varchar(10) NULL,
WO_Nbr varchar(10) NULL,
Lead_Time int NULL,
PO_Nbr char(10) NULL,
PO_Status char(10) NULL,
Currency char(10) NULL,
Last_Cost money NULL,
Dept_No varchar(20) NULL,
MSDSNbr varchar(10) NULL,
VendorName varchar(50) NULL,
Reviewed varchar(20) NULL
)
I tryed all below senarios...it is giving error...
--Indexing the @t_Replenishment_Rpt table on the column Names Item Number, Vender , Department Number
--EXEC sp_executesql(CREATE UNIQUE CLUSTERED INDEX Replenishment_index ON @t_Replenishment_Rpt (Item_Nbr))
--CREATE UNIQUE CLUSTERED INDEX Idx1 ON @t_Replenishment_Rpt.Item_Nbr
INDEX_COL ( '@t_Replenishment_Rpt' , ind_Replenishment_id , Item_Nbr )
--EXEC sp_executesql('SELECT INDEXPROPERTY('+ '@t_Replenishment_Rpt' + ', ' + 'Item_Nbr' + ',' + 'IsPadIndex' + ')')
--EXEC sp_executesql(SELECT INDEXPROPERTY('@t_Replenishment_Rpt', 'Vendor','IsPadIndex'))
--EXEC sp_executesql(SELECT INDEXPROPERTY('@t_Replenishment_Rpt', 'Dept_No','IsPadIndex'))
SSIS Newbie Question:
I have a simple Control Flow setup that checks to see if a particular table exists. If the table does not exists, the table is created in an alternate path, if it does exist, the table is truncated before moving to a file import Data Flow that uses an OLE DB Destination to output the imported data.
My problem is, that I get OLE DB package errors if the table the OLE DB Destination Container references does not exist when I load the package.
How can I over come this issue? I need to be able to dynamically create the table in an earlier step, then use that table to import data into in a later step in the workflow.
Is there a switch I can use to turn off checking in the OLE DB Destination Container so that it will allow me to hook up the table creation step?
Seems like this would be a common task...
Steps:
1. Execute SQL Task to see if the required table exists
2. Use expresions to test a variable to check the results of step 1
3. If table exists, truncate the table and reload it from file in Data Flow using OLE DB Destination
4. If table does not exist, 1st create it, then follow the normal Data Flow
Can someone help me with this?
Signed: Clueless with a deadline approaching...
Hi,
I have application in which i am performing synchronization between SQL Server 2000 and SQL Server 2005 CE.
I have one table "ItemMaster" in my database.There is no relationship with this table,it is standalone.I am updating its values from Windows Mobile Device.
I am performing below operations for that.
Step : 1 Pull To Mobile
Code BlockmoSqlCeRemoteDataAccess.Pull("ItemMaster", "SELECT * FROM ItemMaster", lsConnectString,RdaTrackOption.TrackingOn);
Step : 2 Using one device form i am updating table "ItemMaster" table's values.
Step : 3 Push From Mobile
Code BlockmoSqlCeRemoteDataAccess.Push("ItemMaster", msConnectString);
So i am getting an error on 3rd step.
While i am trying to push it says,
"The Push method returned one or more error rows. See the specified error table. [ Error table name = ]".
I have tried it in different ways but still i am getting this error.
Note : Synchronization is working fine.There is not issue with my IIS,SQL CE & SQL Server 2k.
Can any one help me?I am trying for that since last 3 days.
Hi,
Anyone can help me?
How to create Table A by inserting all the data from Table B?
Cheers,
Daniel.
which one is smarter, where there is no indexing on the table which is really simple table delete everything or recreate table. I got an argument with one of my coworker. He says it doesnt matter i say do delete. Any opinions.
View 7 Replies View Relatedi have 6 table in SQL Server and i have created one view and create single table by linking all the table,now i want to join two column like
Column A and Column B = Column C
e.g
A B C
Atul Jadhav Atuljadhav
Vijay vijayvijay
in above exambe column A having firstName and Column B having second name and i want to join this two column in C column "atuljadhav" and if column B is blank then it join A value tow timestriger code as it is auto update column and every time (update, append, modify, delete) it should be update automatic