bpfsr
asked on
Help building database in MS SQL Server
In order to create new records in my database, I want to import files such as the one attached. The file is a simple list of ISBN numbers. For each number I import I want to create five records. Each record will still contain a column called ISBN but for each ISBN I would like to create five Conditions - New, LikeNew, VeryGood, Good and Acceptable. Finally I would like to create an "SKU" column which would be a combination of the ISBN first and the condition next, without a space, example 0123456789VeryGood. Help with this query much appreciated.
ISBN-HDC-Test-20.xls
ISBN-HDC-Test-20.xls
You could use calculated columns (persisted or not) and Cross Join:
Please notice you can convert the Table Variables into permanent user tables using CREATE TABLE and removing the @ (I have used Table variables for testing but for your environment you may need to create the actual tables).
I prefer this solution because it is easy to maintain, automate (you have the conditions column not hardcoded into your code and have it persisted for future use).
see how to change "DECLARE @ISBN TABLE" to:
CREATE TABLE dbo.ISBN(
ISBN nvarchar(50) NOT NULL,
Condition nvarchar(50) NULL,
SKU AS (ltrim(rtrim(ISBN))+ltrim(
)
DECLARE @ISBN TABLE(
[ISBN] [nvarchar](50) NOT NULL,
[Condition] [nvarchar](50) NULL,
[SKU] AS (ltrim(rtrim([ISBN]))+ltrim(rtrim([CONDITION]))) PERSISTED
)
DECLARE @TABLECONDITION TABLE (
[Condition] [nvarchar](50)
)
INSERT INTO @TABLECONDITION
SELECT 'New'
UNION ALL
SELECT 'LikeNew'
UNION ALL
SELECT 'VeryGood'
UNION ALL
SELECT 'Good'
UNION ALL
SELECT 'Acceptable'
------------------------------------
INSERT INTO @ISBN
SELECT ISBN, T.* FROM IMPORTEDISBN
CROSS JOIN @TABLECONDITION T
-----------------------------------
SELECT * FROM @ISBN
ORDER BY 1
ASKER
Okay, Ernariash, this is how I built it and I am getting an error:
Msg 102, Level 15, State 1, Line 23
Incorrect syntax near 'C:\Documents and Settings\My Documents\ISBN HDC Test 20.csv'.
CREATE TABLE dbo.ISBN(
ISBN nvarchar(50) NOT NULL,
Condition nvarchar(50) NULL,
SKU AS (ltrim(rtrim(ISBN))+ltrim( rtrim(COND ITION))) PERSISTED
)
DECLARE @ISBN TABLE(
[ISBN] [nvarchar](50) NOT NULL,
[Condition] [nvarchar](50) NULL,
[SKU] AS (ltrim(rtrim([ISBN]))+ltri m(rtrim([C ONDITION]) )) PERSISTED
)
DECLARE @TABLECONDITION TABLE (
[Condition] [nvarchar](50)
)
INSERT INTO @TABLECONDITION
SELECT 'New'
UNION ALL
SELECT 'LikeNew'
UNION ALL
SELECT 'VeryGood'
UNION ALL
SELECT 'Good'
UNION ALL
SELECT 'Acceptable'
-------------------------- ----------
INSERT INTO @ISBN
SELECT ISBN, T.* FROM 'C:\Documents and Settings\My Documents\ISBN HDC Test 20.csv'
CROSS JOIN @TABLECONDITION T
-------------------------- ---------
SELECT * FROM @ISBN
ORDER BY 1
Msg 102, Level 15, State 1, Line 23
Incorrect syntax near 'C:\Documents and Settings\My Documents\ISBN HDC Test 20.csv'.
CREATE TABLE dbo.ISBN(
ISBN nvarchar(50) NOT NULL,
Condition nvarchar(50) NULL,
SKU AS (ltrim(rtrim(ISBN))+ltrim(
)
DECLARE @ISBN TABLE(
[ISBN] [nvarchar](50) NOT NULL,
[Condition] [nvarchar](50) NULL,
[SKU] AS (ltrim(rtrim([ISBN]))+ltri
)
DECLARE @TABLECONDITION TABLE (
[Condition] [nvarchar](50)
)
INSERT INTO @TABLECONDITION
SELECT 'New'
UNION ALL
SELECT 'LikeNew'
UNION ALL
SELECT 'VeryGood'
UNION ALL
SELECT 'Good'
UNION ALL
SELECT 'Acceptable'
--------------------------
INSERT INTO @ISBN
SELECT ISBN, T.* FROM 'C:\Documents and Settings\My Documents\ISBN HDC Test 20.csv'
CROSS JOIN @TABLECONDITION T
--------------------------
SELECT * FROM @ISBN
ORDER BY 1
Well you should import your Test 20.csv ( 'C:\Documents and Settings\My Documents\ISBN HDC Test 20.csv') into a table using SSIS(my choice), Bulk insert, the Import Export wizard.
Or use OPENROWSET or OPENDATASOURCE funtions that will provide you with ad hoc connection information as part of a four-part object name without using a linked server name. Please check that SQL Server may blocked access to STATEMENT OpenRowset and OpenDatasource. Ad Hoc Distributed Queries component is turned off as part of the security configuration. Only system administrators can enable the use of Ad Hoc Distributed Queries (use sp_configure.) See code below:
OpenRowset (http://msdn2.microsoft.com/en-us/library/ms190312.aspx)
OpenQuery( http://msdn2.microsoft.com/en-us/library/ms188427.aspx) to retrieve data.
You will have to enable 'Adhoc Distributed Queries' option using the following code;
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE with override
Or use OPENROWSET or OPENDATASOURCE funtions that will provide you with ad hoc connection information as part of a four-part object name without using a linked server name. Please check that SQL Server may blocked access to STATEMENT OpenRowset and OpenDatasource. Ad Hoc Distributed Queries component is turned off as part of the security configuration. Only system administrators can enable the use of Ad Hoc Distributed Queries (use sp_configure.) See code below:
OpenRowset (http://msdn2.microsoft.com/en-us/library/ms190312.aspx)
OpenQuery( http://msdn2.microsoft.com/en-us/library/ms188427.aspx) to retrieve data.
You will have to enable 'Adhoc Distributed Queries' option using the following code;
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE with override
DECLARE @ISBN TABLE(
[ISBN] [nvarchar](50) NOT NULL,
[Condition] [nvarchar](50) NULL,
[SKU] AS (ltrim(rtrim([ISBN]))+ltrim(rtrim([CONDITION]))) PERSISTED
)
DECLARE @TABLECONDITION TABLE (
[Condition] [nvarchar](50)
)
INSERT INTO @TABLECONDITION
SELECT 'New'
UNION ALL
SELECT 'LikeNew'
UNION ALL
SELECT 'VeryGood'
UNION ALL
SELECT 'Good'
UNION ALL
SELECT 'Acceptable'
------------------------------------
INSERT INTO @ISBN
SELECT ISBN, T.* FROM OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=C:\Documents and Settings\My Documents\ISBN HDC Test 20.csv;', 'SELECT * from Book1.csv');
CROSS JOIN @TABLECONDITION T
-----------------------------------
SELECT * FROM @ISBN
ORDER BY 1
ASKER
Thank you. I ran the
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE with override
without a problem. But then when I ran the other portion I got an error:
Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'CROSS'.
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE with override
without a problem. But then when I ran the other portion I got an error:
Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'CROSS'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Timely and accurate, thank you.
I hope this is what you are lookin for.
Open in new window