SQLSponsored by Jamf Now

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

Share tech news, updates, or what's on your mind.

Sign up to Post

Hi,

How to insert datatable rows into specified SQL table columns.

For example,

I have DataTable which contains 4 columns and 100 Rows.

I have SQL Table called "Employee" which contains 10 columns and 500 Rows.

However the matching column will be available in both tables (DataTable and SQL Table)

I just want to insert the Datatable 4 columns into SQL table.

So the final records would be 600 Rows..

Note : remaining 6 columns cell may be blank or null.

How to do this in C#.NET
0
Increase Security & Decrease Risk with NSPM Tools
Increase Security & Decrease Risk with NSPM Tools

Analyst firm, Enterprise Management Associates (EMA) reveals significant benefits to enterprises when using Network Security Policy Management (NSPM) solutions, while organizations without, experienced issues including non standard security policies and failed cloud migrations

Hi

I have 2 PostrgreSQL/ PostGIS databases I need to update a table in 1 DB with data in a table  in the second

geo_table in DB1

id,country_code,country_name,long,lat,geom  # point data

Open in new window


boundary.table in DB2

id,country_code,country_name,geom  # polygons

Open in new window



If both tables were in the same database I could run a select query something like (UNTESTED)

UPDATE geo.table
set g.country_code = b.country_code,g.country_name = b.country_name
FROM
geo_table g, boundary b
INNER JOIN on ST_intersect(geo_table.geom, b.geom) 

Open in new window



How can I update columns country_code,country_name in the geo_table with corresponding values in the boundary table where the 2 geometries intersect

If necessary I could copy the boundary table into DB1 but this would mean maintaining duplicate data
0
In my quest to create a poor man's address correction routine I created about 25 stored procedures similar to the one below. The only thing that changes are the strings used in the search. I have wrapped all these sp's into a VS program plus an SSIS job. What I just discovered is that the only changes that are sticking are the ones in the last sp that is called. If I run them individually the changes stick. I have a COMMIT TRAN at the end of each one.  What must I change to get the changes to stick as each sp is called? It must have something to do with all these sps called inside one session.


CREATE PROCEDURE [dbo].[apd_UpdateAddressesCASESB02]

AS

BEGIN

      SET NOCOUNT ON

DECLARE @TranCount int
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int

SET @TranCount = @@TRANCOUNT

IF @TranCount = 0
      BEGIN TRAN

BEGIN TRY

UPDATE mt
 SET [MOD_ESRI_LOCATION] = chg99.[MOD_ESRI_LOCATION]
 FROM [dbo].[cases] mt
 CROSS APPLY (
     SELECT CASE WHEN PATINDEX('%BLVD%', mt.[MOD_ESRI_LOCATION]) >= 4 THEN SUBSTRING(mt.[MOD_ESRI_LOCATION],1,PATINDEX('%BLVD%',mt.[MOD_ESRI_LOCATION])+1)
         ELSE mt.[MOD_ESRI_LOCATION] END  AS [MOD_ESRI_LOCATION]
 ) AS chg01
 CROSS APPLY (
     SELECT  CASE WHEN PATINDEX('%BSMT%', chg01.[MOD_ESRI_LOCATION]) >= 4 THEN SUBSTRING(chg01.[MOD_ESRI_LOCATION],1,PATINDEX('%BSMT%',chg01.[MOD_ESRI_LOCATION])-1)
         ELSE chg01.[MOD_ESRI_LOCATION] END AS [MOD_ESRI_LOCATION]
 ) AS chg02
 CROSS APPLY (
     SELECT CASE WHEN …
0
Hello,
I have Table1 with date fields
From date  null
To      Date null
Howevr wen  run this query below it inserts 1900-01-01 in the column

INSERT INTO [TABLE1] 

SELECT 
         , IET.[From]
         , IET.[To]
 FROM [TABLE2] IET


WHERE [InvoiceItemGUID] NOT IN 
    (SELECT [InvoiceItemGUID] FROM TABLE1 )

Open in new window


Thanks
0
Hello,I have a project in winforms which takes care of the attendance of the students.In my project I have a form called AttendanceList which has the following:
-a textbox for the Serial number on the NFC tag( the attendance will be based on every student swiping the card over the card reader).
-a combobox where the teacher selects the Course and based on the course,when the teacher clicks a button,the list with all the students should appear.
- a datagridview which displays,updates and deletes the students.
The problem that i'm facing is that when a student registers,he will appear in table RegisterStudent(which is the table for all the registered students).I want to take the values from RegisterStudent(sNr,SN,fName and lName) and insert it in AttendanceList table.This is my method in doing so:
  private void LoadStudents()
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                try
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        string query = "insert into AttendanceList(SN,sNr,fName,lName) select SN, sNr, fName,lName from RegisterStudent p inner join StudentCourses c on p.SN=c.StudentId WHERE  c.CourseId = '" + cmbClassId.SelectedValue.ToString() + "'";
                      // cmd.Parameters.AddWithValue("@CourseId", cmbClassId.Text);
                        cmd.Parameters.AddWithValue("@SN", txtStudentId.Text);
               …
0
i am facing same problem

SQL>  recover database using BACKUP CONTROLFILE until cancel;
ORA-00279: change 7918209709 generated at 06/20/2018 14:57:16 needed for threa
1
ORA-00289: suggestion : I:\ORACLE\AE1\ORAARCH\AE1ARCH1_256980_926244410.DBF
ORA-00280: change 7918209709 for thread 1 is in sequence #256980


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
I:\oracle\AE1\oraarch_bkp\AE1ARCH1_256980_926244410.DBF
ORA-00279: change 7918242496 generated at 06/20/2018 15:00:28 needed for threa
1
ORA-00289: suggestion : I:\ORACLE\AE1\ORAARCH\AE1ARCH1_256981_926244410.DBF
ORA-00280: change 7918242496 for thread 1 is in sequence #256981
ORA-00278: log file 'I:\oracle\AE1\oraarch_bkp\AE1ARCH1_256980_926244410.DBF'
no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
I:\oracle\AE1\oraarch_bkp\AE1ARCH1_256981_926244410.DBF
ORA-00279: change 7918292819 generated at 06/20/2018 15:02:46 needed for threa
1
ORA-00289: suggestion : I:\ORACLE\AE1\ORAARCH\AE1ARCH1_256982_926244410.DBF
ORA-00280: change 7918292819 for thread 1 is in sequence #256982
ORA-00278: log file 'I:\oracle\AE1\oraarch_bkp\AE1ARCH1_256981_926244410.DBF'
no longer needed for this recovery
0
In SQL Server 2008/2012, how  do i check whether this patch KB4057114/KB4057116 is installed or not?
1
I am trying to run sqlcmd with a different windows user
sqlcmd -S servername -u domain\user -p password -i sqlscript.sql -v param=param1

this doesnt work

I also tried powershell.exe Invoke-sqlcmd -S servername -Username domain\user -password password -Inputfile sqlscript.sql -Variable "param='param1'"

both of these doesnt like to authenticate with a different windows user but I think they work for sql authentication.

I would appreciate if you could let me know how to authenticate with a different windows user without any prompts because I am trying to run it using jenkins and I do not want run it interactively.

Request experts to please help me with this.

Please feel free to let me know if you need more info.
0
I want to get a list of sales orders that are 7 years old so I wrote the following query:

SELECT SalesOrderNumber, CAST (OrderDate AS date) AS 'Date of order'
FROM Sales.SalesOrderHeader
WHERE  DATEDIFF(YYYY, OrderDate, GETDATE()) = 7                            

I am concerned about the effect the  DATEDIFF expression in the WHERE clause will have on the execution plan that is decided upon by the optimizer because I know that one should not   have an expression on the lhs , so I changed it for this query and I gave me exactly the same execution plan:

DECLARE @compdate INT = 7
SELECT SalesOrderNumber, CAST (OrderDate AS date) AS 'Date of order'
FROM Sales.SalesOrderHeader
WHERE  @compdate = DATEDIFF(YYYY, OrderDate, GETDATE())  

Is there a better way of doing it ?
0
Hi,
I need to know how to convert the following Query code to Microsoft SQL Server Stored Procedure.

public string db_get_unhandled_items_CommandText(int? fromIdPerson, int? toIdPerson, int? fromIditem, int? toIditem)
{
    string IdPerson_cond = get_cond_str(fromIdPerson, toIdPerson, "aa.IdPerson");
    string Iditem_cond = get_cond_str(fromIditem, toIditem, "aa.Iditem");

    return @"
        select bb.Iditem,CONCAT('http://www.mysite.com/Items', replace(r.SourceURL, '\', '/')) urlSource, r.IdResource
        from [MYDB].[dbo].tblitem bb
          inner join [MYDB].[dbo].tblitemPerson aa
            on aa.Iditem = bb.Iditem AND " + Iditem_cond + @"
            inner join [MYDB].[dbo].tblResource r
            on bb.IdPrimaryAsset = r.IdAsset
            left join [dev].[AW_IR].[tblitem_labels] t2 on t2.Iditem = bb.Iditem
        where t2.Iditem is null AND " + IdPerson_cond + @"
        order by Iditem";
}

private static string get_cond_str(int? from, int? to, string Id_column)
{
    string query;
    if (from == null)
    {
        if (to == null)
        {
            query = "1 = 1";
        }
        else
        {
            query = Id_column + " <= " + to;
        }
    }
    else
    {
        if (to == null)
        {
            query = Id_column + " >= " + from;
        }
        else
        {
            query = Id_column + " >= " + from + " AND " + Id_column + " <= " + to;
        }
    }
    return query;
}
0
Ultimate Tool Kit for Technology Solution Provider
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Hi there,
If I'm searching for phrase "A B" and I define the values , for example, of A as (Red, blue, green, yellow, brown,) and B as (Jacket, hat, blouse, car), then the number of phrases I'm looking for is 20 and the number of words is 40.
Red Jacket ,Red hat, Red Blouse, Red Car
Blue Jacket etcc….  
Is there a way/tool/ search engine that would allow me to search for all the 20 phrases/40 words by just specifying A:  and B (total 9 words) (Red, blue, green, yellow, brown)?
Regards
Hashem
0
Summarize SQL values that need to be divided with remainders.

I need to summarize a group of SKU's and their weights. the challenge I have is that that the weight field has a value that needs to be divided by 100 to get the correct weight
i.e. Sku#1 has a value of 15200 which should actually be 152.00 pounds
Sku#2 has a value of 3 which should actually calculate to .03
Sku#3 has a value of 15 which should actually calculate to .15

The summarized value for the 3 Sku's should then = 152,18 pounds

How would I formulate the sku values to summarize as shown?

Thanks
0
I had this question after viewing SQL Replication problem..

Hello, experts.  I was able to successfully configure my publisher/distribution servers, however, when I created the near real time subscriptions, I ran into issues.  I then attempted to tear down replication and start over, however, I am now receiving the "Could not connect to server 'DISTSVR123' because distributor_admin is not defined as a remote login at the server....  

I am attempting to remove the publications previously created and am unable to do so because of  the aforementioned error.  The servers have not been renamed as that is the solution I'm seeing over and over when attempting to find the solution on my own.  Any assistance would be tremendously appreciated.  

Nikki
0
HELP WITH SQL SERVER 2008R2 STATEMENT

I have a table called SHIPPER with a field called CUST_ORDER_ID and PACKLIST_ID  Ex: DC180001

i have a view called CT_TRACKING_INFORMATION_VIEW that has a field called TRACKING_INFO and PACKLISTS that can contain multiple PACKLIST_ID Ex: DC180001, DC180003, DC180040

I need a select statement that pulls the SHIPPER record by ORDER_ID and the TRACKING_INFO field if the PACKLIST_ID is "in" the PACKLISTS field.
This works if i hard code the LIKE '%DC180001%'
SELECT SHIPPER.CUST_ORDER_ID, shipper.PACKLIST_ID, Tracking_Information FROM SHIPPER, CT_TRACKING_INFORMATION_VIEW WHERE CUST_ORDER_ID = 'CC181430' AND (CT_TRACKING_INFORMATION_VIEW.packlist_id LIKE '%DC180001%' OR CT_TRACKING_INFORMATION_VIEW.packlist_id IS NULL)

Open in new window


but i can't figure out how to make it use the shipper.PACKLIST_ID   i.e. This does not work:
SELECT SHIPPER.CUST_ORDER_ID, shipper.PACKLIST_ID, Tracking_Information FROM SHIPPER, CT_TRACKING_INFORMATION_VIEW WHERE CUST_ORDER_ID = 'CC181430' AND (CT_TRACKING_INFORMATION_VIEW.packlist_id LIKE '%' + shipper.PACKLIST_ID +'%' OR CT_TRACKING_INFORMATION_VIEW.packlist_id IS NULL)

Open in new window


ok, got this to work somewhat:
 WHERE     (dbo.CT_TRACKING_INFORMATION_VIEW.packlist_id LIKE '%' + s.PACKLIST_ID + '%') OR
                      (dbo.CT_TRACKING_INFORMATION_VIEW.packlist_id IS NULL)

Open in new window


but it returns 2 records for each Order 1 with Null TRACKING_INFORMATION and 1 with the actual tracking information.
Note all records will have tracking information, that is why i am including IS NULL.
what i need is for the select statement to bring back only 1 record and if it has tracking information to include it.
0
hi,

as MS SQL has always encrypted feature, what is the diff between Network Encryption and always encrypted?
0
Hi,

I have a two queries. One is for union and the other is for recent. From the recent query i want to do totals based on Criteria. The criteria is for same "Cou","Flow","Years","PType", Sum "Values" based on Pro. Attach is a sample DB in which you can see tblPro have different ID.The order for this tblPro is ID="1" is total for ID "200,201,300" and ID="2" is total for ID="100", so that total should be on the basis of Product with Sum of Values and group by other. i have try that in qry_total , but it's not working. Any help?

Attach is a sample DB.

Thank you.
error.accdb
0
I am looking for the following Oracle query
   I have a table TB_Docs . The DocNo column has sample values ( Length is not fixed . Also the "_ " can appear anywhere )  
     123_456-23_0
      12-32_1-24567_0
       A1-23_c2_1
      G2/334/1_A

  The "_ " before the last character should be replaced by  "/"  ( For example 123_456-23_0  should be updated as 123_456-23/0 . But it should not update any other  "_ " other than the one before the last character)
0
I had this question after viewing Syntax to import multiple files with Oracle IMP.

I invoke imp by passing parfile=Import_Parfile.txt. Is it still possible in below way? please help

file=file1.dmp,file2.dmp,file3.dmp,file4.dmp
log=file_alll.log
ignore=Y
rows=Y
grants=none
fromuser=SCOTT

Open in new window

0
I build vb.net application and connect to SQL Server 2012, and my code is to print all report that user selected (Checkbox in datagridview), let say DataGrideView has 5 Rows and user select 3 row and press Click on print button and the system will print the 3 item that user selected, but my problem is when user select 3, it will print only 2, if select 2 it will print only 1, and if select only 1 row, it will not print. And here is my code.

                        For Each drow As DataGridViewRow In DataGridView1.Rows

                            If drow.Cells(0).Value = True Then
                                Dim frm As New frmViewReports
                                Dim rpt As New rptInvoiceKHDolla
                                Dim dsRptPurchase As New dsReport1
                                Dim daPurchase As SqlDataAdapter

                                Dim strSql As String
                                strSql = "select * from v_RptInvoicesB5 where InvoiceNo=" & drow.Cells(1).Value
                                daPurchase = New SqlDataAdapter(strSql, sqlcon)
                                daPurchase.Fill(dsRptPurchase, "v_RptInvoicesB5")
                                GF_ViewReport2(rpt, dsRptPurchase, "Print Invoices")
                                drow.Cells(0).Value = False
                            End If
                        Next



and here is my function to print that report.

    Public Function GF_ViewReport2(ByVal …
0
Problems using Powershell and Active Directory?
LVL 8
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

how to convert ssql syntax to C# syntax:

declare @mnt1 decimal(18,4), @mnt_r decimal(18,4), @nbr bigint  
  declare @ST_NB_DECIMAL_QTY INT  
  set @mnt1 = 16.60
    select @ST_NB_DECIMAL_QTY = 2
	  set @nbr = POWER(10 ,@ST_NB_DECIMAL_QTY)  
  Set @mnt_r = round(@nbr * @mnt1,@nbr)  
  IF @mnt_r < 0  
  Set @mnt_r = CEILING(@mnt_r)/ @nbr  
  ELSE  
  Set @mnt_r = FLOOR(@mnt_r)/ @nbr  
  Select  @mnt_r

Open in new window

0
Hello experts. I inherited a front end ms access application which connects to a sql database. One of my forms it connects to a sql table called tbl_conversations. The table has no primary or foreign keys (if that matters). Here is my issue. I added a new column to the table (tbl_conversations) called track. I then re-open ms access and when I attempt to update the table in the linked table manager I received a message that says "The search key was not found in any record". I select ok and naturally I cannot see the field I want to add as a record source on the form. I tried a compact and repair and still no luck. Any ideas on how to resolve would be great.
0
I clicked on the "Reports" tab of Server 2016 Windows Server Updates Services and was told that "The  "Microsoft Report Viewer 2012 Redistributable"  is required for this feature. Please close the console before installing this package."

So I clicked on the URL link and was directed to the website https://www.microsoft.com/en-us/download/details.aspx?id=35747 where I downloaded the "Microsoft Report Viewer 2012 runtime."

However, when I installed the "Microsoft Report Viewer 2012 runtime" ReportViewer.msi file I received a message that said "Setup is missing an installation prerequisite. -Microsoft System CLR Types for SQL Server 2012" (see the screenshot).

I have googled this and the results I have found have had me download several different MSI installation files without telling me exactly which one(s) I need to install. I want to make sure that I only install the component or program that I absolutely need and that I don't install anything extra.

So what is the best URL to visit to download and install the right "Microsoft System CLR Types for SQL Server 2012?"

Microsoft System CLR Types
0
sql and php vulnerabilities
0
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

using System.Collections.Generic;
using System.ComponentModel;

using System.Drawing;
using System.Text;
using System.IO;
using System.IO.Ports;
using System.Threading;
using System.Text.RegularExpressions;
using System;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SqlStoredProcedure1(string strPortName, string strBaudRate, String strPhone, String StrMsg)
    {
        try
        {
            bool sms_status;
        SerialPort port = new SerialPort();
        port.PortName = strPortName;
        port.BaudRate = Convert.ToInt32(strBaudRate);               //updated by Anila (9600)
        port.DataBits = 8;
        port.StopBits = StopBits.One;
        port.Parity = Parity.None;
        port.ReadTimeout = 300;
        port.WriteTimeout = 300;
        port.Encoding = Encoding.GetEncoding("iso-8859-1");
        //  port.DataReceived += new SerialDataReceivedEventHandler(port_DataReceived);
        port.Open();
        port.DtrEnable = true;
        port.RtsEnable = true;
        StoredProcedures p = new StoredProcedures();
        sms_status = p.sendMsg(port, strPortName, strBaudRate, strPhone, StrMsg);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public AutoResetEvent receiveNow;



    //Close Port
    public void ClosePort(SerialPort port)
    {
  

Open in new window

0
Hi

Is it possible to have a line break in an ASP.net GridView cell that is bound to SQL data?
0

SQLSponsored by Jamf Now

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.