Create table Error 2

[hedgracer]

I am receiving the following error on a create table string in a stored procedure:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.

Here is the code:

DECLARE @SQLSTRING AS VARCHAR(8000);


--create Moneyline_One @YEARMO table
SET @SQLSTRING = 'CREATE TABLE DBO.Moneyline_One_201105 ('+
' [Record_Id] [varchar](1) NULL,' +
' [Firm] [varchar](1) NULL,' +
' [Office] [varchar](3) NULL,' +
' [Account] [varchar](5) NULL,' +
' [Account_Type] [varchar](2) NULL,' +
' [Sales_Series] [varchar](5) NULL,' +
' [Account_Class_Code] [varchar](1) NULL,' +
' [Account_SubClass_Code] [varchar](1) NULL,' +
' [Account_Currency_Code] [varchar](3) NULL,' +
' [Product_Currency_Code] [varchar](2) NULL,' +
' [Account_Base_Currency_Code] [varchar](3) NULL,' +
' [Firm_Base_Currency_Code] [varchar](3) NULL,' +
' [Currency_Processing_Code] [varchar](1) NULL,' +
' [Number_Call_Records_Account] [numeric](2, 0) NULL,' +
' [Oldest_Call_Account] [numeric](2, 0) NULL,' +
' [Filler_Byte_Available] [varchar](1) NULL,' +
' [Avg_Equity_Calc_Days] [numeric](2, 0) NULL,' +
' [Discretionary_Code] [varchar](1) NULL,' +
' [Omnibus_Code] [varchar](1) NULL,' +
' [Country_Province_Code] [varchar](2) NULL,' +
' [Mgd_Account_Prgm_ID] [varchar](2) NULL,' +
' [Short_Name] [varchar](20) NULL,' +
' [Cust_Name_Address_One] [varchar](32) NULL,' +
' [Last_Activity_Date] [varchar](9) NULL,' +
' [Last_Activity_Date_Work] [varchar](9) NULL,' +
' [Related_Firm_One] [varchar](1) NULL,' +
' [Related_Office_One] [varchar](3) NULL,' +
' [Related_Account_One] [varchar](5) NULL,' +
' [Related_Type_One] [varchar](1) NULL,' +
' [Related_Firm_Two] [varchar](1) NULL,' +
' [Related_Office_Two] [varchar](3) NULL,' +
' [Related_Account_Two] [varchar](5) NULL,' +
' [Related_Type_Two] [varchar](1) NULL,' +
' [Related_Firm_Three] [varchar](1) NULL,' +
' [Related_Office_Three] [varchar](3) NULL,' +
' [Related_Account_Three] [varchar](5) NULL,' +
' [Related_Type_Three] [varchar](1) NULL,' +
' [Group_Firm_Number] [varchar](1) NULL,' +
' [Group_Office_Number] [varchar](3) NULL,' +
' [Group_Sales_Series] [varchar](5) NULL,' +
' [Group_Account_Number] [varchar](5) NULL,' +
' [Account_Balance] [numeric](15, 2) NULL,' +
' [Securities_Long_Mkt_Value] [numeric](15, 2) NULL,' +
' [Securities_Short_Mkt_Value] [numeric](15, 2) NULL,' +
' [Long_Option_Value] [numeric](15, 2) NULL,' +
' [Short_Option_Value] [numeric](15, 2) NULL,' +
' [Total_Outstanding_Margin_Calls] [numeric](15, 2) NULL,' +
' [Withdrawable_Funds] [numeric](15, 2) NULL,' +
' [Futures_Initial_Margin_Reqt] [numeric](15, 2) NULL,' +
' [Futures_Maint_Margin_Reqt] [numeric](15, 2) NULL,' +
' [Futures_Exch_Min_Initial_Reqt] [numeric](15, 2) NULL,' +
' [Margin_Excess_Deficit] [numeric](15, 2) NULL,' +
' [Futures_Open_Trade_Equity] [numeric](15, 2) NULL,' +
' [Special_Misc_Account_Amt] [numeric](15, 2) NULL,' +
' [Long_Options_Open_Trade_Equity] [numeric](15, 2) NULL,' +
' [Short_Option_Open_Trade_Equity] [numeric](15, 2) NULL,' +
' [Avg_Equity] [numeric](15, 2) NULL,' +
' [Month_To_Date_Commission] [numeric](15, 2) NULL,' +
' [Unsettled_Security_Long_Mkt_Value] [numeric](15, 2) NULL,' +
' [Trade_Date_Mkt_Balance_Securities] [numeric](15, 2) NULL,' +
' [Unsettled_Short_Mkt_Value] [numeric](15, 2) NULL,' +
' [Mkt_Maker_Haircut_Amt] [numeric](15, 2) NULL,' +
' [Jpn_Unr_Cash_Deposits] [numeric](15, 2) NULL,' +
' [Mth_To_Date_P_and_L] [numeric](15, 2) NULL,' +
' [Seg_Req_Long_Mkt_Value] [numeric](15, 2) NULL,' +
' [Seg_Req_Short_Mkt_Value] [numeric](15, 2) NULL,' +
' [Equities_Initial_Marging_Req] [numeric](15, 2) NULL,' +
' [Equities_Maint_Margin_Req] [numeric](15, 2) NULL,' +
' [Conv_Rate_Firm_Base_Curr] [numeric](15, 2) NULL,' +
' [Monthly_Dividend_Balance] [numeric](15, 2) NULL,' +
' [Jpn_Unsettled_Unrealized_P_and_L] [numeric](15, 2) NULL,' +
' [Futures_Style_Opt_OTE] [numeric](15, 2) NULL,' +
' [Loan_Value] [numeric](15, 2) NULL,' +
' [Buying_Power] [numeric](15, 2) NULL,' +
' [Pending_Dividend_Amt] [numeric](15, 2) NULL,' +
' [Pending_Interest_Amt] [numeric](15, 2) NULL,' +
' [Liquidating_Value] [numeric](15, 2) NULL,' +
' [Margin_Collateral_Value] [numeric](15, 2) NULL,' +
' [Futures_OTE_Credit] [numeric](15, 2) NULL,' +
' [Futures_OTE_Debit] [numeric](15, 2) NULL,' +
' [Opt_Premium_Value] [numeric](15, 2) NULL,' +
' [Futures_Style_Opt_OTE_Credit] [numeric](15, 2) NULL,' +
' [Avg_Sec_On_Deposit] [numeric](15, 2) NULL,' +
' [Long_Options_Premium] [numeric](15, 2) NULL,' +
' [Unsettled_Unrealized_P_and_L] [numeric](15, 2) NULL,' +
' [Avg_Margin_Excess] [numeric](15, 2) NULL,' +
' [Jpn_IR] [numeric](15, 2) NULL,' +
' [Jpn_IR_Cash] [numeric](15, 2) NULL,' +
' [Jpn_MR] [numeric](15, 2) NULL,' +
' [Jpn_Initial_To_Release] [numeric](15, 2) NULL,' +
' [Total_Account_Requirements] [numeric](15, 2) NULL,' +
' [Equity_Products_OTE] [numeric](15, 2) NULL,' +
' [LOV_US_Treasury] [numeric](15, 2) NULL,' +
' [Total_Equity] [numeric](15, 2) NULL,' +
' [Avg_Balance] [numeric](15, 2) NULL,' +
' [Avg_Opt_Value] [numeric](15, 2) NULL,' +
' [Conv_Rate_Account_Base_Currency] [numeric](15, 2) NULL,' +
' [Commissions_Accrued_Recv] [numeric](15, 2) NULL,' +
' [Commissions_Accrued_Unsecured] [numeric](15, 2) NULL,' +
' [Span_Option_Value] [numeric](15, 2) NULL,' +
' [Equities_Exch_Min_Margin_Req] [numeric](15, 2) NULL,' +
' [Equities_Initial_Req_Trades] [numeric](15, 2) NULL,' +
' [Equities_Option_Initial_Reqts] [numeric](15, 2) NULL,' +
' [Equities_Option_Maint_Reqts] [numeric](15, 2) NULL,' +
' [Futures_Exch_Min_Maint_Reqt] [numeric](15, 2) NULL,' +
' [Group_Excess_Used_Deficit_Covered] [numeric](15, 2) NULL,' +
' [Non_Cash_OTE] [numeric](15, 2) NULL,' +
' [Cash_Collateral_Daily_Net_Value] [numeric](15, 2) NULL,' +
' [Equity_Percent] [numeric](15, 2) NULL,' +
' [Regulatory_Collateral_Value] [numeric](15, 2) NULL,' +
' [Conv_Rate_Account_Base_Cur_SMFD] [numeric](15, 2) NULL,' +
' [Trade_Date_Mkt_Value_Securities] [numeric](15, 2) NULL,' +
' [Total_Current_Calls] [numeric](15, 2) NULL,' +
' [Margin_Limit_Firm_Base_Curr] [numeric](15, 2) NULL,' +
' [Pend_Other_Amts] [numeric](15, 2) NULL,' +
' [Initial_Reqts_Ineligible_Collat] [numeric](15, 2) NULL,' +
' [Initial_Reqts_Eligible_Collat] [numeric](15, 2) NULL,' +
' [Deficit_Offset_Treasuries] [numeric](15, 2) NULL,' +
' [Secured_Amt] [numeric](15, 2) NULL,' +
' [Credit_Lines_Cover_Margin_Reqts] [numeric](15, 2) NULL,' +
' [Credit_Lines_Cover_Debit_OTE] [numeric](15, 2) NULL,' +
' [Span_Adjusted_Equity] [numeric](15, 2) NULL,' +
' [Mkt_Value_Cover_Initial_Reqts] [numeric](15, 2) NULL,' +
' [Accreted_Interest] [numeric](15, 2) NULL,' +
' [TIFFE_Paid_Out_OTE] [numeric](15, 2) NULL,' +
' [Jpn_UNR_Deps] [numeric](15, 2) NULL,' +
' [Prev_Total_Calls] [numeric](15, 2) NULL,' +
' [Prev_SMA] [numeric](15, 2) NULL,' +
' [Liq_Value_T_Minus_Four_Days] [numeric](15, 2) NULL,' +
' [Liq_Value_T_Minus_Three_Days] [numeric](15, 2) NULL,' +
' [Liq_Value_T_Minus_Two_Days] [numeric](15, 2) NULL,' +
' [Prev_Equity_Opt_Initial_Reqt] [numeric](15, 2) NULL,' +
' [Prev_Equity_Initial_Reqt] [numeric](15, 2) NULL,' +
' [Prev_Futures_Exch_Maint_Reqt] [numeric](15, 2) NULL,' +
' [Prev_Futures_Exch_Initial_Reqt] [numeric](15, 2) NULL,' +
' [Prev_Futures_Initial_Reqt] [numeric](15, 2) NULL,' +
' [Prev_OTE] [numeric](15, 2) NULL,' +
' [Prev_Total_Equity] [numeric](15, 2) NULL,' +
' [Prev_Liq_Value] [numeric](15, 2) NULL,' +
' [Prev_Reg_Collat_Value] [numeric](15, 2) NULL,' +
' [Prev_Account_Balance] [numeric](15, 2) NULL,' +
' [Flag_Binary_Bit_One] [varchar](1) NULL,' +
' [Flag_Binary_Bit_Two] [varchar](1) NULL,' +
' [Flag_Binary_Bit_Three] [varchar](1) NULL,' +
' [Flag_Binary_Bit_Four] [varchar](1) NULL,' +
' [Flag_Binary_Bit_Five] [varchar](1) NULL,' +
' [Misc_Byte_Code_One] [varchar](1) NOT NULL,' +
' [Misc_Byte_Code_Two] [varchar](1) NULL,' +
' [Misc_Byte_Code_Three] [varchar](1) NULL,' +
' [Misc_Byte_Code_Four] [varchar](1) NULL,' +
' [Misc_Byte_Code_Five] [varchar](1) NULL,' +
' [Misc_Byte_Code_Six] [varchar](1) NULL,' +
' [Misc_Byte_Code_Seven] [varchar](1) NULL,' +
' [Misc_Byte_Code_Eight] [varchar](1) NULL,' +
' [Misc_Byte_Code_Nine] [varchar](1) NULL,' +
' [Misc_Byte_Code_Ten] [varchar](1) NULL,' +
' [Cond_Flag_Byte_One] [varchar](1) NULL,' +
' [Cond_Flag_Byte_Two] [varchar](1) NULL,' +
' [Cond_Flag_Byte_Three] [varchar](1) NULL,' +
' [Cond_Flag_Byte_Four] [varchar](1) NULL,' +
' [Cond_Flag_Byte_Five] [varchar](1) NULL,' +
' [Cond_Flag_Byte_Six] [varchar](1) NULL,' +
' [Cond_Flag_Byte_Seven] [varchar](1) NULL,' +
' [Cond_Flag_Byte_Eight] [varchar](1) NULL,' +
' [Cond_Flag_Byte_Nine] [varchar](1) NULL,' +
' [Cond_Flag_Byte_Ten] [varchar](1) NULL,' +
' [Reg_Sec_Unreg_Code] [varchar](1) NULL,' +
' [Seg_Sec_Rpt_Line] [varchar](1) NULL,' +
' [Net_Liq_Class] [varchar](1) NULL,' +
' [Net_Equity_Class] [varchar](1) NULL,' +
' [Cross_Margin_Indicator] [varchar](1) NULL,' +
' [Group_Net_Equity_Code] [varchar](1) NULL,' +
' [Unused_One] [varchar](1) NULL,' +
' [Unused_Two] [varchar](1) NULL,' +
' [Unused_Three] [varchar](1) NULL,' +
' [Unused_Four] [varchar](1) NULL,' +
' [UK_Flg_One] [varchar](1) NULL,' +
' [UK_Flg_Two] [varchar](1) NULL,' +
' [UK_Flg_Three] [varchar](2) NULL,' +
' [UK_Flg_Four] [varchar](1) NULL,' +
' [FR_Flg_One] [varchar](1) NULL,' +
' [FR_Flg_Two] [varchar](1) NULL,' +
' [FR_Flg_Three] [varchar](1) NULL,' +
' [JP_Flg_One] [varchar](1) NULL,' +
' [JP_Flg_Two] [varchar](1) NULL,' +
' [JP_Flg_Three] [varchar](1) NULL,' +
' [HK_Flg_One] [varchar](1) NULL,' +
' [GE_Flg_One] [varchar](1) NULL,' +
' [FR_Flg_Four] [varchar](1) NULL,' +
' [Reg] [varchar](1) NULL,' +
' [Dept] [varchar](3) NULL,' +
' [Date_Upload] [varchar](8) NULL ' +
' ) ON [PRIMARY]';

