Insert Output Of SP Into Table
Aug 20, 2007Hi gurus
Please help me to get out the problem ... i wanna store the output of SP_MONITOR into a table. Please let me know how can i do this
Thanks & Regards
Chander
ADVERTISEMENT
SQL 2012 :: How To Insert Powershell Output Into A Table
Mar 20, 2015I'd like to know how to get windows events script below into a SQL Server Table.
Get-WinEvent -LogName application -MaxEvents 200 | where {$_.LevelDisplayName -eq "Error"}
Insert Record In Table - Select Output Of Query
Oct 14, 2013I had a query and i need to insert record in table and also want to select output of query.
Actually I need to insert and show data in same query.
;with cte as (
select id , status
from tbl_main
where id = 15555
)
insert into testinsert (id , status)
select * from cte
SQL Server 2014 :: Insert Stored Procedure Output To Table And Add Datetimestamp
Jun 22, 2015I have a need to insert stored procedure output a table and in addition to that add a datetimestamp column.. For example, Below is the process to get sp_who output into Table_Test table. But I want to add one additional column in Table_test table with datetimestamp when the procedure was executed.
insert into Table_Test execute sp_who
Interaction Between Instead Of Insert Trigger And Output Clause Of Insert Statement
Jan 14, 2008
This problem is being seen on SQL 2005 SP2 + cumulative update 4
I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable
I now need to add an "instead of insert" trigger to the table that is the subject of the insert.
As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows
Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table
Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.
To run the repro below - select each of the sections below in turn and execute them
1) Create the table
2) Create the trigger
3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value
4) Drop the trigger
5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row
I need the behaviour to be correct when the trigger is present
Any thoughts would be much appreciated
aero1
/************************************************
1) - Create the table
************************************************/
CREATE TABLE [dbo].[my_table](
[my_table_id] [bigint] IDENTITY(1,1) NOT NULL,
[forename] [varchar](100) NULL,
[surname] [varchar](50) NULL,
CONSTRAINT [pk_my_table] PRIMARY KEY NONCLUSTERED
(
[my_table_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 70) ON [PRIMARY]
)
GO
/************************************************
2) - Create the trigger
************************************************/
CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO my_table
(
forename,
surname)
SELECT
forename,
surname
FROM inserted
END
/************************************************
3) - Do the insert
************************************************/
DECLARE @my_insert TABLE( my_table_id bigint )
declare @forename VARCHAR(100)
declare @surname VARCHAR(50)
set @forename = N'john'
set @surname = N'smith'
INSERT INTO my_table (
forename
, surname
)
OUTPUT inserted.my_table_id INTO @my_insert
VALUES( @forename
, @surname
)
select @@identity -- expect this value in @my_insert table
select * from @my_insert -- OK value without trigger - zero with trigger
/************************************************
4) - Drop the trigger
************************************************/
drop trigger [dbo].[trig_my_table__instead_insert]
go
/************************************************
5) - Re-run insert from 3)
************************************************/
-- @my_insert now contains row expected with identity of inserted row
-- i.e. OK
Output And Error Output Write The Same Table At The Same Time, Stall The Process.
Aug 30, 2006Hi
I have Lookup task to determine if source data should be updated to or insert to the customer table. After Lookup task, the Error Output pipeline will redirect to insert new data to the table and the Output pipeline will update customer table. But these two tasks will be processing at the same time which causes stall on the process. Never end.....
The job is similiart to what Slow Changing Dimention does but it won't update the table at the same time.
What can I do to avoid such situation?
Thanks in advance,
JD
OPENXML Insert/Output
Mar 11, 2008Hi Guru's,
INSERT INTO Books (BookName) SELECT BookName FROM OPENXML (@hDoc, '/Objects/Book', 2) WITH (BooKName nvarchar(255)) xmlBookss WHERE NOT EXISTS (SELECT ID FROM Books WHERE Books.BookName = xmlBooks.BookName)
Where my XML looks like:
<Objects>
<Book>
<ForeignID>876</ForeignID>
<BookName>SQLTeam Rocks</BookName>
</Book>
<Book>
<ForeignID>981</ForeignID>
<BookName>My SQL must get better</BookName>
</Book>
</Objects>
When I insert run the query I want to get the @@IDENTITY for each row and insert them with the corresponding ForeignID into a lookup table structured:
[ID Auto], [BooksID (from Books table)], [ForeignID (from XML)]
Any thoughts appreciated!
Retrieving Output Paramater After Insert
Sep 14, 2006Can some one offer me some assistance? I'm using a SQLDataSource control to call a stored proc to insert a record into a control. The stored proc I'm calling has an output paramater that returns the new rows identity field to be used later. I'm having trouble getting to this return value via the SQLDataSource . Below is my code (C#): SqlDataSource1.InsertParameters["USR_AUTH_NAME"].DefaultValue = storeNumber;SqlDataSource1.InsertParameters["usr_auth_pwd"].DefaultValue = string.Empty;SqlDataSource1.InsertParameters["mod_usr_name"].DefaultValue = "SYSTEM";SqlDataSource1.InsertParameters["usr_auth_id"].Direction = ParameterDirection.ReturnValue;SqlDataSource1.Insert();int id = int.Parse(SqlDataSource1.InsertParameters["usr_auth_id"].DefaultValue); below is the error I'm getting: System.Data.SqlClient.SqlException: Procedure 'csi_USR_AUTH' expects parameter '@usr_auth_id', which was not supplied. Has anyone done this before and if so how did you do it?
View 1 Replies View RelatedHow Can You Use A Variable Tablename And Retrieve The Output From The Insert?
Aug 28, 2007We are trying to create a unique key from a table with indentity set in the table. We will have a number of these tables. Therefore, we will be creating a stored procedure and passing the table as a parameter. In this example we are setting the table.
When we run the the script, the output clause from the insert should give us a unique number from the given table in the temporary table. This example stores the output in a temporary table @tTemp.
How can you use a variable table name and retrieve the output from the Insert?
declare @tTestTable varchar (20)
set @tTestTable = 'mis.test_sequence'
--DECLARE @tTestTable TABLE ( sqVal [int] IDENTITY(1,1) NOT NULL, add_date datetime)
declare @testsql varchar (4000), @testseq int
DECLARE @tTemp table (mainpk int)
set @testsql = 'DECLARE @tTemp table (mainpk int) INSERT ' + @tTestTable + ' OUTPUT INSERTED.sqVal into @tTemp VALUES (getdate() ) SELECT @testseq=mainpk FROM @tTemp'
select @testsql
EXECUTE sp_executesql @testsql, N'@testseq int output,@tTemp table (mainpk int),@tTemp table (mainpk int) ',@tTemp,@tTemp,@testseq output,@tTemp
SELECT * FROM @tTemp
Please help
Thanks Tim.
Insert Stored Procedure With Output Parameter
Mar 2, 2007Hello everyone.
I need a stored procedure that excecutes a INSERT sentence.
That's easy. Now, what I need is to return a the key value of the just inserted record.
Someone does know how to do this?
Sql Insert, Capture Scope_Identity Output To Session Variable?
May 24, 2007What C# code would capture the Scope_Identity value (CoDeptRowID) output by the code below? Do I even need to capture it or is it already available as a C# variable CoDeptRowID ? I can't seem to get my hands on it!
SqlDataSource1.Insert();<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
InsertCommand="INSERT INTO [CompanyDepartment] ([User_Name], [FirstName], [LastName]) VALUES (@User_Name, @FirstName, @LastName);
SELECT @CoDeptRowID = SCOPE_IDENTITY()"
<insertparameters>
<asp:sessionparameter Name="User_Name" Type="String" SessionField ="LoginName"/>
<asp:controlparameter Name="FirstName" Type="String" ControlID="TextBox1" PropertyName ="text"/>
<asp:controlparameter Name="LastName" Type="String" ControlID ="TextBox2" PropertyName ="text"/>
<asp:Parameter Direction =Output Name ="CoDeptRowID" Type ="Int32" DefaultValue = "0" />
</insertparameters>
</asp:SqlDataSource>
T-SQL (SS2K8) :: Stored Procedure To Truncate And Insert Values In Table 1 And Update And Insert Values In Table 2
Apr 30, 2015table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
I Have Created A Table Table With Name As Varchar And Id As Int. Now I Have Started Inserting The Rows Like, Insert Into Table Values ('arun',20).
Jan 31, 2008I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?
Strange Problem: SQL Insert Statement Does Not Insert All The Fields Into Table From Asp.net C# Webpage
Apr 21, 2008An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
thanks
OPENROWSET (INSERT) Insert Error: Column Name Or Number Of Supplied Values Does Not Match Table Definition.
Mar 24, 2008Is there a way to avoid entering column names in the excel template for me to create an excel file froma dynamic excel using openrowset.
I have teh following code but it works fien when column names are given ahead of time.
If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match.
Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition.
here is my code...
SET @sql1='select * from table1'SET @sql2='select * from table2'
IF @File_Name = '' Select @fn = 'C:Test1.xls' ELSE Select @fn = 'C:' + @File_Name + '.xls' -- FileCopy command string formation SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn
-- FielCopy command execution through Shell Command EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT -- Mentioning the OLEDB Rpovider and excel destination filename set @provider = 'Microsoft.Jet.OLEDB.4.0' set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet1$]'') '+ @sql1 + '') exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet2$]'') '+ @sql2 + ' ')
Insert Command Fails When I Want To Insert Records In Data Table
Apr 20, 2008On my site users can register using ASP Membership Create user Wizard control.
I am also using the wizard control to design a simple question and answer form that logged in users have access to.
it has 2 questions including a text box for Q1 and dropdown list for Q2.
I have a table in my database called "Players" which has 3 Columns
UserId Primary Key of type Unique Identifyer
PlayerName Type String
PlayerGenre Type Sting
On completing the wizard and clicking the finish button, I want the data to be inserted into the SQl express Players table.
I am having problems getting this to work and keep getting exceptions.
Be very helpful if somebody could check the code and advise where the problem is??
<asp:Wizard ID="Wizard1" runat="server" BackColor="#F7F6F3"
BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px"
DisplaySideBar="False" Font-Names="Verdana" Font-Size="0.8em" Height="354px"
onfinishbuttonclick="Wizard1_FinishButtonClick" Width="631px">
<SideBarTemplate>
<asp:DataList ID="SideBarList" runat="server">
<ItemTemplate>
<asp:LinkButton ID="SideBarButton" runat="server" BorderWidth="0px"
Font-Names="Verdana" ForeColor="White"></asp:LinkButton>
</ItemTemplate>
<SelectedItemStyle Font-Bold="True" />
</asp:DataList>
</SideBarTemplate>
<StepStyle BackColor="#669999" BorderWidth="0px" ForeColor="#5D7B9D" />
<NavigationStyle VerticalAlign="Top" />
<WizardSteps>
<asp:WizardStep runat="server">
<table class="style1">
<tr>
<td class="style4">
A<span class="style6">Player Name</span></td>
<td class="style3">
<asp:TextBox ID="PlayerName" runat="server"></asp:TextBox>
</td>
<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="PlayerName" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="style5">
<td class="style3">
<asp:DropDownList ID="PlayerGenre" runat="server" Width="128px">
<asp:ListItem Value="-1">Select Genre</asp:ListItem>
<asp:ListItem>Male</asp:ListItem>
<asp:ListItem>Female</asp:ListItem>
</asp:DropDownList>
</td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
ControlToValidate="PlayerGenre" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
</tr>
</table>
Sql Data Source
<asp:SqlDataSource ID="InsertArtist1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [Playerst] ([UserId], [PlayerName], [PlayerGenre]) VALUES (@UserId, @PlayerName, @PlayerGenre)"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>">
<InsertParameters>
<asp:Parameter Name="UserId" Type="Object" />
<asp:Parameter Name="PlayerName" Type="String" />
<asp:Parameter Name="PlayerGenre" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
</asp:WizardStep>
Event Handler
To match the answers to the user I get the UserId and insert this into the database to.protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e)
{
SqlDataSource DataSource = (SqlDataSource)Wizard1.FindControl("InsertArtist1");
MembershipUser myUser = Membership.GetUser(this.User.Identity.Name);
Guid UserId = (Guid)myUser.ProviderUserKey;String Gender = ((DropDownList)Wizard1.FindControl("PlayerGenre")).SelectedValue;
DataSource.InsertParameters.Add("UserId", UserId.ToString());DataSource.InsertParameters.Add("PlayerGenre", Gender.ToString());
DataSource.Insert();
}
How To Insert Data From A File Into Table Having Two Columns-BULK INSERT
Oct 12, 2007
Hi,
i have a file which consists data as below,
3
123||
456||
789||
Iam reading file using bulk insert and iam inserting these phone numbers into table having one column as below.
BULK INSERT TABLE_NAME FROM 'FILE_PATH'
WITH (KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '||')
but i want to insert the data into table having two columns. if iam trying to insert the data into table having two columns its not inserting.
can anyone help me how to do this?
Thanks,
-Badri
SQL Server 2008 :: Insert From Table 1 To Table 2 Only If Record Doesn't Exist In Table 2?
Jul 24, 2015I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query
INSERT INTO vacationaccrual
(empno,
accrued_vacation,
accrued_sick_effective_date,
accrued_sick,
import_date)
[Code] ....
Default Table Owner Using CREATE TABLE, INSERT, SELECT && DROP TABLE
Nov 21, 2006For 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 RelatedWants To Insert Into Multiple Table By A Single Insert Query
Apr 11, 2008Hi,ALL
I wants to insert data into multiple table within a single insert query
Thanks
Multiple Insert Call For A Table Having Insert Trigger
Mar 1, 2004Hi
I am trying to use multiple insert for a table T1 to add multiple rows.
Ti has trigger for insert to add or update multiple rows in Table T2.
When I provide multiple insert SQL then only first insert works while rest insert statements does not work
Anybody have any idea about why only one insert works for T1
Thanks
Insert To Recordset Gives Different Date Format To Table Insert ????????
Jun 28, 2007Help please!
I have an asp page with some simple vbscript to add a record to a table, the record has a datefield (dob).
the insert results in a US formated date if I add a record to a dynamic recordset but a UK formated date if I insert direct to the table ?????
i.e.
if request("dob") is "01/11/2007" (1st november 2007)
set conn = server.createobject("adodb.connection")
set rs = server.createobject("adodb.recordset")
rs.open "tez", mc, 2, 2 rs.addnew
rs("dob") = request("dob")
rs.update
11 jan 2007 stored in table
while
set trs = Server.CreateObject("ADODB.RecordSet")
qfn= "insert tez values('"+request("dob")+"')"
trs.Open qfn,mc
results in
1 november 2007 is written to the table.
Both of these methods are used in the same asp page.
This is on a windows2003 server, sql2005,iisv6, asp.netv2
I have tried every setting I can find in iis,asp,sql server to no avail.
I need the recordset method to work correctly.
Terry
Table Output
Jul 4, 2006Hi everyone,
While creating a stored prodecure, I now that it is possible to use an output parameter which is a table. However, I do not know how to do it ??
In other words, I would like to return a table but as an output parameter, so how can this happen ?
OUTPUT On Table With A Trigger
Sep 7, 2006First some background info.
SQL Express
Visual Studio 2005
VB.Net
Data.SQLClient Namespace
Well I was using the OUTPUT option in my program and it was great, i was returning my PrimaryKey from the newly inserted row. (I am using paramaters in my program and running a sqlCommand.ExecuteScalar)
--Example Insert
"Insert into tblInfo (fldInfo,fldDate) OUTPUT inserted.fldInfoID values (@fldInfo, @fldDate)"
Then I found a problem...:(
I added trigger to the tblInfo, that after a Insert,Update, Delete it would perform a audit on the table saving the old info that was overritten, type of action taken (insert, update or delete), and who did it.
Well my OUTPUT didn't like that says, it can't do a OUTPUT on a table with a trigger.
--Error msg
Msg 334, Level 16, State 1, Line 1
The target table 'tblInfo' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.
So what am i supose to do? I want the audit to occur, but I walso want the primarykey without doing a select (maxID) which could potentially get the wrong id (multiuser).
Any suggestions?
Thanks,
Jordon
Why Is Table DDL Output In Error?
Apr 11, 2007I used the oTable.Script method to output the DDL for a Sql Server 2000 user defined table. The result is DDL with an error. I don't think the problem is with DMO itself so I posted this here. Note the 'TEXTIMAGE_ON ' clause. The table does not have a text column, and the DDL will not execute.
CREATE TABLE [MyTable] (
[FilterID] [int] IDENTITY (1, 1) NOT NULL ,
[LoanAgentID] [int] NOT NULL ,
[Key] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IsActive] [int] NOT NULL ,
[tStamp] [timestamp] NOT NULL ,
[Formula] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[_AuditAddDt] [datetime] NOT NULL ,
[_AuditUpdateDt] [datetime] NOT NULL ,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[FilterID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Server: Msg 1709, Level 16, State 1, Line 1
Cannot use TEXTIMAGE_ON when a table has no text, ntext, or image columns.
Any ideas?
Thanks!
How To Output Data To A New Table
Jul 10, 2006Hi
I run a SQL query to select a few colums of data using a select statement, I want the output to be stored in the new table which can be defined in the SQL statement how can I do it?
Your helpwill bw highly appreciated.
Output Table As Variable
May 14, 2007Hi,
I've been looking into using the ability to select the table of an OLEDB destination task based on a package variable but would like to know if this could be utilised in the following example.
I have a CSV file (potentially there are many) with a number of rows. The rows do not necessarily contain the same number of elements. Each row has an EventID and this is used to determine the database table that the row is to be inserted in.
Initially i thought about using a conditional split to send each row down the path relating to its event ID. There would be multiple destinations depending on the table. With the possibility of 60 to 70 different event ID's this is very quickly going to get out of hand in the designer. I'd like to know if it would be possible to set up a case statement (or similar) in a script component and specify the table name as a variable depending on the event ID. I'd then use this table variable to set the destination task's table property.
Does this sound like a possibility or do i have to go down the route of multiple paths?
Many thanks in advance,
Grant
Output Of A Stored Proc Into A Table
Sep 2, 2004Want to obtain the outpur of a xp_cmdshell (or any other procedure call) command to a table. Is it possible ?
View 6 Replies View RelatedOutput Table Content To A Textfile (was Need Help On SQL!!!)
Nov 30, 2004Hi.. anyone know wat the syntax to output the table content to a textfile? and copy back the content on the textfile back to the table?
Must use SQL command for MS SQL Server 2000 cannot use manual.. tks!
Create Table From Query Output
May 5, 2008I have a query that is spliting up FullName into FistName and LastName columns. Need help writing a query that dump that output into a new table.
Thanks
Kevin
Output Duplicate Row Occurrences In A Table
Aug 18, 2013I want to retrieve staff who attend less than 80% for a meeting type assuming we have 10 meetings per list.
Meeting Table:
staffID list date
------------------------
1 A 2013-01-15
2 B 2013-01-17
1 B 2013-01-17
1 A 2013-01-18
2 B 2013-01-19
1 A 2013-01-20
2 C 2013-01-21
* 1 - Dan
* 2 - Jane
When the staffID occur 3 times (70%), query will Output:
staffName list Participation%
------------------------
Dan A 70
SELECT a.staffName, b.list,
(100 - ((COUNT(c.staffID) * 100) / 10)) AS 'Participation%'
from Staff AS a, listType AS b, Meeting AS c
where a.staffID = c.staffID AND
b.list = c.list
GROUP BY a.staffName, b.list
HAVING COUNT(c.staffID) > 2
Create Table From Query Output
Oct 25, 2013I have the following sql query in vb.net and ms access , how do I create a table from the query result ?
SELECT 'Table1' AS [Table], SUM(a) - SUM(b) AS Result FROM table1
I have tried but it does not work
create table tble10 as SELECT 'Table1' AS [Table], SUM(a) - SUM(b) AS Result FROM table1
Table Corruption - DBCC Output
Dec 14, 2005Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1513928615, index ID 0: Page (3:33709) could not be processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 1513928615, index ID 0, page (3:33709), row 0. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 95 and 30.
DBCC results for 'Result'.
There are 41589306 rows in 660667 pages for object 'Result'.
CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'Result' (object ID 1513928615).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (LASAR2.dbo.Result ).