Dynamic SQL error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic SQL error

Author

Topic

mclam
Starting Member

6 Posts

Posted - 2011-10-28 : 11:13:05

Solved

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-28 : 11:17:47

would you mind explaining what you're trying to achieve with above code?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-28 : 11:19:56

>> I am trying to load data from one table to another

What are the names of the 2 tables, and please post the DDL and what data is going from what table to which table

ALSO please let us know if there are any predicates associated with the move

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

mclam
Starting Member

6 Posts

Posted - 2011-10-28 : 11:37:54

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-28 : 11:46:31

it would really help with at least a sample of the DDL..but..if I get your meaning...you want to normalize your data..right?

Without samples we can only guess..and NO ONE is going to try and decipher your ...dynamic sql..something like this?




CREATE TABLE myTable99(PK_Col varchar(10), Jan int, Feb int, Mar int)
CREATE TABLE myTable98(PK_Col varchar(10), Month_Col char(3), Amt_Col int)
GO

INSERT INTO myTable99(PK_Col, Jan, Feb, Mar)
SELECT 'xxx',1,2,3 UNION ALL
SELECT 'xyz',1,2,3 UNION ALL
SELECT 'zzz',1,2,3
GO

INSERT INTO myTable98(PK_Col, Month_Col, Amt_Col)
SELECT PK_Col, 'Jan', Jan
FROM myTable99
UNION ALL
SELECT PK_Col, 'Feb', Feb
FROM myTable99
UNION ALL
SELECT PK_Col, 'Mar', Mar
FROM myTable99
GO

SELECT * FROM myTable98
GO

DROP TABLE myTable99, myTable98
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

mclam
Starting Member

6 Posts

Posted - 2011-10-28 : 12:05:02

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-28 : 12:31:50

you can dispense with dynamic sql like this (assuming you're using sql 2005 or above)


DECLARE @DateSK int
SET @DateSK = 20110901
DECLARE @year int
SET @year = 2011

INSERT INTO dbo.A
([Date_SK], [CHRG_TYPE], [CD], [SVP],
[Category], [J_Chrg], [F_Chrg], [M_Chrg])
SELECT @DateSK,
CASE WHEN FieldName LIKE '%Pln' THEN 'B'
     WHEN FieldName LIKE '%Act' THEN 'A'
     ELSE 'F'
END,
CASE WHEN FieldName LIKE '%Pln' THEN CAST (@year as varchar(4)) +'P'
     WHEN FieldName LIKE '%Act' THEN 'ACT
     ELSE '8+4'
END,
[SVP],
[Category],
MAX(CASE WHEN FieldName LIKE 'Jan_%' THEN FieldValue ELSE NULL END),
MAX(CASE WHEN FieldName LIKE 'Feb_%' THEN FieldValue ELSE NULL END),
MAX(CASE WHEN FieldName LIKE 'Mar_%' THEN FieldValue ELSE NULL END)
FROM
(
SELECT
[SVP],
[Category],
REPLACE(REPLACE(REPLACE(FieldName,'Jan_',''),'Feb_',''),'Mar_','') AS FieldType,
FieldName,
FieldValue
FROM dbo.B
UNPIVOT (FieldValue FOR FieldName IN (Jan_Act , Feb_Act , Mar_Act , Jan_8+4F ,Feb_8+4F ,Mar_8+4F,...))u
)m
GROUP BY [SVP],
[Category],
FieldType

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-28 : 13:15:43

if its 2000, it will be like


DECLARE @DateSK int
SET @DateSK = 20110901
DECLARE @year int
SET @year = 2011

INSERT INTO dbo.A
([Date_SK], [CHRG_TYPE], [CD], [SVP],
[Category], [J_Chrg], [F_Chrg], [M_Chrg])
SELECT @DateSK, 'B',CAST (@year as varchar(4)) +'P',[SVP],[Category],Jan_Pln,feb_Pln,Mar_Pln FROM dbo.B
UNION ALL
SELECT @DateSK, 'A','ACT',[SVP],[Category],Jan_Act,feb_Act,Mar_Act FROM dbo.B
UNION ALL
SELECT @DateSK, 'F','8+4',[SVP],[Category],Jan_Jan_8+4F,feb_Jan_8+4F,Mar_Jan_8+4F FROM dbo.B

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mclam
Starting Member

6 Posts

Posted - 2011-10-28 : 14:10:12

mclam
Starting Member

6 Posts

Posted - 2011-10-28 : 17:19:01

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-29 : 00:47:00

so you dont want any other values (SD,Category etc)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mclam
Starting Member

6 Posts

Posted - 2011-10-29 : 09:09:53

There was no problem with other values like CD, category etc. The only issue is with the above result set (Jan_Chrg..Dec_Chrg) that I mentioned in the above response. Any ideas on how that can be achieved?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-29 : 09:39:36

are CD and Category fields having different values for above set?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources