I've been looking online and cannot find any help / resources with this so I brought it here :D
I'm looking for help in creating a Cursor (this will be inside a SP) that will loop through the records of a "Table" (Temporary or Retrieved) and for each row that is looped through I can use it's values to do inserts against a few other tables.
Any resources / help would be great! I work best by example.
Hi,I would have used the aspnet membership tool to auto-create all the ASP.NET membership tables. However, the hosting company don't allow remote connections which meant I had to create the tables by hand, scripting the tables using script to CREATE using management studio.However, I noticed one of the tables has data without any users: aspnet_SchemaVersions, which causes an error when trying to log onto my site.The fix is to make sure the table has the 4-5 rows of data in it (which is missing off the live server). Its just a few rows of data, but I want to script the inserts for each row so I don't have to type them in using myLittleAdmin (the host's web version of management studio). Can anyone point me in the right direction?
View 3 Replies View RelatedI want to insert a row for a Global user in Table 'A' whenever ADO.Net code inserts a Local user row into same table. I recommended using a trigger to implement this functionality, but the DBA was against it, saying that stored proecedures should be used, since triggers are unreliable and slow down the system by placing unecessary locks on the table. Is this true OR the DBA is saying something wrong? My thinking is that Microsoft will never include triggers if they are unreliable and the DBA is just wanting to offload the extra DBA task of triggers to the programmer so that a stored procedure is getting called, so he has less headache on his hands.Thanks
View 2 Replies View RelatedHere are two tables:
TABLE_A
[ac] [dest]
1 A
1
1
2
2
3
3 B
3
(ac=1, dest=A; ac=3, dest=B)
The space between '1' and 'A' isn't showing up correctly.
TABLE_B
[fleet] [ron]
1 A
1 A
1 A
1 A
1 A
2
2
2
2
3 B
3 B
(fleet=1, ron=A; fleet=3, ron=B; etc.etc.)
I would like to fill TABLE_B's column "ron" by referring to TABLE_A's column "dest".ac = fleet, and for each ac, I would like to loop through the 'dest' column in TABLE_A from top to bottom to get the top most value. If there are no values (like with ac = 2), then value is blank.
For 1, the value is A.
For 2, the value is blank.
For 3, the value is B.
Therefore, in TABLE_B, for all 'ron' for fleet=1, the value is filled A.
For all 'ron' for fleet=2, the value is blank.
For all 'ron' for fleet=3, the value is B.
- create variable 'v'
- where ac=fleet, loop through 'dest' from top to bottom to get top-most value. variable 'v' = the value.
- in TABLE_B, for each ac=fleet, insert variable 'v' into the 'ron' column
Hello, Its hard trying to explain this.
I have 3 tables
Table 1 is where the users are stored, each user has a username and a userrank
Table 2 is where the points that decides the userrank are stored
Table 3 contains the available userranks like this
Table 1 (user_list) looks briefly like this:username nvarchar(20),userrank int, -- Reference to Table3 id... alot more fields
Table 2 (settings_profile) looks like this:username nvarchar(20),total_active_points int,... some more fields
Table 3 (data_ranks) looks like this:id int primary key auto inc,rankname nvarchar(20),min_pts int,max_pts int
Points get added to table 2 whenever they do something that generates points on the site. Points also get withdrawn every 7 days, so a user can only collect points for 7 days, on the 8th day, all points he earned on the 1st day is reduced from the current points with this code:
WHILE (SELECT @username = username, @id = id, @temp1 = ap_sentmails, @temp2 = ap_createdthreads, @temp3 = ap_createdanswers, @temp4 = ap_signguestbook, @temp5 = ap_blogcomment, @temp6 = ap_createblogentry, @temp7 = ap_profilefirsttime, @temp8 = ap_profilephoto, @temp9 = ap_activateguestbook, @temp10 = ap_addnewfriend, @temp11 = ap_superguruvote, @temp12 = ap_forumtopicvote, @temp13 = ap_labervote, @temp14 = ap_funstuffitemvote, @temp15 = ap_movievote, @temp16 = ap_actorvote, @temp17 = ap_money_new WHERE (created < Dateadd(dd, -7, @todaysdate))BEGINSET @sum = 0SET @sum = @temp1 + @temp2 + @temp3 + @temp4 + @temp5 + @temp6 + @temp7 + @temp8 + @temp9 + @temp10 + @temp11 + @temp12 + @temp13 + @temp14 + @temp15 + @temp16 + @temp17UPDATE settings_profile SET total_active_points = total_active_points - @sum WHERE (username = @username)DELETE FROM konto_daylist WHERE (id = @id)END
Now my question is this, i want to loop thru the table A, collect all usernames inside of it, then run it against table b and table c to determine the current rank of the user.Something like this...
DECLARE @username nvarchar(20)DECLARE @pts int, @rank int
...something that starts a loop thru table A (user_list) and get the username into @username...
SELECT @pts = total_active_points FROM settings_profile WHERE (username = @username)-- Determine the rank here, by compairing the points the user have against the pointstabel in table data_ranksSELECT @rank = id FROM data_ranks WHERE (pts_min => @pts AND pts_max < @pts)UPDATE user_list SET rank = @rank WHERE (username = @username)
...next persion in the loop...
This SP runs once a day and will first reduce the points from 8days ago, then it will run thru all the users and determine their new rank...
But how do i loop thru all the users? with a cursor?
Hello,
I am a little new at T-SQL. and I could use a suggestion on the best way to accomplish this task. I have an application where I must loop through a table in Microsoft SQL Server 2005, find the records that match and break them up into groups where the value of the Thickness field is in groups of ( <=8).
Lets say I have 100 records and the table contains sacks marked A, B, C, and D. Within Sack A is 6 records. Each of the 6 records has a thickness column with a value between 0.5 to 1.0 (my example shows 2) I have a column called bundle that I must update to show a 1 for the first group of (<=8) inches thick a 2 for the second group (<=8) inches thick and so on. Then repeat the process for sacks B, C and D.
The table itself has the following
Order Number |Zipcode |Sack(A, B, C,or D)| Thickness |Bundle|
1 |19809 |A | 2 |NULL |
2 |19809 |A | 2 |NULL
3 |19809 |A | 2 |NULL
4 |19809 |A | 2 |NULL
5 |19809 |A | 2 |NULL
6 |19809 |A | 2 |NULL |
7 |19721 |B | 4 |NULL |
8 |19721 |B | 3 |NULL |
9 |19721 |B | 2 |NULL |
10 |19721 |B | 5 |NULL |
11 |19721 |B | 2 |NULL |
12 |19721 |B | 2 |NULL |
What I need to do is sum the thickness until it gets to less than 8 and update bundle with a 1. The result would be 4 records and the table would then look like this:
Order Number |Zipcode | Sack(A, B, C,or D)| Thickness |Bundle|
1 |19809 |A | 2 |1 |
2 |19809 |A | 2 |1
3 |19809 |A | 2 |1
4 |19809 |A | 2 |1
5 |19809 |A | 2 |NULL
6 |19809 |A | 2 |NULL |
7 |19721 |B | 4 |NULL |
8 |19721 |B | 3 |NULL |
9 |19721 |B | 2 |NULL |
10 |19721 |B | 5 |NULL |
11 |19721 |B | 2 |NULL |
12 |19721 |B | 2 |NULL |
Notice there are two records that are not bundles for sack A. I need to place them in a separate bundle called 2. The result will be as follows
Order Number |Zipcode | Sack(A, B, C,or D)| Thickness |Bundle|
1 |19809 |A | 2 |1 |
2 |19809 |A | 2 |1
3 |19809 |A | 2 |1
4 |19809 |A | 2 |1
5 |19809 |A | 2 |2
6 |19809 |A | 2 |2 |
7 |19721 |B | 4 |NULL|
8 |19721 |B | 3 |NULL
9 |19721 |B | 2 |NULL
10 |19721 |B | 5 |NULL |
11 |19721 |B | 2 |NULL |
12 |19721 |B | 2 |NULL |
Now Sack A is complete so I must move on to Sack B. The first two records total to 7 and adding another record would move the thickness past 8 so I must update the bundle for only the first two records with a 1. The next two total to seven also and the following 2 total to four. We must set those bundles to 1, 2, 3 as follows
1 |19809 |A | 2 |1 |
2 |19809 |A | 2 |1
3 |19809 |A | 2 |1
4 |19809 |A | 2 |1
5 |19809 |A | 2 |2
6 |19809 |A | 2 |2 |
7 |19721 |B | 4 |1 |
8 |19721 |B | 3 |1 |
9 |19721 |B | 2 |2 |
10 |19721 |B | 5 |2 |
11 |19721 |B | 2 |3 |
12 |19721 |B | 2 |3|
These scenarios just keep repeating until the last record
Thank you for any help you can give me
The process of adding a column with DEFAULT (0) to a table that has 15million records takes a despicable amount of time (too much time) and the transaction log of the database grew to an unacceptable size. I would like to accomplish the same task using this procedure:
·Add the column to the table with null value.
·Loop through the table (500000 records at a time) and SET the value in the newly added column to 0.
·Issue a commit statement after each batch
·Issue a checkpoint statement after each batch.
·Alter the table and SET the column to NOT Null DEFAULT (0)
Here is my Sample script
ALTER TABLE EMPLOYEE ADD EZEVALUE NUMERIC (9,6) NULL
Go
Loop
UPDATE EMPLOYEE SET EZEVALUE = 0
Commit Tan
CHECKPOINT
END (Repeat the loop until the rows in EMPLOYEE have the value 0)
Go
ALTER TABLE EMPLOYEE ALTER COLUMN EZEVALUE NUMERIC (9,6) NOT NULL DEFAULT (0)
My problem is with the loop section of this script. How do I structure the loop section of this script to loop through the employee table and update the EZEVALUE column 500000 rows at a time, issue a Commit Tran and a CHECKPOINT statement until the whole table has been updated. Does anyone out there know how to accomplish this task? Any information would be greatly appreciated.
Thanks in advance
Hi,
If i have an SP called mySP that accepts one parameter @param
If I have a table of paramaters with only one column like this:
Param1
Param2
..
ParamN
How do I do if I want to execute the SP on all the table fields:
some thing like this:
Exec my SP 'Param1'
Exec mySP 'Param2'
...
Exec mySP 'ParamN'
I want that automatically since the parameters are going to be in a table called myTblParams
Notice that I don t want to pass all the parameters to the SP just once but only one value each time I execute the SP since mySP ccepts only one parameter.
Thanks a lot for guidelines
I am starting to create a new database table based on an existing dbtable. My existing table has a list of ID's and a date range for each ID.
For example:
TableSource
ID Start DateTime End datetime
Y10012 01-12-12 13:00:00 01-19-12 13:00:00
So for this ID, I need my SQL statement to read this table, then create a new table and insert a new row for every second starting from the start date to the end date. I have several id's that span a week at a time. So I am expecting millions and millions of records once I am done.
End Result:
MainTable
IDFullTime
Y1001201-12-12 13:00:00
Y1001201-12-12 13:00:01
Y1001201-12-12 13:00:02
Y1001201-12-12 13:00:03
....
Y10012 01-12-19 13:00:00
Then once it completes reading S1001, it moves on to the next ID and appends it to the table. The date ranges are different for each ID- so it can't be hard coded.
I have a large table that looks like this.
(ID INT NOT NULL IDENTITY(1,1),PK INT , pocket VARCHAR(10))
1, 1, p12, 1, p23, 2, p34, 2, p45, 3, p56, 3, p67, 4, p78, 5, p19, 5, p210,5, p83
i would like to loop through the table and concatenate the pocket filed for all the records that has the same pk. and insert the pk and the concatenated string into another table in a timely manner.
can anyone help?
i have to use temporary tables. (not cursors-with cursors i know how to di it, but i want with temporary table)
thanks in advance
I have a table (temp_Order) wherein I use to insert a temporary order... after inserting the temporary order, when the buyer submits the order,I want to loop through the table and fetch the orders made by the buyer and insert those products on another table (Order)...how can I achieve this? im currently using sqlDataSource...
View 2 Replies View RelatedHello all. I've got a problem with really slow INSERTs on one (and only one) of the tables in a database. For example, using SQL Management Studio, it takes 4 minutes and 48 seconds to insert 25 rows. There are only about 8 columns in the table and only about 1500 records. All the other tables in the database are very fast for inserts.
Another odd thing uniquely associated with INSERTs on this table: prior to inserting the 25 new rows of data, SQL Management Studio tells me that it inserted 463 rows of data which I know did not happen. Here's the INSERT statement:
INSERT INTO FieldOps(StudySiteID
, QA_StructureID
, Notes
, PersonID)
SELECT DISTINCT StudySiteKey
, QA_StructureKey
, SampleComments1
, '25'
FROM ScriptOutput_Nitrate
WHERE (ScriptOutput_Nitrate.StudySiteKey IS NOT NULL)
and SQL Management Studio (eventually) says:
(463 row(s) affected)
(463 row(s) affected)
(25 row(s) affected)
The table has an index on the primary key (INT data type with auto increment). I tried running the following code to fix things but it made no difference:
USE [master]
GO
ALTER DATABASE [FieldData] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
use FieldData
GO
DBCC CHECKTABLE ('FieldOps', REPAIR_REBUILD) With ALL_ERRORMSGS
GO
USE [master]
GO
ALTER DATABASE [FieldData] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
I'm guessing that the problem might be related to the index (??). I don't know... Does anyone here have a suggestion as to what I should do to fix this problem.
Dear All,
I developed an application years ago where I have to insert records in multiple table from asp.net page during registration process with one button click. I have seperate Insert statements and then i have to select Identity column value and insert into another table as a foriegn key and so on.
I want to run all these SQL insert in one statement or Stored procedure. becuase currently, if one of insert statemetn fails, I have no way to roll back previous inserts and start over again.
Any advice or sample T-SQL Where I can insert in Table A, and then read the identity from Table A and insert in Table B, in one single Transaction, so if it fails at end of Transaction, i want to roll back the all the inserts and updates in table(s).
Thanks,
Hi,
I have data in an old database I would like to capture for my new
system but I dont have the original insert scripts. Is there a
tool (in SQL Server 2000 or thirdparty) that will help me export the
data as SQL inserts?
Thanks
jr.
Hi,
I am working on an application to analyse down time on a production line system. The system has about 40 rows inserted per minute. The inserts are coming from about 10 different stations.
I need to a analyse the downtime between each insert from each station. The plan is to copy the data to another database on a different server so as not to affect the live system that is being updated by the production line.
However the initial requirement was to do this at night while the production line was down but now they want the data to be updated every 3 hours which means performing this huge query while the production line is bombarding the DB with inserts.
I am wondering what is the best way of doing this. Is there any way I can limit the abount of processor this proceedure will take.
Any advice appreciated,
Thanks,
Sean
I have a table into which the inserts will be done by multiple users at the same time;
The table has a primary key, non clustered, on unique id; each insert will have unique id, which is a seqence generated number, so they are always different for different transactions.
Is this possible to set the settings of sql server in such a way, that these inserts are done at the same time i.e. during insert by one user that table is not locked so that other inserts can take place at the same time as well?
I need to create a table that only allows records to be inserted or deleted. Once the record has been created it can only be deleted. Is there anyway to configure a table in this manner?
Table Definition
USE [DB_AUTOMATED_PACKAGING_SYSTEM]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TBL_PCL_LENS_DATA](
[SerialNumber] [varchar](50) NOT NULL,
[ProcessedDate] [datetime] NOT NULL,
[Filename] [varchar](50) NOT NULL,
[CartonLabelImage] [image] NOT NULL,
[ExpirationDateLabelImage] [image] NOT NULL,
[LabelSetLabelImage] [image] NOT NULL,
[ReplyCardLabelImage] [image] NOT NULL,
[TextFile] [ntext] NOT NULL,
CONSTRAINT [PK_TBL_PCL_LENS_DATA] PRIMARY KEY CLUSTERED
(
[SerialNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
I have been wrestling with the code all day to accomplish the following: I need to update a table based on values from another table. So far, I have been able to do the below:
DECLARE @LookUpTerm VARCHAR(25)
, @SearchCol VARCHAR(255)
, @LogonIDToProcess VARCHAR(50)
, @Matched CHAR
, @Cycle INT = 1
IF OBJECT_ID('tempdb..#Glossary','U') IS NOT NULL DROP TABLE #Glossary
IF OBJECT_ID('tempdb..#Employees','U') IS NOT NULL DROP TABLE #Employees
[code]...
Problem:
Two tables t1 and t2 have the same schema but exist on two different servers. Which is the better technique for updating t2 and why?
/****** Object: Table [dbo].[t1] Script Date: 9/6/2007 9:55:21 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[t1]
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[t1] (
k [int] IDENTITY (1, 1) NOT NULL ,
a [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
b [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
c [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
x [int] NULL ,
y [int] NULL ,
amt [money] NULL
) ON [PRIMARY]
END
GO
/****** Object: Table [dbo].[t2] Script Date: 9/6/2007 9:55:44 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[t2]
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[t2] (
k [int] IDENTITY (1, 1) NOT NULL ,
a [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
b [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
c [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
x [int] NULL ,
y [int] NULL ,
amt [money] NULL
) ON [PRIMARY]
END
GO
-- Technique 1:
set identity_insert t2 on
insert into t2 (k,a,b,c,x,y,amt)
select k,a,b,c,x,y,amt from t1
where not exists (select k from t2 where t1.k = t2.k)
set identity_insert t2 off
update t2
set a = t1.a,
b = t1.b,
c = t1.c,
x = t1.x,
y = t1.y,
amt = t1.amt
from t1
where t1.k = t2.k
-- Technique 2:
set identity_insert t2 on
declare t1_cur cursor for
select k,a,b,c,x,y,amt from t1
for read only
open t1_cur
declare @k int
declare @a char(10)
declare @b char(10)
declare @c char(10)
declare @x int
declare @y int
declare @amt money
fetch next from t1_cur into @k,@a,@b,@c,@x,@y,@amt
while(@@FETCH_STATUS = 0)
begin
if exists(select k from t2 where k = @k)
begin
update t2
set a = @a, b = @b, c = @c, x = @x, y = @y, amt = @amt
where (k = @k)
end
else
begin
insert into t2 (k,a,b,c,x,y,amt) values(@k,@a,@b,@c,@x,@y,@amt)
end
fetch next from t1_cur into @k,@a,@b,@c,@x,@y,@amt
end
close t1_cur
deallocate t1_cur
set identity_insert t2 off
Thanks,
Joel K
Database Adminstration/Application Development
I have a situation here. Please
advice me on this.
I have a master table and a child
table. They have a PK and FK relationship. The master table has an identity
column with auto increment set to true. This map as a FK in the child table. My
questions are:
Can I have a single form to insert a new record into
master and child table at the same time?This has to be accomplished without stored
procedures. Can it be done?Is it possible to do this with a single insert query?
If yes, can it be done with sql data source or
dataset or tableadapters’?Please point me towards appropriate link for doing
so.
Thank you.
I am currently using SQL Server
2005 and VS 2005.
I am using vwde2008 and created db and table. i want to create a stored procedure that will insert values. I am bit lost on how write this.
this is my table info
table = BusinessInfo
Columns = BusinessID, BusinessName, BusinessAddress
how can i create a stored procedure ?
I am looking for pros and cons for the following scenarios:
When a table contains a unique key constraint is it viable to always do an insert and immediately check the @@ERROR value and if @@ERROR states a duplicate key exception then perform an update statement?
Another possible solution would be to always check if the key exists and then do the insert / update based upon that result. This method will always require two steps.
I have a master table containing details of over 800000 surveys made up of approximately 400 distinct document names and versions. Each document can have as few as 10 questions but as many as 150. Each question represents one row.
My challenge is to create a separate spreadsheet for each of the 400 distinct document names and versions containing all the rows and columns present in the master table. The largest number of rows would be around 150 and therefore each spreadsheet will not be very big.
e.g. in my sample data below, i will need to create individual Excel files named as follows . . .
"Document1Version1.xlsx" containing all the column names and 6 rows for the 6 questions relating to Document 1 version 1
"Document1Version2.xlsx" containing all the column names and 8 rows for the 8 questions relating to Document 1 version 2
"Document2Version1.xlsx" containing all the column names and 4 rows for the 4 questions relating to Document 2 version 1
I assume that one of the first things is to create a lookup of the distinct document names and versions assign some variables and then use this lookup to loop through and sequentially filter the master table data ready for creating the individual Excel files.
--CREATE TEMP TABLE FOR EXAMPLE
IF OBJECT_ID('tempdb..#excelTest') IS NOT NULL DROP TABLE #excelTest
CREATE TABLE #excelTest (
[rowID] [nvarchar](10) NULL,
[docName] [nvarchar](50) NULL,
[Code] .....
--Output
rowIDdocNamedocVersionquestionblankField
1document11q1NULL
2document11q2NULL
3document11q3NULL
4document11q4NULL
5document11q5NULL
6document11q6NULL
[Code] .....
Hopefully someone can at least point me in the right direction for moreresearch (e.g.: correct terminology). My only previous experience was justdumping data into a database using ODBC, and that was some years ago so nowmostly forgotten.I need to write an NT Service/Application (in C/C++) that will be gettingdata sent to it via SQL Server 2000. The data will arrive in my SQL Server(read-only access), via replication of tables from another remote SQLServer.My application needs know when new row are inserted, or updated so it can toread this data (needs to be quick/timely so hopefully no polling) to theninterface with other remote proprietary systems.T.I.A.PS: If you can recommend appropriate books on SQL Server 2000 that wouldalso be useful.
View 2 Replies View RelatedHello,Can someone point me to getting the total number of inserts and updates on a tableover a period of time?I just want to measure the insert and update activity on the tables.Thanks.- Vish
View 3 Replies View Related
Hi
I have a SSIS package that imports data into a staging table from an excel sheet (This works fine). From the staging tabler i want it to insert the values into my members table, take that unique indentityID that gets created and insert the other values into other tables for that member that was just created.
In the staging table, i have all the values for a single member. But the structure of the database needs all the values inserted into seperate tables. There is no conditions ID in my members table, so the member first has to be created and from there i need to use the newly created member's MemberID and insert the conditions into a seperate table using the MemberID
I have created some sample data that can be used. I think i have an idea of how to do it, but i'm not totally sure if it will work that way, i have however included it in the sample data.
Code Snippet
DECLARE @ImportedStagingData TABLE
(
ID INT IDENTITY(1,1),
Name VARCHAR(50),
Surname VARCHAR(50),
Email VARCHAR(50),
[Chronic Heart Failure] INT,
[Colon Cancer] INT
)
INSERT INTO @ImportedStagingData VALUES ('Carel', 'Greaves', 'CarelG@Email.com', 1,0)
INSERT INTO @ImportedStagingData VALUES ('Jamie', 'Jameson', 'JamieJ@Email.com', 1,1)
INSERT INTO @ImportedStagingData VALUES ('Sarah', 'Bolls', 'SarahB@Email.com', 0,1)
INSERT INTO @ImportedStagingData VALUES ('Bells', 'Scotch', 'BellsS@Email.com', 1,1)
INSERT INTO @ImportedStagingData VALUES ('Stroh', 'Rum', 'StrohR@Email.com', 0,0)
DECLARE @Conditions TABLE
(
ID INT IDENTITY(1,1),
Condition VARCHAR(50)
)
INSERT INTO @Conditions VALUES ('Chronic Heart Failure')
INSERT INTO @Conditions VALUES ('Colon Cancer')
DECLARE @Members TABLE
(
MemberID INT IDENTITY(1,1),
Name VARCHAR(50),
Surname VARCHAR(50),
Email VARCHAR(50)
)
DECLARE @memConditions TABLE
(
MemberID INT,
ConditionID INT
)
SELECT * FROM @ImportedStagingData
SELECT * FROM @Conditions
SELECT * FROM @Members
SELECT * FROM @memConditions
/* --- This is the part that i am battling with ---
DECLARE @CurrentValue INT
DECLARE @numValues INT
SET @numValues = (SELECT COUNT(ID) FROM @ImportedStagingData)
WHILE @numValues <> 0
BEGIN
INSERT INTO @Members
SELECT Name, surname, email
FROM @ImportedStagingData
GO
SET @CurrentValue = (SELECT IDENT_CURRENT('@ImportedStagingData'))
INSERT INTO @memConditions (MemberID), (ConditionID)
VALUES (@CurrentValue, --ConditionValue from @ImportedStagingData, all the values that have a 1)
@numValues = @numValues - 1
END
END
*/
All help will be greatly appreciated.
Kind Regards
Carel Greaves
I have a Problem with my SQL Statement.I try to insert different Columns from different Tables into one new Table. Unfortunately my Statement doesn't do this.
If object_ID(N'Bezeichnungen') is not NULL
Drop table Bezeichnungen;
GO
create table Bezeichnungen
(
Artikelnummer nvarchar(18),
Artikelbezeichnung nvarchar(80),
Artikelgruppe nvarchar(13),
[code]...
I am trying to create a trigger on a table. Let's call it table ABC. Table looks like this:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ABC](
[id] [uniqueidentifier] NOT NULL,
[Code] ....
When someone updates a row on table ABC, I want to insert the original values along with the current date and time getdate() into table ABCD with the current date and time into the updateDate field as defined below:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ABCD](
[id] [uniqueidentifier] NOT NULL,
[Code] .....
The trigger I've currently written looks like this:
/****** Object: Trigger [dbo].[ABC_trigger] Script Date: 4/10/2015 1:32:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[ABC_trigger] ON [dbo].[ABC]
[Code] ...
This trigger works, but it inserts all of the rows every time. My question is how can I get the trigger to just insert the last row updated?
I can't sort by uniqueidentifier in descending as those can be random.
will my fetches on a cursor continue seamlessly even if deletes and multiple commits are occuring on the underlying source table? I want to make sure the cursor will not lose it's place as I believe some older dbms's did.
View 11 Replies View RelatedI have this table
CREATE TABLE [Sales].[Test_inmem]
(
[c1] [int] NOT NULL,
[c2] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ModifiedDate] [datetime2](7) NOT NULL CONSTRAINT [IMDF_Test_ModifiedDate] DEFAULT (sysdatetime()),
[Code] ....
I have to generate 1000000 random records into it. I tried various ways to insert records, but not being a developer could not do it. I hope to make the C1 as a serial number, C2 can be anything, C3 I want to be the timestamp.
Assuming I should be using values from temp inserted to insure correct record...
Need help coding IF...THEN INSERT statements in following After TRIGGER:
Create TRIGGER trg_insertItemRows
ON dbo.a_form
AFTER INSERT
AS
SET NOCOUNT ON
-- Checkbox Driven:
IF a_form.missingCheckbox = -1 THEN
Insert into b_items (form_ID, parent_ID, ItemTitle)
Values (Select Distinct i.form_ID,i.parent_ID from inserted i)', '+ 'User checked Missing Data')
-- Textbox Driven:
IF a_form.incorrectTxtbox <> 'na' THEN
Insert into b_items (form_ID, parent_ID, ItemTitle)
Values (Select Distinct i.form_ID,i.parent_ID from inserted i)', '+ Correction: Replace '+ incorrectTxtbox + ' with '+replaceWithTxtbox)
Sample code below:
-- Source table the Trigger acts on
Create Table a_form (
form_ID int Not Null,
parent_ID int,
missingCheckbox bit,
missingNote varchar(100),
incorrectTxtbox varchar(50),
replaceWithTxtbox varchar(50)
)
--Target table Trigger inserts into
Create Table b_items (
items_ID int Not Null,
form_ID int Not Null,
parent_ID int,
ItemTitle varchar(150)
)
I need a script that inserts the data of an excel sheet into a table. If something already exists it should leave it, unless it's edited in the excel sheet and so on and so on. This proces has to go through a stored procedure... ...But how?
View 6 Replies View RelatedIm having a issue. Im not sure how I am going to carry out but I have two tables in SQL server 2005
TABLES
Category SubCategory (PK)CategoryName (PK) SubCategoryNameCategoryID SubCategoryIDDate Date (Just shows the date inserted) (FK)CategoryID
On the front page, I need to have it querys out the CategoryName from Categorys but also querys out all....Well not all but atleast 5 subcategorys that relate to that categoryName. Once its down it moves to the next category and does the same and so on. Does anyone know the trick ?