EXEC(@SQLSTRING);

I have been looking at this long create table statement for a bunch of hours and have not been able to find the problem. Can someone help me on this? Any help is appreciated. Thanks.

Dave

 

[simian336]

I think it exceeds 8000

Simi

 

[simian336]

PS always do

print @SQLSTRING before Execute so you can see your statement.

Simi

 

[gmmastros]

The problem is @SQLSTRING is declared as varchar(8000), but your string requires 8,398 characters. When this happens, anything beyond 8000 characters is truncated.

There are a couple ways to solve this problem.

1. Use a varchar(max) string instead of varchar(8000).

ex:

DECLARE @SQLSTRING AS VARCHAR(max);

--create Moneyline_One @YEARMO table
Set @SQLSTRING = ''
SET @SQLSTRING = @SQLSTRING + 'CREATE TABLE DBO.Moneyline_One_201105 ('+ 
    ' [Record_Id] [varchar](1) NULL,' +
[!]etc...[/!]

Notice the part where we initialize @SQLSTRING to an empty string and then concatenate it to the big ole string? This is important. When SQL Server sees hard-coded data (like your string) it will automatically convert the data type for you. In this case, it is automatically converting to a varchar(8000), truncating the string, and then assigning it to a varchar(max).

This method will only work if you are using SQL2005 or newer.

2. Make the string smaller

Sounds obvious, right? The trick here is knowing that columns default to allowing NULLs, so you don't need that in your create script. You also don't need the square brackets around your data types, so that would same you an additional 2 characters per column. You can also get rid of extra spaces making the string smaller still.

DECLARE @SQLSTRING AS VARCHAR(8000);

--create Moneyline_One @YEARMO table
SET @SQLSTRING = 'CREATE TABLE DBO.Moneyline_One_201105 ('+ 
    'Record_Id varchar(1),' +
    'Firm varchar(1),' +
    'Office varchar(3),' +
    'Account varchar(5),' +
    'Account_Type varchar(2),' +
    'Sales_Series varchar(5),' +
    'Account_Class_Code varchar(1),' +
    'Account_SubClass_Code varchar(1),' +
    'Account_Currency_Code varchar(3),' +
    'Product_Currency_Code varchar(2),' +
    'Account_Base_Currency_Code varchar(3),' +
    'Firm_Base_Currency_Code varchar(3),' +
    'Currency_Processing_Code varchar(1),' +
    'Number_Call_Records_Account numeric(2, 0),' +
    'Oldest_Call_Account numeric(2, 0),' +
    'Filler_Byte_Available varchar(1),' +
    'Avg_Equity_Calc_Days numeric(2, 0),' +
    'Discretionary_Code varchar(1),' +
    'Omnibus_Code varchar(1),' +
    'Country_Province_Code varchar(2),' +
    'Mgd_Account_Prgm_ID varchar(2),' +
    'Short_Name varchar(20),' +
    'Cust_Name_Address_One varchar(32),' +
    'Last_Activity_Date varchar(9),' +
    'Last_Activity_Date_Work varchar(9),' +
    'Related_Firm_One varchar(1),' +
    'Related_Office_One varchar(3),' +
    'Related_Account_One varchar(5),' +
    'Related_Type_One varchar(1),' +
    'Related_Firm_Two varchar(1),' +
    'Related_Office_Two varchar(3),' +
    'Related_Account_Two varchar(5),' +
    'Related_Type_Two varchar(1),' +
    'Related_Firm_Three varchar(1),' +
    'Related_Office_Three varchar(3),' +
    'Related_Account_Three varchar(5),' +
    'Related_Type_Three varchar(1),' +
    'Group_Firm_Number varchar(1),' +
    'Group_Office_Number varchar(3),' +
    'Group_Sales_Series varchar(5),' +
    'Group_Account_Number varchar(5),' +
    'Account_Balance numeric(15,2),' +
    'Securities_Long_Mkt_Value numeric(15,2),' +
    'Securities_Short_Mkt_Value numeric(15,2),' +
    'Long_Option_Value numeric(15,2),' +
    'Short_Option_Value numeric(15,2),' +
    'Total_Outstanding_Margin_Calls numeric(15,2),' +
    'Withdrawable_Funds numeric(15,2),' +
    'Futures_Initial_Margin_Reqt numeric(15,2),' +
    'Futures_Maint_Margin_Reqt numeric(15,2),' +
    'Futures_Exch_Min_Initial_Reqt numeric(15,2),' +
    'Margin_Excess_Deficit numeric(15,2),' +
    'Futures_Open_Trade_Equity numeric(15,2),' +
    'Special_Misc_Account_Amt numeric(15,2),' +
    'Long_Options_Open_Trade_Equity numeric(15,2),' +
    'Short_Option_Open_Trade_Equity numeric(15,2),' +
    'Avg_Equity numeric(15,2),' +
    'Month_To_Date_Commission numeric(15,2),' +
    'Unsettled_Security_Long_Mkt_Value numeric(15,2),' +
    'Trade_Date_Mkt_Balance_Securities numeric(15,2),' +
    'Unsettled_Short_Mkt_Value numeric(15,2),' +
    'Mkt_Maker_Haircut_Amt numeric(15,2),' +
    'Jpn_Unr_Cash_Deposits numeric(15,2),' +
    'Mth_To_Date_P_and_L numeric(15,2),' +
    'Seg_Req_Long_Mkt_Value numeric(15,2),' +
    'Seg_Req_Short_Mkt_Value numeric(15,2),' +
    'Equities_Initial_Marging_Req numeric(15,2),' +
    'Equities_Maint_Margin_Req numeric(15,2),' +
    'Conv_Rate_Firm_Base_Curr numeric(15,2),' +
    'Monthly_Dividend_Balance numeric(15,2),' +
    'Jpn_Unsettled_Unrealized_P_and_L numeric(15,2),' +
    'Futures_Style_Opt_OTE numeric(15,2),' +
    'Loan_Value numeric(15,2),' +
    'Buying_Power numeric(15,2),' +
    'Pending_Dividend_Amt numeric(15,2),' +
    'Pending_Interest_Amt numeric(15,2),' +
    'Liquidating_Value numeric(15,2),' +
    'Margin_Collateral_Value numeric(15,2),' +
    'Futures_OTE_Credit numeric(15,2),' +
    'Futures_OTE_Debit numeric(15,2),' +
    'Opt_Premium_Value numeric(15,2),' +
    'Futures_Style_Opt_OTE_Credit numeric(15,2),' +
    'Avg_Sec_On_Deposit numeric(15,2),' +
    'Long_Options_Premium numeric(15,2),' +
    'Unsettled_Unrealized_P_and_L numeric(15,2),' +
    'Avg_Margin_Excess numeric(15,2),' +
    'Jpn_IR numeric(15,2),' +
    'Jpn_IR_Cash numeric(15,2),' +
    'Jpn_MR numeric(15,2),' +
    'Jpn_Initial_To_Release numeric(15,2),' +
    'Total_Account_Requirements numeric(15,2),' +
    'Equity_Products_OTE numeric(15,2),' +
    'LOV_US_Treasury numeric(15,2),' +
    'Total_Equity numeric(15,2),' +
    'Avg_Balance numeric(15,2),' +
    'Avg_Opt_Value numeric(15,2),' +
    'Conv_Rate_Account_Base_Currency numeric(15,2),' +
    'Commissions_Accrued_Recv numeric(15,2),' +
    'Commissions_Accrued_Unsecured numeric(15,2),' +
    'Span_Option_Value numeric(15,2),' +
    'Equities_Exch_Min_Margin_Req numeric(15,2),' +
    'Equities_Initial_Req_Trades numeric(15,2),' +
    'Equities_Option_Initial_Reqts numeric(15,2),' +
    'Equities_Option_Maint_Reqts numeric(15,2),' +
    'Futures_Exch_Min_Maint_Reqt numeric(15,2),' +
    'Group_Excess_Used_Deficit_Covered numeric(15,2),' +
    'Non_Cash_OTE numeric(15,2),' +
    'Cash_Collateral_Daily_Net_Value numeric(15,2),' +
    'Equity_Percent numeric(15,2),' +
    'Regulatory_Collateral_Value numeric(15,2),' +
    'Conv_Rate_Account_Base_Cur_SMFD numeric(15,2),' +
    'Trade_Date_Mkt_Value_Securities numeric(15,2),' +
    'Total_Current_Calls numeric(15,2),' +
    'Margin_Limit_Firm_Base_Curr numeric(15,2),' +
    'Pend_Other_Amts numeric(15,2),' +
    'Initial_Reqts_Ineligible_Collat numeric(15,2),' +
    'Initial_Reqts_Eligible_Collat numeric(15,2),' +
    'Deficit_Offset_Treasuries numeric(15,2),' +
    'Secured_Amt numeric(15,2),' +
    'Credit_Lines_Cover_Margin_Reqts numeric(15,2),' +
    'Credit_Lines_Cover_Debit_OTE numeric(15,2),' +
    'Span_Adjusted_Equity numeric(15,2),' +
    'Mkt_Value_Cover_Initial_Reqts numeric(15,2),' +
    'Accreted_Interest numeric(15,2),' +
    'TIFFE_Paid_Out_OTE numeric(15,2),' +
    'Jpn_UNR_Deps numeric(15,2),' +
    'Prev_Total_Calls numeric(15,2),' +
    'Prev_SMA numeric(15,2),' +
    'Liq_Value_T_Minus_Four_Days numeric(15,2),' +
    'Liq_Value_T_Minus_Three_Days numeric(15,2),' +
    'Liq_Value_T_Minus_Two_Days numeric(15,2),' +
    'Prev_Equity_Opt_Initial_Reqt numeric(15,2),' +
    'Prev_Equity_Initial_Reqt numeric(15,2),' +
    'Prev_Futures_Exch_Maint_Reqt numeric(15,2),' +
    'Prev_Futures_Exch_Initial_Reqt numeric(15,2),' +
    'Prev_Futures_Initial_Reqt numeric(15,2),' +
    'Prev_OTE numeric(15,2),' +
    'Prev_Total_Equity numeric(15,2),' +
    'Prev_Liq_Value numeric(15,2),' +
    'Prev_Reg_Collat_Value numeric(15,2),' +
    'Prev_Account_Balance numeric(15,2),' +
    'Flag_Binary_Bit_One varchar(1),' +
    'Flag_Binary_Bit_Two varchar(1),' +
    'Flag_Binary_Bit_Three varchar(1),' +
    'Flag_Binary_Bit_Four varchar(1),' +
    'Flag_Binary_Bit_Five varchar(1),' +
    'Misc_Byte_Code_One varchar(1) NOT NULL,' +
    'Misc_Byte_Code_Two varchar(1),' +
    'Misc_Byte_Code_Three varchar(1),' +
    'Misc_Byte_Code_Four varchar(1),' +
    'Misc_Byte_Code_Five varchar(1),' +
    'Misc_Byte_Code_Six varchar(1),' +
    'Misc_Byte_Code_Seven varchar(1),' +
    'Misc_Byte_Code_Eight varchar(1),' +
    'Misc_Byte_Code_Nine varchar(1),' +
    'Misc_Byte_Code_Ten varchar(1),' +
    'Cond_Flag_Byte_One varchar(1),' +
    'Cond_Flag_Byte_Two varchar(1),' +
    'Cond_Flag_Byte_Three varchar(1),' +
    'Cond_Flag_Byte_Four varchar(1),' +
    'Cond_Flag_Byte_Five varchar(1),' +
    'Cond_Flag_Byte_Six varchar(1),' +
    'Cond_Flag_Byte_Seven varchar(1),' +
    'Cond_Flag_Byte_Eight varchar(1),' +
    'Cond_Flag_Byte_Nine varchar(1),' +
    'Cond_Flag_Byte_Ten varchar(1),' +
    'Reg_Sec_Unreg_Code varchar(1),' +
    'Seg_Sec_Rpt_Line varchar(1),' +
    'Net_Liq_Class varchar(1),' +
    'Net_Equity_Class varchar(1),' +
    'Cross_Margin_Indicator varchar(1),' +
    'Group_Net_Equity_Code varchar(1),' +
    'Unused_One varchar(1),' +
    'Unused_Two varchar(1),' +
    'Unused_Three varchar(1),' +
    'Unused_Four varchar(1),' +
    'UK_Flg_One varchar(1),' +
    'UK_Flg_Two varchar(1),' +
    'UK_Flg_Three varchar(2),' +
    'UK_Flg_Four varchar(1),' +
    'FR_Flg_One varchar(1),' +
    'FR_Flg_Two varchar(1),' +
    'FR_Flg_Three varchar(1),' +
    'JP_Flg_One varchar(1),' +
    'JP_Flg_Two varchar(1),' +
    'JP_Flg_Three varchar(1),' +
    'HK_Flg_One varchar(1),' +
    'GE_Flg_One varchar(1),' +
    'FR_Flg_Four varchar(1),' +
    'Reg varchar(1),' +
    'Dept varchar(3),' +
    'Date_Upload varchar(8) NOT NULL ' +
    ') ON [PRIMARY];'

EXEC(@SQLSTRING);

3. Use ALTER TABLE with an initial CREATE TABLE

Specifically, you could create the table with just a column or 2 and then ALTER the table to add more columns.

4. You can create this table based on another table that has the same structure, like this:

Select * 
into   DBO.Moneyline_One_20110[!]5[/!] 
From   DBO.Moneyline_One_20110[!]4[/!]
Where 1=0

The part 'Where 1=0' effectively filters out all the rows and you will only get the structure.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Can you also explain a reason for using dynamic SQL in your code?

PluralSight Learning Library

 

[bborissov]

Maybe the name of the table (or part of it) is passed as parameter?

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[hedgracer]

Thanks to everyone for all of your help.

Dave