Reputation: 89
Inserting in SQL Server Table based on Different Table
I had these two tables in sql server database for C# application.
----------------------------
table_Items
----------------------------
Item1 | Item2| Item3 | Item4
A | B | C | D
E | F | G | Null
H | I | Null | Null
J | Null | Null | Null
------------------
table_Item_Shelves
------------------
Item_Name | Item_ID
A | Null
B | Null
C | Null
D | Null
E | Null
This is what i needed to do. For each row in table_items, first check if row has values in all(item 1,2,3,4) or three of the fields, than in table_Item_Shelves insert '1' for each item in Item_ID field. For next row conduct the same check and get maximum value from Item_ID field and increment by 1. I also need to check that maximum four Item_ID's can be same. Any help in C# and SQL will be great. Thanks.
If table_Item_Shelves column Item_ID already has a value than i should not insert new ID table_Item_Shelves already contain records, and i need to update against those records.
CREATE PROCEDURE UpdateItemIDs AS BEGIN SELECT ROW_NUMBER() OVER ( ORDER BY Item1 ) AS RowIndex , IT.* , 0 AS processed INTO #TempTable FROM dbo.table_items IT WHERE ( Item1 IS NOT NULL AND Item2 IS NOT NULL AND item3 IS NOT NULL ) OR ( Item1 IS NOT NULL AND Item2 IS NOT NULL AND item4 IS NOT NULL ) OR ( Item1 IS NOT NULL AND Item3 IS NOT NULL AND item4 IS NOT NULL ) OR ( Item2 IS NOT NULL AND Item3 IS NOT NULL AND item4 IS NOT NULL )
DECLARE @ITEM1 VARCHAR(50)
DECLARE @ITEM2 VARCHAR(50)
DECLARE @ITEM3 VARCHAR(50)
DECLARE @ITEM4 VARCHAR(50)
DECLARE @RowIndex INT
DECLARE @NewItemID INT
WHILE ( SELECT COUNT(*)
FROM #TempTable
WHERE processed = 0
) > 0
BEGIN
SELECT TOP 1
@ITEM1 = Item1 ,
@ITEM2 = Item2 ,
@ITEM3 = item3 ,
@ITEM4 = Item4 ,
@RowIndex = RowIndex
FROM #TempTable
WHERE processed = 0
UPDATE #TempTable
SET processed = 1
WHERE RowIndex = @RowIndex
SET @NewItemID = ( SELECT ISNULL(MAX(Item_ID), 0) + 1
FROM dbo.table_items_shelves
) ;
UPDATE dbo.table_items_shelves
SET Item_ID = @NewItemID
WHERE Item_Name IN ( @ITEM1, @ITEM2, @ITEM3, @ITEM4 )
AND Item_ID IS NULL
END
END
I have this above stored Procedure which works (From the help of someone), but i need to modify it to work with 8 columns in table_items(Item1,Item2.....Item8), and check if row has values in all(item1, item2....item8) or 5 of the fields, than in table_Item_Shelves insert '1' for each item in Item_ID field. For 8 columns in in table_items(Item1,Item2.....Item8)
able_Items
-----------------------------------------------------------------------
Item1 | Item2 | Item3 | Item4 | Item5 | Item6 | Item7 | Item8 |
------------------------------------------------------------------------
Pencils | Rubbers | Books | DvDs | Glue |Stapler| CDs |Mouse |
Marker |KeyChain |Clipboards |Pens |Bucket| Null |
Monitors| Null |
Glue | Null |Null | Null | Null | Null | Null | Null | Null |
Papers| Null | Null | Null
table_Item_Shelves
------------------
Item_Name | Item_ID
-------------------
Pencils | Null
Rubbers | Null
Pens | Null
Books | Null
Staplers | Null
Glue | Null
Buckets | Null
Keyborads | Null
Monitors | Null
Mouse | Null
CDs | Null
DvDs | Null
Papers | Null
Clipboards| Null
Markers | Null
KeyChains | Null
Now from the Data in tables Provided i expect results something like this
table_Items has values in all columns in row 1,
There is no Item_ID in table so for each item in row 1 i will insert '1' . Than check Row 2, it has 5 items so for each item, i will insert Max(Item_ID) + 1.
Row 3<5 AND Row 4 < 5 AND Row 5<5 columns with values AND ROW 3+ ROW 4 + ROW 5 is also < 5, so i ignore them. also if "Item_ID" is not "NULL or Empty" i ignore the column.
The end result will look like this.
table_Item_Shelves
------------------
Item_Name | Item_ID
-------------------
Pencils | 1
Rubbers | 1
Pens | 2
Books | 1
Staplers | 1
Glue | 1
Buckets | 2
Keyborads | Null
Monitors | Null
Mouse | 1
CDs | 1
DvDs | 1
Papers | Null
Clipboards| 2
Markers | 2
KeyChains | 2
No Design Suggestions please, i know it is horrible. Thanks.
Upvotes: 1
Views: 526
Answers (2)
Reputation: 425
This might work.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
namespace InsertTeamIdIntoTable
{
class Program
{
const string str = @"Data Source=(localdb)\Projects;Initial Catalog=TestDb;Integrated Security=SSPI";
static void Main(string[] args)
{
InsertItemData(str);
}
private static void InsertItemData(string connectionString)
{
string queryString =
"SELECT item1,item2,item3,item4 FROM dbo.table_items;";
using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlCommand command =
new SqlCommand(queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
int itemId = 1;
//check if row has values in all(item 1,2,3,4) or three of the fields,
while (reader.Read())
{
bool flag = CheckValueNumber((IDataRecord)reader);
if (flag)
{
for (int i = 0; i < ((IDataRecord)reader).FieldCount; i++)
{
string itemName = ((IDataRecord)reader)[i].ToString();
if (string.IsNullOrWhiteSpace(itemName) == false)
{
if (CheckItemShelveExists(str, itemName))
{
if (CheckItemIdExists(str, itemName) == false)
{
UpdateTableItemShelves(str, itemId, itemName);
}
}
else
{
InsertTableItemShelves(str, itemId, itemName);
}
}
}
itemId++;
}
}
reader.Close();
}
}
public static void UpdateTableItemShelves(string connectionString, int itemId, string itemName)
{
string updateString = string.Format("Update dbo.table_item_shelves set item_id ={0} WHERE item_name ='{1}';", itemId, itemName);
using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlCommand command =
new SqlCommand(updateString, connection);
connection.Open();
command.ExecuteNonQuery();
}
}
public static void InsertTableItemShelves(string connectionString, int itemId, string itemName)
{
string updateString = string.Format("Insert Into dbo.table_item_shelves(item_id,item_name) VALUES({0},'{1}');", itemId, itemName);
using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlCommand command =
new SqlCommand(updateString, connection);
connection.Open();
command.ExecuteNonQuery();
}
}
public static bool CheckItemShelveExists(string connectionString, string itemName)
{
string updateString = string.Format("Select count(id) From dbo.table_item_shelves WHERE item_name ='{0}';", itemName);
using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlCommand command =
new SqlCommand(updateString, connection);
connection.Open();
return (Int32)command.ExecuteScalar() > 0;
}
}
public static bool CheckItemIdExists(string connectionString, string itemName)
{
string updateString = string.Format("Select item_id From dbo.table_item_shelves WHERE item_name ='{0}';", itemName);
using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlCommand command =
new SqlCommand(updateString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
if (string.IsNullOrWhiteSpace(((IDataRecord)reader)[0].ToString()) == false)
{
return true;
}
}
reader.Close();
return false;
}
}
public static bool CheckValueNumber(IDataRecord record)
{
int count = 0;
for (int i = 0; i < record.FieldCount; i++)
{
if (string.IsNullOrWhiteSpace(record[i].ToString()) == false)
{
count++;
}
}
return count >= 3;
}
}
}
Upvotes: 0
Reputation: 2380
It's a little hard to understand your desired final outcome (no expected result example) but the first part of your procedures WHERE claus could be simplified dramatically to deal with more fields more easily...
-- OLD
WHERE ( Item1 IS NOT NULL
AND Item2 IS NOT NULL
AND item3 IS NOT NULL
)
OR ( Item1 IS NOT NULL
AND Item2 IS NOT NULL
AND item4 IS NOT NULL
)
OR ( Item1 IS NOT NULL
AND Item3 IS NOT NULL
AND item4 IS NOT NULL
)
OR ( Item2 IS NOT NULL
AND Item3 IS NOT NULL
AND item4 IS NOT NULL
)
by using an alternate format like this (extra data to allow query to run standalone)
This format allows you to just duplicate the CASE statements and change the Criteria on the count of valid fields.
WITH A AS (
SELECT 'A' AS Item1, 'B' AS Item2, 'C' AS Item3, 'D' AS Item4
UNION ALL SELECT 'E', 'F', 'G', NULL
UNION ALL SELECT 'H', 'I', NULL, NULL
UNION ALL SELECT 'J', NULL, NULL, NULL
), B AS (
SELECT A.*
, ROW_NUMBER() OVER ( ORDER BY Item1 ) AS RowIndex
, 0 AS processed
, CASE WHEN Item1 IS NULL THEN 0 ELSE 1 END
+ CASE WHEN Item2 IS NULL THEN 0 ELSE 1 END
+ CASE WHEN Item3 IS NULL THEN 0 ELSE 1 END
+ CASE WHEN Item4 IS NULL THEN 0 ELSE 1 END
AS ValidFieldCount
FROM A
)
SELECT *
FROM B
WHERE ValidFieldCount >=3
Upvotes: 0
Related Questions
- Inserting data row into a sql table from another sql table in C#
- inserting table values from one table to another
- How to insert values from a table in another along with other values in sql server?
- insert into two tables
- insert value in a table from another table
- insert into table from another table and values
- Inserting data from two tables into one c#
- Insert data into two table in SQL
- How can I insert into two sql related tables?
- inserting values from one table into another table using sql/c#.net