Wednesday, September 18, 2013

[SQL Server] How to insert in a 3 table

[SQL Server] How to insert in a 3 table

How to insert in a 3 table

Posted: 18 Sep 2013 02:14 PM PDT

I Have 3 tables....namely Bio, Sex, Status*BioBioIDFirsNameMiddleNameLastNameSexIDStatusID*SexSexIDSex(Male or Female)*StatusStatusIDStatus(Single, In Relationship or Married)here is my question...How can i insert in Table Bio in such a way that the SexID and StatusID would also be insert a data???

Inserting into two tables - little help needed

Posted: 18 Sep 2013 04:24 AM PDT

Hello all.First of all, I wanted to [b]thank [/b]everyone for participating in these forums and helping those who need help. Like me! I know you're taking time out of your day to help others. Sometimes, you just can't get the answer from a book or more importantly, some feedback!I am writing a SP which will take data from an old table (tens of thousands of records) and insert it into two related tables. I'm still early int he writing and testing phase so the code below is an early draft and a mock up.I have a table which contains basic customer information and account numbers. As a precautionary measure, I plan to take the account numbers out of the new table and store them in a separate table. So my old table may look like this:OldCustomers-------------OldCustomerIDOldAccountNumFullNameAddressAnd my two new tables may look like this:NewCustomers------------------NewCustomerIDAccountID (FK)FullNameAddressAccounts----------------AccountIDAccountNumberSo my LOOP logic is to:*Grab the first Account # from OldCustomers and insert into the Accounts Table.*Grab the ID from Accounts that was just created.*Insert the rest of the record into NewCustomers and take that ID and insert it as the FK.*Go to next record.So I have written code like this:[font="Courier New"]--INSERT Account Nums into ACCOUNTS Table FirstINSERT INTO ACCOUNTS ([AccountNumber],[Active])VALUES(( --Pull fron OldCustomers and get Account #. If empty (''), insert all zeros. CASE WHEN (SELECT [OldAccountNum] FROM OldCustomers WHERE OldCustomerID BETWEEN 1 AND 5) LIKE '' THEN '000000000' -- Insert Account # from OldCustomers ELSE (SELECT [OldAccountNum] FROM OldCustomers WHERE OldCustomerID BETWEEN 1 AND 5) END ),1)--INSERT into NewCustomers Table and also grab and insert ID of record from Accounts.INSERT INTO NewCustomers([AccountID],[FullName],[Address])(SELECT (SELECT IDENT_CURRENT('ACCOUNTS')),[FullName],[Address] FROM OldCustomers WHERE OldCustomerID BETWEEN 1 AND 5)END[/font]In case you're wondering, the BETWEEN 1 and 5 clause is just for testing. Don't want to insert 75,000 records on a test.When I try to execute this, SQL Server barks at me:[font="Courier New"]sg 512, Level 16, State 1, Procedure sp_Insert...... Line 21Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.The statement has been terminated.Msg 547, Level 16, State 0, Procedure sp_Insert........, Line 40The INSERT statement conflicted with the FOREIGN KEY constraint "FK_NewCustomers_Accounts". The conflict occurred in database "MyDatabase", table "dbo.Accounts", column 'AccountID'.The statement has been terminated.[/font]I'm going to assume that both errors are coming from my lack of a loop. I am also assuming that it is trying to insert ALL of the Account Numbers from OldCustomer into Accounts FIRST before trying to insert even the first NewCustomer Record.Correct?Some guidance would be appreciated!!

SQL Report Help Pls

Posted: 17 Sep 2013 10:01 PM PDT

HiI've got 2 tables in my db ( Property and Contact ) which I need to report on ( extracts below ) :Property :Prop Id.....Choices 48...........1_ChoiceA, 1_ChoiceB, 1_ChoiceC, 1_ChoiceD49...........2_ChoiceA, 2_ChoiceB, 2_ChoiceC50...........3_ChoiceA, 3_ChoiceB, 3_ChoiceC, 3_ChoiceD, 3_ChoiceEContact :Interact Id....p48 1.................1_ChoiceA 2.................1_ChoiceC 3.................NULL 4.................1_ChoiceA 5.................NULL 6.................1_ChoiceB 7.................1_ChoiceA8.................1_ChoiceC I need to create a report which gives a list of totals for all possible instances of Property Id 48 from the Contact table including zeros. The users can add more 'choices' to this list so it needs to check each time I run it for the number of values in the 'Choices' field.So basically, for each possible outcome in Choices field for Property Id 48 in Property table, total up the number of times it appears in the Contact table col p48. e.g. it should return :p48 Options Total1_ChoiceA 31_ChoiceB 11_ChoiceC 21_ChoiceD 0I have written this code ( using a Tally table ) to get list of possible Choices in the comma sep field :SELECT SubString(',' + P.Choices + ',' , T.Inc_No ,CharIndex(',' , ',' + P.Choices + ',' , T.Inc_No) - T.Inc_No)FROM S_Tally T, Property PWHERE T.Inc_No <= LEN(',' + P.Choices + ',')AND SubString(',' + P.Choices + ',' , T.Inc_No - 1, 1) = ','AND P.PropertyId = 48This gives a list of all possible Choices but how do I join this to table Contact to get totals?Hope this ramble makes sense!

Incorrect syntax near 'JOIN'

Posted: 18 Sep 2013 04:46 AM PDT

I am attempting to run this script:[b]SELECT a.GRGR_CK, a.GRGR_ID, a.GRGR_NAME, s.SBSB_ID, s.SBSB_LAST_NAME, s.SBSB_FIRST_NAME, e.CLCL_ID, e.CLCL_CUR_STS, e.CLST_MCTR_REAS, e.CLCL_CL_TYPE, e.CLCL_RECD_DT, GETDATE() AS today, e.Age, CASE WHEN e.Age <= 15 THEN 15 WHEN (e.Age > 15 AND e.Age <= 30) THEN 30 WHEN (e.Age > 30 AND e.Age <= 45) THEN 45 WHEN (e.Age > 45 AND e.Age <= 60) THEN 60 WHEN (e.Age > 60 AND e.Age <= 90) THEN 90 WHEN (e.Age > 90 AND e.Age <= 120) THEN 120 WHEN (e.Age > 120 AND e.Age <= 180) THEN 180 ELSE 181 END AS AgeRange, p.PRPR_ID, p.PRPR_NAME, CASE WHEN (i.CLST_MCTR_REAS IS NULL) THEN g.SYMD_MSG_CD ELSE i.CLST_MCTR_REAS END FROM #temp1112b e INNER JOIN [rpt_ALL].[dbo].[CER_SYMD_MSG_DEF] f INNER JOIN [rpt_ALL].[dbo].[CER_SYML_MSG_LOG] g ON g.SYMD_ID = f.SYMD_ID AND g.SYMD_MSG_CD = f.SYMD_MSG_CD INNER JOIN [rpt_ALL].[dbo].[CMC_GRGR_GROUP] a INNER JOIN [rpt_ALL].[dbo].[CMC_CLCL_CLAIM] i ON a.GRGR_CK = i.GRGR_CK LEFT JOIN [rpt_ALL].[dbo].[CMC_MCTR_CD_TRANS] j ON i.CLST_MCTR_REAS = j.MCTR_ENTITYI receive the following error: Msg 102, Level 15, State 1, Line 14Incorrect syntax near 'JOIN'.Can someone assist me in resolving this error?

Run Job with LAN ID

Posted: 18 Sep 2013 05:37 AM PDT

Hi,I have this issue: a developer told me that her SQL job (run a simple DOS command with her LAN id) failed. It worked before. The history shows: "A required privilege is not held by the client". I checked the setting, proxy account was created and linked to her credential (LAN ID) and "Operating system (CmdExec) was selected with her proxy in "proxy account properties". The account is in "SQLAgentOperatorRole", "SQLAgentReaderRoler" and "SQLAgentUserRole". The account is the local admin group.I modify the job step to run it with "SQL Server Agent Service Account". No problem. Any idea or suggsetion? xp_cmdshell is not allowed to use.Thank youYan

Manualyl update autoincemental ID field

Posted: 17 Sep 2013 06:28 PM PDT

Hi. I have a table where the UserID is set to primary key and autoincrement, i need to import data to that table, and i need the same UserID fields from my old DB. The problem is that i can't write to that field since first it is the primary key, and second it seeams to be read only and it autoincrements. I have removed the Primary key, but it's still read only. I plan to set the column back to it's original state as long as i can import some data one time to it. I have checked that the table has no dependencies, i only need the userID to be writable in the table.

Last executed queries

Posted: 17 Sep 2013 06:58 PM PDT

Dear AllI am using foloing query to get list of last executed queriesSELECT deqs.last_execution_time AS [Time], dest.text AS [Query], dest.*FROM sys.dm_exec_query_stats AS deqsCROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS destORDER BY deqs.last_execution_time DESCBut noticed that for many rows dbid column is null. What does this null value means?Regards

load Report Model in Report Builder - Entities/daataset not shown

Posted: 02 May 2012 05:17 PM PDT

When report model is open in report builder[b][u]3.0[/u][/b]from report manager dataset is not populated by default(i.e. entities are not shown). where as Report builder 1.0 used to show that.Showing entity helps enduser to avoid unecessary cluter.steps to Replicate problem: 1) Open ReportManager->ReportModel2) click on dropdown and select "Load in report builder"3) it will open report builder 3.0 and will not show entities.(i.e it will show nothing under datasets)4) if you changer default report builder for reportmanager to 1.0 version it will show enitities.another way to replicate problem1) use follwoing url : [b]http://<Server>/ReportServer/reportbuilder/reportbuilder.application?model=/Models/<ModelName> [u][/u][/b] it will open report builder 1.0 and will [b]show [/b]entites.2) use follwoing url : [b]http://<Server>/ReportServer/reportbuilder/reportbuilder_3_0_0_0.application?model=/Models/<ModelName> [u][/u][/b] it will open report builder 3.0 and will [b][u]not[/u][/b] show entites.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog